Import Related Records with an External ID in Salesforce Data Loader

When performing the Upsert with Salesforce Data Loader, here are a few considerations:

• Always back up your data before performing any Data Loader operation. You can use either Export Backup Data via the export service or you can select Export Data for the appropriate objects via the Data Loader. Run a test with one or a small subset of records to ensure the operation was successful by verifying the corresponding records in Salesforce are correct. Once you have confirmed the result is correct, perform the appropriate Insert, Update, and/or Upsert operation for all records. For more details, review Export Backup Data from Salesforce.

• It may not be possible to map all reference fields to an External ID. For example, polymorphic fields such as the standard Activity object's Related To (WhatId) and Name (WhoId) fields and a custom object's Owner field do not allow mapping to external IDs. Consider promoting the Idea: Enable External Keys on Polymorphic Fields if this is a feature you would like to see. Ideas can potentially be added to Salesforce with a future release. For more on polymorphic fields, you may want to review Understanding Relationship Fields and Polymorphic Fields.

Video guide on importing related records using External IDs

How to Update Records Using the External ID Using Data Loader

Import related records using an External ID

Similar to Salesforce record IDs, when a field is marked as an External ID, its values can be used to match and associate related records to one another. External IDs are commonly used to store unique record identifiers from external systems and allow for routinely loading data into Salesforce without having to prepare your import file with existing or related Salesforce record IDs each time.
 

1. Identify an existing External ID field on Object A that you'd like to use for matching (or create an External ID via a new custom field with the Data Type "Text" on Object A) and select the External ID and Unique field attributes.

Note: For more details on creating an external Id, see these related help articles: Create Custom Fields​ and Custom Field Attributes​.
 

2. Populate the External Id field on related (Object A, in our example) records in Salesforce:

If the related Object (A) records already exist in Salesforce:

- Ensure the External ID field is populated with unique values so you can easily use it as matching criteria to relate records later on.

If your external Id field is not populated on existing records, you can mass update them with desired values:​

A) Create a .CSV file containing all Object A records by either Building a Report and Exporting a Report or by using the Data Loader to Export Data. 

Note: Be sure to include the Salesforce IDs for Object A records in your report or export file.

B) Populate your resulting export with the desired unique values for the External Id field and then perform an update to the existing records by mapping Salesforce ID and your External Id field.
 

If the related object (A) records don't already exist in Salesforce:
 

Identify a column that contains data that would serve as a unique identifier for Object A records and map it to the External ID field you identified or created in Step 1 when inserting the records. Ideally, the unique values you choose to map from your Object A import file would already be contained in your Object B import file and/or commonly contained in files from an external system.
 

3. Perform an Insert for new records or perform an Update for existing records to Object A. Map required fields and the External ID column to the External ID field on Object A's records from step 1 (be sure to include and map Salesforce ID for an update or upsert operation) to populate a unique External ID value in Salesforce for all of Object A's records.

For more details, see this related help article: Insert, Update, or Delete Data Using Data Loader​

4. Prepare your Object B file with the related Object A record's corresponding External ID values you'd like to use for matching in order to populate the lookup field from Object B to Object A using the following method:
 

• In the .CSV file for Object B, choose an existing column containing the unique data that matches the External ID values you set for Object A's records in steps 1-3 or create a new column named "External ID" and manually populate it (or use Excel's vlookup function) to bring over the External ID values set on Object A records.

5. In Data Loader:

a. Click Upsert.
b. Select the appropriate object (Object B in our example).
c. Click Browse.
d. Select your import file for Object B.
e. Click Next.

6. In Step 2: 'choose your related objects' select the External ID field for the Object A drop-down to use the External ID field for matching related Object A records upon importing Object B records.

7. In step 3: 'mapping' drag and drop the Salesforce field with the name syntax [ObjectName]:[ExternalIDField__c] to map it to your Object B file's column containing the External ID values for Object A's records (in our example, the naming syntax [ObjectName] is "Object A" and [ExternalIDField__c] is the API name of object A's External Id field from step 1). 
 

• By doing this, you can easily relate Object B records to Object A records using the External ID that you created, which eliminates the process of importing Object A records, exporting the new IDs, matching them up in Excel and then importing the child records.