Skip to main content

Dataloader

Realfire dataloader is a suffusticated dataloader with many capabilities that no other tools support. This document outlines and provides information about how to use the dataloader.

Dataloader is available in Professional version (with some limits) and Enterprise edition.

Concepts

It helps to understand various terms that you might come across while dealing with Dataloader.

Dataload​

Dataload a configuration that describes data manipulation job consisting of (usually) one or more dataload steps.

Dataload is not an execution on itself meaning that by just creating Dataload, you are not actually doing any data manipulation but it is just a configuration that describes the data job that needs to be executed.

Dataload Step​

Dataload Step is one step inside a dataload. Each step is assiciated with a particular dataload type. If there are multiple steps, each step is executed one after the other. Anytime, the Step errors out, execution of dataload is stopped.

Dataload Type​

Dataload Type is type of data job that a step is configured to do. Dataloader supports many dataload types (more are being added each release). Refer to the section below for available dataload types.

Input/Output​

Each Dataload Step takes data input and optionally processes into output. Depending on dataload type (Sql Update or Sql Delete etc) some steps supports only Input and no output.

Input/Output Type​

Input/Output of a particular type, which identifies type of that input or output. Currently Dataloader supports File and Salesforce types. More input/output types are being considered to be added

Mapping​

Mapping identifies how input fields needs to be mapped to output fields. It allows to skip some input fields, allows to map fields with different names and also to map one input field to multiple output fields.

Sql Update/Sql Delete types do not support mapping as they always works with one source.

Advanced Mapping​

Each mapping entry can be configured with advanced characteristics including date/number formats, default formulas and transformation formulas.

Id Lookup​

Id Lookup allows to resolve the Id of reference fields against another file or soql or parent field

Notifications​

Dataload Notifications allows you to specify emails/Slack url to receive notification upon completion of dataload.

Dataload Types

Dataloader supports various type of dataload jobs. In case of multi-step dataload, each step can be configured with any of these dataload types.

Dataloader supports following types:

  • Export
  • Export Bulk Results
  • Insert
  • Update
  • Delete
  • Delete Hard
  • Sql Update
  • Sql Delete

Export​

This dataload type is used to export data from Salesforce using Soql. You can specify any valid soql including parent ref fields (for Batch api type). It also supports exporting binary content from Base64 fields like Attachment or Document.

Data can be exported to csv file. While exporting the csv file, file can be split into multiple files based on number of records. You can use this feature to manegable sized export files.

Export Bulk Results​

If you are another third party app has created bulk jobs whose results you want to export (either query or data mutate jobs), you can do so using this dataload type. Just specify the Bulk Job id and specify where to store the exports and it will do the job.

This type is really helpful if you have a query bulk job, which has produced multiple files but you want to combine all of them into one file or if you want to apply some mapping while exporting the results.

Insert​

This dataload type allows you to insert records from a csv file into a Salesforce object using full capability of mapping.

Update​

This dataload type allows you to update existing records in Salesforce. Select the file containing records you update to update, select Salesforce target and upload.

Delete​

This dataload type allows you to delete records from Salesforce based on Id stored in a csv file.

Delete Hard​

Similar to Delete but it will also remove the records from Recycle bin after deleting. You can use this if you are delete huge number of records and you don't want to keep those deleted records in Recycle bin

Sql Update​

Sql Update Dataload can be used to update any updatable objects in Salesforce using sql like statement. Dataloader will do all the heavy lifting of exporting the records matching per sql statement, updating the records and pushing the updates back to Salesforce.

See below for more information about Update Sql Statemet.

Sql Delete​

Sql Delete dataload is used to delete records using sql like syntax. Syntx allows to pick the records that needs to be deleted and the order in which those records needs to be deleted. Optionally it also allows to remove the records from recycle bin.

See below for more information about Delete Sql Statement

Update Statement

Update Statement is sql like support to update Salesforce records in a very flexible and powerful way.

It supports fetching the exact set of records you are looking to update using same where condition as soql, allows you to limit updates to set first top n records and also allows you to order the records so updates will happen in a predictable way.

Update syntax is as follows (anything within [] is optional and {} is sobject or field names or other values)

UpdateStatement:
    update {SobjectName}
    [IncludeFields]
    set UpdateExpressions
    [Scope]
    [Where]
    [Order]
    [Limit]
    [Offset]

IncludeFields:
    include fields {field1}, {field2}

UpdateExpressions:
    {field1} = {UpdateValue}, {field2} = {UpdateValue}

Scope/Where/Order/Limit/Offset:
    {same definition as soql}

UpdateValue: null | literal | Field | ParentField | Formula

UpdateValue​

The important element of update statement is UpdateValue. It is one of following values as applicable for the field being updated.

  • Null Value
  • Any literal (string, boolean, date, datetime, number)
  • Other field of same record
  • Any parent field of same record (upto Salesforce supported 5 levels)
  • Formulas

Null Value

If you want to blank out any fields, then use this update value.

update account set billingstreet = null where billingstate = null

Literal Value

Literal value is any value acceptable for the field type.

Here are some examples.

//Sets checkbox field to true
update account set active__c = true where owner.email = 'me@gmail.com';

//Sets checkbox field to false
update account set active__c = false where owner.email = 'me@gmail.com';

//Sets text/textarea/email/phone or any other string based field
update account set url__c = 'https://www.datasert.com' where url__c = null;

//Sets Number field
update account set anualrevenue = 10000 where name = 'Datasert';

//Sets date field, which should be in ISO 8601 format
update contact set dob__c = '1980-01-01' where email = 'kyle@datasert.com'

//Sets datetime field, which should be in ISO 8601 format
update contact set last_contact_time__c = '1980-01-01T10:10:10Z' where email = 'kyle@datasert.com'

Other Field

Any updatable field can be set to any other datatype compatible field of same record. This is used to copy values from one field to another. For example:

update account set billingstreet = shippingstreet, billingstate = shippingstate where billingstreet = null

Parent Fields

Value could be any valid parent reference field, upto salesforce supported maximum 5 levels. For example.,

update contact mailingstreet = account.billingstreet, mailingcity = account.billingcity where mailingstreet = null

Formulas

Formulas allows you to transform the values in a very flexible ways and sets the results of that formula to the value. For more details about the formulas check out Formuas. You can also experiment with Formulas in Formula Playground in Realfire

Formula values are enclosed within double quotes in the sql statement (single quotes are used for literal values).

Some examples are:

//Trims the names to remove leading/trailing whitespaces
update contact set name = "value.trim()"

//Increments the discount by 5%
update account set max_discount__c = "value.get() * 1.05" where name = 'Some Company`

Delete Statement

Delete Statement is sql like support to delete Salesforce records.

It supports identifying the exact set of records you are looking to delete using same where condition as soql and delete them. Then optionally also purge those deleted records from recycle bing

Delete syntax is as follows (anything within [] is optional and {} is sobject or field names or other values)

DeleteStatement:
    delete [hard] from {SobjectName}
    [Scope]
    [Where]
    [Order]
    [Limit]
    [Offset]

Scope/Where/Order/Limit/Offset:
    {same definition as soql}

Some examples:

//Delete all accounts created today
delete from account where createddate = today;

//Delete all accounts created today and purge them from recycle bin
delete hard from account where createddate = today;

//Delete all accounts which have no address
delete from account where billingstreet = null