How to Import Data to Salesforce using Data Loader

It is inevitable that at some point you will need to import data into Salesforce, and if the number of records exceeds 50K, you will not be able to complete the task using the native Data Import wizard. There are a number of data management tools that can be used, and I am referencing Salesforce Data Loader for this post, however many of the following steps are relevant to any tool being used.

The following are the high-level steps for importing data files. Of course, before you begin any import session, you should put on good music, stretch and take deep breaths, and caffeinate adequately.

1. Backup existing data
2. Review your configuration
3. Create a field mapping file
4. Build and cleanse the import file
5. Deactivate validation, workflow, or Lead/Case assignment rules and triggers
6. Perform a test import of a sample set of records
7. Verify the records in Salesforce
8. Resolve errors if applicable
9. Import the remaining records
10. Verify the records in Salesforce
11. Reactivate validation, workflow, or Lead/Case assignment rules
12. Perform tests to validate any reactivated rules

Backup Existing Data

Do I even need to say more? Backup the current data of the objects you are importing and any other related objects. Don’t be stingy; backup as much as you can, if not everything.

Review the Configuration

The goal of this step is to understand your data model and identify any mandatory required fields, along with all applicable validation, workflow, or Lead/Case assignment rules and triggers for the target objects. Take the time to fully think through the relationships and how the creation or updating of records will affect your Org. Note if any sequencing will be needed, for example, you will need to create the account records before related contacts. Understand the criteria used to trigger rules and whether your import can possibly meet those criteria.

Create a Field Mapping File

You will now document the configuration, capturing where each field will go and ensuring that the data types match.

First, I suggest that you create a folder structure for each data file, it may seem obsessive but trust me, you may need to retrace your steps and you will thank me.

• Date and file name (ex. 2017-03-22 Contacts East Region Import)
• Original Data Files (store originals)
• Working files (this is where you can store .xlsx files that contain formatting and formulas such as vlookups)
• Import Files (store .csv files)
• Success and Error Files (Data Loader specific log files)

It may be that you have multiple objects as targets and will need to pull related field id’s from your org before you begin. Don’t forget record types and record owners, or any other fields that you may want to input when importing that may not exist in your import data. You may need to create new custom fields to house some fields. Pro Tip - Use a unique ID field in case you need to later import other fields that were left off the list. Also, note in the screenshot example below that I used the Salesforce “API Field Name” - this will be extremely helpful, as you will see later on. As a bonus, this file can be reused for every similar data load, just be sure that you update it when configuration changes are made, i.e. new fields are added or picklist values changed.

Build and Cleanse the Import File

This is a critical and time-consuming step that requires strict attention to detail. Listed below are some “gotchas” that regularly appear in source data and will cause errors. The steps below are intended to provide an example of working through an import file to ensure correct formatting and data integrity.

• Important note - when you are working with a .csv format file, any formatting changes will not remain intact if you close and reopen the file. Make formatting changes and save the file without closing just prior to importing.

• Open your import file and save it to the “Working Files” folder in .xlsx format. That will ensure that you can format and use formulas that can be saved. This file is your “audit trail” and can be referenced at any time.

• Review each column of data to identify discrepancies. For example, the “email” field requires values formatted as xxxx@yyy.zzz. If you see things like “Bounced” or “Unknown” delete them - they will fail upon import. You will need to understand the proper format or validation for each field.

• Check to see that fields such as “Owner” or “User” contain the Salesforce record ID and not the name. You can do vlookups to identify these IDs.

• Address may need to be manipulated to conform to Salesforce format and can be tricky depending on the source data. The target fields are Street, City, State, Postal Code/Zip where Street contains the separate street columns (35 Main St, Unit 2, Suite 116) combined into one column, and the street lines are separated by carriage returns.

• Ensure that “State” and “Country” matches your system configuration - are the “States” abbreviated? Check to see if your Salesforce org has State and Country Picklists enabled and format your data accordingly.

• Review all columns containing picklist fields to ensure that the data match the picklist values, including syntax. You do not want to load values like “inactive” when the picklist value should be “Inactive”. Additionally, you don’t want to introduce new picklist values this way - if you need to do that, add the new values to the field in Salesforce first.

• Look for improper syntax, for example, unless the First and Last names should be all capitalized, you want to fix this in your file prior to importing using the PROPER formula function in Excel or Google Sheets.

