You are using an older browser that might negatively affect how this site is displayed. Please update to a modern browser to have a better experience. Sorry for the inconvenience!

10 Ways to show the (Apex) Data Loader that You are the Boss of It


If you have been working with the Salesforce API for any length of time, you have probably used the Apex Data Loader. A robust and simple tool, the Data Loader is useful for getting data into and out of Salesforce, and with a bit of practice, offers more functionality than the import wizards.

The following are some Data Loader features you’re likely to run into as you use the Data Loader with some clarifications on what’s going on and how to overcome

1. Log In Issues

Check the following three things to verify when you have problems logging into the Data Loader:

A. Are you timed out?

B. Are you logging into sandbox or production?

C. Do you need a security token?

A. Log In Issues: Are You Timed Out?

If you leave the Data Loader running on your machine, it will time out in the number of seconds you have defined in the Settings:

If the Data Loader has timed out, and you try to perform another operation with it, you will receive a message like this one:

The message looks a bit ominous, and if you are seeing it for the first time … or for the first time in a while … it’s easy to suspect that something worse than a time-out is happening, but that’s all it is.

If you try the exact same operation again, this time it will work.

B. Log In Issues: Are you logging into sandbox or production?

Another thing to check if you receive an error logging into the Data Loader is whether you are logging into Production with your Sandbox credentials, or vice-versa.

apex data loader

Check the Settings for the Data Loader. The Server host selected should be https://test.salesforce.com for a sandbox org and https://login.salesforce.com for a production org. In the above example, the credentials were those of the production server host, but the Server host was set to https://test.salesforce.com.

C. Log In Issues: Do you need a security token?

Unless your IP address is trusted by the Salesforce org you are trying to log into, you will need to add your security token to the end of your password in order to log into the Data Loader.

Keep track of your security tokens – you need one for each production org and each sandbox org for which your IP address is not trusted.

You will see this error message if your security token is required and you did not include it after the password:

Apex Loader

Either add your security token to the end of your password string, or trust your IP address. Be careful trusting IP addresses – doing so reduces the protection on your Salesforce org.

To trust your IP address, obtain the IP address from your system. Then, in Salesforce, under Administration Setup => Security Controls =>Network Access, enter your IP Address as the Start IP Address and End IP Address, or enter a range that includes your IP Address. Afterwards, you will not need the security token to use Data Loader with this Salesforce org.

2. Loading Null Values into Fields

Sometimes you may need to use the Data Loader to “null out” a value and load blank values for one field for one or more records.

In the Data Loader, select Insert null values. It is recommended that you only select this option when you specifically intend to insert null values. Don’t leave it selected because you may inadvertently erase data if you map a blank column to a field when you are data loading.

3. Loading Data When Same Record is Updated More than Once

The Data Loader limits the number of records you can update with an upsert in the same batch. If you try to upsert the same record more than time in the same batch, you will receive an error message like one of the following:

duplicate value found: unknown duplicates value on record with id: a044000000HpfNS

Duplicate external id specified: 12345

Depending on the situation, the data you are trying to load may need to be analyzed, for example, to determine the most recent update, or some other criterion of precedence.

However, if you know that all the upserts need to be loaded, you can eliminate this obstacle by reducing the Data Loader batch size to one. Each record will then load, regardless of whether that record was already upserted earlier in the data load.

apex loader

4. Invoking Assignment Rules When Loading Leads

If you are loading leads with Data Loader and want a particular lead assignment rule to fire when they are loaded, first obtain the lead assignment rule’s Salesforce ID. If you open the Lead Assignment rule in Salesforce, you can select it from the URL:

https://na9.salesforce.com/setup/own/entityruledetail.jsp?id=01QE0000000KxBX&setupid=LeadRules

From the Data Loader Settings option, enter the Salesforce ID for the Assignment rule:

apex data loader

5. Show All Salesforce Objects

The Data Loader defaults to showing the most common standard objects and your org’s custom objects when you insert/update/upsert/delete/export.

However, there are other objects that you can access by selecting Show all Salesforce objects when you begin your data loader operations.

Some examples of additional objects that are accessible with the Data Loader include Account, Case, and Opportunity Contact Roles, History records, Approvals, Content details, attachments, and details about Territories and Account teams, if your site uses them.

6. Using Data Loader to Write SOQL Queries

When you delete/export Salesforce records using the Data Loader, you are performing a SOQL query. The generated query appears at the bottom of the Data Loader dialog. Add filters to the query by first selecting from the fields in object you are querying, and then selecting an operation and choosing a value.

Remember to click Add condition once you have set up the condition with the drop-downs!

apex data loader

The Data Loader automatically separates multiple conditions with AND’s. If you need OR’s, then edit the generated query to add them manually.

7. Data Loader and Dates

The date format used with the Data Loader is somewhat tricky, particularly the time. The format looks like this: YYYY-MM-DDThh:mm:ssZ. For example:

2014-12-29T00:00:00.000Z

If you want to use it in a filter, for example with CreatedDate, the condition looks like this:

WHERE CreatedDate > 2014-12-29T00:00:00.000Z

Here’s another example, of a range:

WHERE CloseDate > 2013-01-01T00:00:00.000Z and CloseDate < 2014-01-01T00:00:00.000Z

