Loading

Add IDs of Available Records to your Import File

Date de publication: Oct 13, 2022
Description
Using the unique Salesforce ID for existing records in your import file is the best way to ensure that your import file's data is properly matched against existing records in Salesforce. 


The Unified Data Import Wizard allows you to select matching criteria outside of Salesforce.com ID such as record names which is convenient if your import file does not contain existing Salesforce record IDs, however, there may be several potential matches for records existing in Salesforce due to name fields not typically required to contain unique values. This is why it is recommended, if possible, to use record IDs to ensure a proper match to an existing record in Salesforce.

In addition API tools, such as the Data Loader for example, do not allow for matching records based on anything other than Salesforce.com record IDs or potentially External ID fields so adding IDs may not only be recommended but required in your import file.
 
Résolution

Warning: Always back up your data before performing any data operation. See Exporting Backup Data or Export Data for more details. It is recommended to run a test with a small subset of records to ensure the operation was successful through manually opening and verifying that the corresponding records are correct in Salesforce.
 

1. Extract existing records from the target object for import or update

Build a Report and include your record’s ID as a column, which doesn’t appear by default. For instance, for Accounts, add the “Account ID” column. Then, Export a Report in Comma Delimited .csv format or alternatively use an API tool such as the Data Loader to Export Data from the import's target object. Be sure to include the record Id field in your report or export file and fields that may contain unique data that also exists in your import file or may be helpful in comparing existing records against the data in your file.
 
For example, if you're importing Accounts and your import file contains Account names, phone numbers, address, or other information that may be unique to each Account, select to include those fields or data in your report or export in case you need it to cross reference existing Salesforce records against your import file's data to confirm a potential match.

 

2. Use Excel's Vlookup function to populate the existing record's Salesforce IDs from your export file into your import file

 
Note: Salesforce does not provide support for Microsoft Excel features or functionality and it's against policy to perform data management or manipulation (de-duping, merging, cleansing) tasks including Vlookups. The following details are provided to serve as general guidelines for customers to prepare their data and can't be performed by Support on their behalf.
 
  1. Open both your import file and the report/export files in order to perform an Excel Vlookup operation. You can use the record name as the matching criteria upon performing the Vlookup or other data of your choosing as long as it is unique to each record and contained in both files.
 
  1. If the record name is not unique enough to for the Vlookup to populate the IDs from your export file into your import file you may consider combining data from multiple columns in order to make a more unique value in each file for matching purposes while running the Vlookup.
 
For example, if your import and export files both contain name and phone values for each account in columns A and B you could use an Excel formula to combine these two column's values together in a new column. In Excel's formula bar type:
 
=(A1&B1)
 
Where cell A1 contains the Account's name value and cell B1 contains the Account's phone number and press Enter. The result in the formula's column should read with the syntax: [AccountNameAccountPhoneNumber]. Be sure to populate the formula for all rows in Excel and convert the formulas to values by highlighting the formula's column, right click, copy, right click in the same location again, select Paste Special... and select Values. Converting to values allows you to reference the combined columns as matching criteria for your Vlookup in order to bring over the Salesforce record IDs into your import file from your export file.
 

3. Check the results of your Vlookup operation to ensure that all potentially matching records have been populated with record Ids

 

  1. If the Vlookup was successful your import file should now contain existing Salesforce record IDs. When Excel does not find an exact match based on your selection for matching criteria it will display a value of "#N/A". If all records are displaying #N/A it indicates that no matches for existing records in your import file were found. If you're confident that there should be matching records in Salesforce reference the official VLOOKUP function documentation for troubleshooting common issues and their resolution.
 
  1. Once you've confirmed the Vlookup was successful convert the formulas to values by highlighting the column, copy, right click Paste Special...  Values. Perform a find #N/A and replace with blanks via Excel's search and replace feature (Ctrl + F) to clear out the #N/A values.
 
  1. Use Excel's sort feature to organize both files by the column containing the data that you used for matching in the Vlookup operation. Manually check rows with a blank record ID and cross reference them with your export file in order to populate any remaining record IDs for close or known record matches that the Vlookup function may have missed due to the values not being an exact match between files.


This is a manual process and may take a fair amount of time but will prevent duplicates, ensure that records are updated with relevant information, and improve overall data integrity on import.

 

See also: 
Import Data with the Data Import Wizard 
Insert, Update, or Delete Data Using Data Loader 
 

Numéro d’article de la base de connaissances

000387582

 
Chargement
Salesforce Help | Article