• Check for spaces before and after in the cells, you can use the formula function TRIM in Excel or Google Sheets.

• Salesforce Date and Date/Time fields can and will cause import errors. Review the online help and format accordingly. Reference this page -- you will refer to it regularly!

• Zip codes or Postal Codes should be formatted as such, especially if you’re working with Zip codes that begin with a leading “0”. In Excel, highlight the column and click Format > Cells > Number > Special and select Zip Code or Zip Code +4 and in Google Sheets, select Format menu item, select Number -> More formats -> Custom number format, enter 5 zeros or the number of characters you need for the zip codes, and save.

• Currency fields cannot contain the currency sign or commas. It’s always best to maintain a simple number format.

• True / False (checkbox) fields must contain just that - true or false; you can also use 1 (true) and 0 (false). Yes and No will cause failures.

• Save the file regularly as you work, and then save the sheet as .csv format in the “Import Files” folder. Here I recommend that you use a consistent format and include the date, for example, “2017-03-22 Contact East Region Import.csv”.

Deactivate Rules, Processes, and Triggers

Review each of these to determine if you want to leave them activated or deactivated. You may decide that the need is to leave some activated and deactivate others. Validation rules are fired when data is edited in Salesforce, whether you are updating or inserting records through the API (which Data Loader does). Your choices are to either abide by the rules in place or deactivate them temporarily for the import. Always keep in mind that Process Builder processes and Workflow rules can fire depending upon the entry criteria, and their actions can apply to the object and related objects (Parent object). You really do not want to send hundreds or even thousands of emails unnecessarily, do you?

Perform Test Import

Use a subset of your import file ensuring that you send data to each identified target field. This gives you a chance to see if you have possibly missed anything and to verify the records in Salesforce. Remember the folder structure above where you created the “Success and Error” folder? This is where you tell Data Loader to save these files and, if errors occur, review the error log file in this folder to identify the culprit. Resolve all errors and run the file again. Do this as many times as it takes until you achieve success. Pro Tip - After you run an import, immediately rename the success and error files generated using the name of the import file. For reference on how to use Data Loader, refer to the online documentation here and here.

Verify Records and Resolve Errors

If you encounter errors, review the errors file and note what is captured in the column labeled “ERROR” as it contains the information you will need to correct the issue(s). You may see things such as “invalid date” or “invalid email address”. Pro Tip - You can save the error file as a new import file, format as csv, and name it the same as the original file with a number or something else to indicate that’s it’s the 2nd pass. Make any corrections directly in this file, and remember that you will need to reformat columns. Repeat the steps to perform the import, and don’t forget to capture and rename the success and error files for each pass. The success file is important, as it contains the Salesforce IDs of the records created in the first column labeled “ID”. Pro Tip -  Create and use Salesforce reports to validate the imported records.

Import Remaining Records

Take one last glance through the import file to see if any potential issues stand out, then proceed to import the remaining records. It may be helpful to split your import into multiple files if you are working with a large data set; through experience, you can learn to determine what defines a large data set to you.

Verify Records in Salesforce

Wash, rinse, and repeat above steps until you import and validate all of the records. Take some deep breaths and pat yourself on the back once completed.

Reactivate Rules and Triggers

Activate all rules and triggers that were deactivated prior to the import.

Perform Tests and Validate Reactivated Rules and Triggers

Test, test, and test some more to ensure that things are in order. This is a good time to refer to training materials, follow different processes and note the results. Is your training material outdated or non-existent? This is a good time to capture a task (hello GTD) so that you can address discrepancies in the materials.

Backup Data Again For Good Measure

Why not? You will then have both pre and post-import backup files that can help you sleep better.

Document Lessons Learned

This was not included in the checklist up top, but it bears mention that it is always wise to reflect on what you learned and how you can improve the process going forward. Here you may want to again capture tasks identified from your reflection.

Importing data is an art, and an art requires skill and practice. You will improve each time you use Data Loader and go through the import process, following these steps and making them your own based on your experience.

Check out our referenced knowledge article Importing Data on the Salesforce Trailblazer Community for more information. 

What has your experience been? Want to share your thoughts or opinions? Please feel free to reach out in the Success Community, or to me directly via Twitter at @sfdcclicks.