How do I add existing record's Salesforce Ids in my import file?
|Knowledge Article Number||000220791|
|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.
To include existing Salesforce.com record Ids in your import file:
1. Extract existing records for the target object you're looking to run the import or update into:
- Build a Report and 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/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.
- Open both your import file and the report/export files in order to perform an Excel vlookup operation. You can use 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.
- If 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:
Where cell A1 contains the account's name value and cell B1 contains the account's phone number and hit 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.
- 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.
- 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.
- 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.
Import Data with the Data Import Wizard
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.
Data Import How-To Series
Insert, Update, or Delete Data Using Data Loader