8. Upserts: Identifying Records Using External IDs

When you need to update Salesforce records by identifying them with a key other than their Salesforce ID, you need to perform an upsert. Once you identify the object for which you are upserting records and click Next, you will be prompted to indicate which field(s) you are matching with external IDs in the data you are loading.

The picklist for the object’s ID will contain every unique external ID you have defined for that object.

Apex Data Loader

Each lookup on the object that has any unique external IDs defined will also have a picklist.

Apex Loader

You do not have to select anything to match the lookup field; only do so if the data file you are loading contains values for one of the IDs. You still need to map the ID along with the other fields for loading.

Always be careful with upserts. There is no clear path to undo them.

9. Data Loader Miscellaneous

Here are a few more notes for using the Data Loader.

  1. For checkboxes, you can use either “1” or “True” for checked and either “0” or “False” for unchecked.
  2. Note that you can load any text into a picklist field, regardless of whether it is a picklist selection. Depending on the situation, you may wish to proof the values in your load file first. If you load a value not found in the picklist, the value will be saved in that record, but you will not be able to select it for any other record, nor re-select it if you change it.
  3. To load multi-picklist values, separate them by semi-colons. The above bullets apply.
  4. If you load a file that has no data for a mapped field that is not required, a record will be created that is blank except for the autonumber. If you are using Name instead of autonumber for that object, the record’s Salesforce ID will load for the name if not provided.
  5. If you load data and receive exactly six errors, it may be that you saved an extracted Salesforce report and did not remove the footer lines. Since the Spring ’14 release, you can remove the footer lines from a report by going to Customize =>Reports& Dashboards => User Interface, and then selecting “Export Reports Without Footers.”

apex loader

10. Deciphering Data Loader Errors

Learning to recognize and resolve data loading errors will make you a stronger Salesforce Admin or Developer. In addition to many common dataloading errors, here are a few Data Loader error examples and what they mean.

Account: id value of incorrect type: #N/A

This example error is for the Account object, but where the error reads “Account,” it could be any standard or custom Salesforce object. The word(s) after the colon in this error refer to what was found in the column mapped to the Account ID – in this case, the column for Account read “#N/A.” This value is typical of using a vlookup function in Excel to build your data load file and getting no results for this row.

There is no way to update a Salesforce record without its Salesforce ID. If you have values for another field on the object that is defined as a foreign key (or “unique identifier” in Salesforce), you can use that field to upload data, but you have to perform an upsert.

Error converting value to correct data type: Failed to parse date: N/A

Error converting value to correct data type: Failed to parse date: 00:00.0

The above are two examples of errors when the data being mapped is not of the correct format for the date field to which it is being mapped. You can tell by what follows the colon in the error; this is what the Data Loader is finding and trying to map to a date. See above for help with Date/Time format.

Insufficient access rights on cross-reference ID

Insufficient access rights messages pertain to the owner of the record that is being modified. So, if you are loading an owner ID (using the Salesforce ID of someone’s User record), then that person has insufficient access (permission) to what you are trying to insert or update.

If you are not loading an Owner ID for the records, then you (the user logged into the Data Loader) do not have permission to insert or update. If you are a System Admin, check for the following:

  1. An unassigned record type for the object (it must be one of the record types for that object that is assigned to your profile in order for you to be able to create records of that record type)
  2. An inactive record type for the object
  3. A field that it is read-only for the System Admin profile (it may be read-only for all profiles)

Similarly, if you see this error:

Record Type ID: this ID value isn’t valid for the user

Check to make sure:

  1. Is it active?
  2. Does the record owner’s profile have access to the record type?
  3. Is the ID correct for record type – no typos, or other errors in the string?

Invalid cross-reference ID

This error is related to the other ID errors. It indicates that you are trying to load some value (or a blank value) for an external ID for a custom object that does not exist for any record in that object. For example, trying to load a reference to Contact with “9013133” as a Student_ID__c (where Student_ID__c is defined as a unique external ID for Contact) when you don’t have a Contact in your org with that Student_ID__c value.

It is also possible that the user performing the load does not have access to the Student_ID__c field (in the example). To check on this second possibility, manually try to access one of the Contact records with one of the Student_ID__c values that is giving this error, such as by building a view of Contacts with Student_ID__c = “9013133” (which should return exactly one Contact if you have access to the field and that value exists for one of the Contact records.

npe01.IndividualAccounts: execution of BeforeInsert

Any error message that references BeforeInsert, BeforeUpdate, or another Apex operation is most likely being triggered by some existing code in your organization. Review the code base for the object to which you are trying to insert or update records.

If there is a Foreign Key, you must include a Legacy System ID.

Sometimes the error is caused by existing validation rules in your Salesforce org. To recognize such errors, use consistent language for your validation rule error messages and if you need to be able to load data – for example, legacy data – that does not have to be consistent with validation rules, consider making an exception to the validation rule, such as a Custom Setting that keeps it from running when the user is the System Administrator.

More On Data Loader Errors

When you can’t figure out the cause of your error, you can usually find someone with a similar one, by typing the text of the error into a search engine, or directly in the Salesforce Success Community. The Data Loader messages have not improved much in clarity or completeness since the tool has been in use, but there are many Ideas on the Idea Exchange for improvement, so vote up any that you agree with, or add one of your own.