The Salesforce Apex Data Loader is a downloadable tool that runs on the desktop.  It is a free download from Salesforce and can perform Inserts, Updates, Upserts, Deletes and Exports of data.  The Data Loader utilizes an API connection to Salesforce which requires a Salesforce login, password and security token.  It also can be configured for use of the Bulk API which can tremendously improve performance for large record sets.

The Salesforce Apex Data Loader is available in both Salesforce Classic and Lightning Experience, but is only functional with the Enterprise, Performance, Unlimited, Developer, and Database.com Editions.

When importing data, Data Loader reads, extracts, and loads data from comma separated values (CSV) files or from a database connection. However, if the csv delimiter is not your standard, Data Loader will accommodate other delimiters.  When exporting data, it outputs CSV files.

Connecting to your Source and Target

When you first open the Data Loader, you are presented with a form that has several different operations available:

Choosing one of these options will open the login form to make your connection to Salesforce.  You will be required to enter your Salesforce login name, Salesforce password appended with your Security Token, and your Salesforce URL.

If you don’t already know/have a valid Security Token, you will need to login to your Salesforce deployment and navigate to the top navigation bar go to your name > Setup > Personal Setup > My Personal Information > Reset My Security Token.  However, if your Salesforce Administrator has setup restricted IP addresses, the link to reset your Security Token may not appear.  Check with your Administrator.  If you can reset your Security Token, it will be emailed to the email address that is in your profile.  This is how you use it. If your password is pass@word, and your security token is 10wovm593749t6kjfdke0, then you must

enter pass@word10wovm593749t6kjfdke0 as the complete password to log in.

CRUD Operations (Create, Read, Update and Delete)

Before clicking one of the operations available, here are a few things to think about regarding your source data.

1. Relationships between parent/child records.

If you are inserting records, in more than one operation and in more than one object, make sure you have a unique identifier on the records so that the parent/child relationships can be maintained.  For example:  If inserting account data, make sure there is a unique identifier on the account as well as the matching identifier on the contacts.  Once the accounts have been migrated, the Data Loader will provide you with a .csv file of the migrated accounts with the GUID that was assigned by the API during the migration process.  You will need to include that GUID when migrating the related contacts in order to maintain the parent/child relationship.
This example can and should be applied to any inserts/updates between any objects that have a parent/child relationship.

2. Picklist and Lookup Values.

If your source data will include picklist values that exist in Salesforce, make sure that the text values match exactly to those found in Salesforce.  Salesforce stores picklist values as text in the database so no need to worry about any numeric identifiers.  Also, multi-select picklists work the same way, so just make sure your source data text is correct and you are using the correct delimiter between values in the multi-select.
However, lookup fields in Salesforce are not stored as text, but is stored as a GUID.  If you are migrating data fields that match to lookup fields in Salesforce, you will need to find the GUID of those values and include the GUID in your source data file.

3. Business Rules and Workflows.

Because the Apex Data Loader uses the Salesforce API, the migrated data passes through the API and does not go directly to the database.  What this means is any business rules or workflows that are tied to the objects you are migrating data into, will be triggered.  Sometimes these rules or workflows, depending on the values found in some fields, require data to be applied to other fields.  If all required fields do not have valid values, the migration will fail.

Mapping Source Fields to Target Fields

Once you choose the operation you are going to perform, you’ll be prompted to select the Salesforce object that you will be migrating data into.  Typically you can only choose one object, so, if you have child records that reside in a different object, that data will have to be migrated in a separate operation (see notes above for maintaining parent/child relationships).

Once you’ve chosen the Salesforce object you are migrating to, you will be required to point to the source data file that you will be using.  See notes above concerning an appropriate delimiter.

When your source and target have been configured, mapping fields from your source to your target is fairly straight forward and intuitive.

http://www.crmsalesforcetraining.com/wp-content/uploads/2014/04/dataloader401.png

Running the Migration Job

Running the migration job once your mapping is complete is also fairly straight forward and intuitive.

Once the job is complete, the Apex Data Loader offers a very nice feature.  It creates a file of both your successfully migrated records as well as your failed migrated records.  The files that are produced are .csv text files and include the results of passing through the API.  This means that the successful records will include the GUID that was assigned to those records at run-time.  See the notes above regarding using this GUID in your child record migrations.

Failed records are in a separate file from the successful records and includes the error message produced through the API so you will know the cause of the failure.  The advantage of having this file is it is a copy of the original file used in the migration (with an added column for the error message).  So, you can correct the error found in each record and use the file as your source in a second attempt.  This prevents trying to modify the original file to remove the successfully migrated records to prevent migrating duplicates.

http://www.crmsalesforcetraining.com/wp-content/uploads/2014/04/dataloader421.png

Extracting Data

There are several reasons why you might want to extract data from your Salesforce deployment.  A very common reason is to have a local copy of the data in order to build reports that cannot be built using the tools available within the Salesforce environment, but certainly there are an infinite number of reasons for extracting data.

The Apex Data Loader tool lets you chose a specific object in Salesforce, all the objects in Salesforce, or you can write a SOQL based query for extracting a specific subset of data.  SOQL is a hyper SQL language that, like all SQL tool specific applications, includes the ANSII standard reserved words, so you don’t have to be a SOQL expert to write a simple SELECT, FROM, WHERE type of query.

The Apex Data Loader offers a type of query-builder to help you form your extractions specifications.

http://blog.wdcigroup.net/wp-content/uploads/2013/10/data-loader.png

Conclusion

The Salesforce Apex Data Loader is a great tool for occasional data loads or data extractions.  It’s very intuitive and offers a BULK API connection to increase performance for large data loads.  There are limitations with regard to parent/child migrations, making it a bit more laborious to get things migrated while maintaining the relationships.  Also, if you do regular migration, there may be better tools for automating the process.

But, for the price point (free), this is a great tool for small, infrequent ETL jobs.