Salesforce Data Loader Tips: Preparing Your File

Updated on July 30, 2019

Here at StarrData, we’ve helped many customers with their data transformation and import needs. We’d like to share some tips with you on how you can be successful while using the Data Loader tool.

We know the Data Loader process isn’t always the most straightforward, but these tips come from our experience, and we hope they help you as much as they have helped us over the years.

Preparing your file for Salesforce Data Loader:

•     The import file must be in CSV format before uploading; however, frequently it is easier to work in Excel format to preserve formatting if you can’t finish all of your data transformation/editing in one session. When finally saving as a CSV, you may want to open the file to ensure there is no corruption upon conversion, but re-save as a CSV prior to uploading to preserve date formatting.
•     Ensure you do not have any empty columns or any columns with the same header names as this can cause mapping issues.
•     Ensure you have a column header and rows of data populated for all system required fields such as like Account Name or Contact Last Name
•     In your Excel file prior to saving as a CSV, leverage conditional formatting functionality to highlight cells with duplicate values in columns that should not have duplicates
•     CSV files do not preserve formatting so if you do any data formatting in Excel and then convert to CSV, the formatting needs to be reapplied
•     If you apply formatting to a CSV file, save it but keep it open during the import to preserve the formatting
•     In Data Loader, fields with date values should be formatted as YYYY-MM-DD and fields with date/time values should be formatted as YYYY-MM-DD HH:MM:SS. This assumes that you have set Data Loader settings with your local time zone. If not, please see the Salesforce Help Topic “Data Loader Date Values” for other options.
•     If you’re working with Zip Code data that has leading zeros, Excel will remove these zeros. Leverage the Zip Code Cell Format to put them back. Remember, you will need to do this formatting again if you convert from Excel to CSV.
•     You can use Excel cell format of “Phone Number” to format phone and fax columns
•     Ensure email addresses are correctly formatted. Salesforce email fields will not accept bad email addresses and will cause with bad addresses to fail. Bad addresses include information like a comma instead of dot or mailto: in front of the address.
•     Remove all currency symbols and commas from number and currency columns.
•     Salesforce will not accept text in number fields.
•     For checkboxes, use “1” or TRUE for checked and “0” (zero) or FALSE for unchecked. You can also leave unchecked cells blank if you like
•     Data Loader does not allow importing multiple columns into one field in Salesforce. You will need to concatenate these fields before uploading
•     Data Loader does not support Address 1, 2, and 3 fields, so if you need to import more than one street address for a record, see the Salesforce Help Topic “Account Billing Address Fields”

o   Below is a formula that looks for blank cells and does not put in extra line breaks if a blank cell is found. Be sure to change the cell references to match your file

• =CONCATENATE(IF(ISBLANK(A2),””,A2),IF(ISBLANK(A2),””,CHAR(10)),IF(ISBLANK(B2),””,B2),IF(ISBLANK(B2),””,CHAR(10)),IF(ISBLANK(C2),””,C2))
•     Salesforce picklists will accept any values imported into them (up to the character limit) so ensure that your picklist columns have the proper values
•     You can only import one object at a time so if you will be importing multiple objects, create a file for each and put them in separate folders. This will help you to stay organized later in the import process
•     Once your file is ready for import, copy the top row (headers) and about 4-5 rows below to a new file so you can do a test import. It is easier to delete 5 test records than 5000 real records
•     Try to name your column headers with the same names as your Salesforce fields to make mapping easier

If you follow these guidelines, your file will be ready for the Salesforce Data Loader! And read part 2 of this blog post, Salesforce Data Loader Tips: Mapping and Importing.