Salesforce Data Loader Tips: Preparing Your File

Here at StarrData, we have helped many customers with their data importing and manipulation needs and we would like to share some tips with you on using the Salesforce Data Loader. These tips come from our  experience working with this tool and we hope they help you as much as they have helped us over the years.

Preparing Your File for the Salesforce Data Loader:

• The file must be in CSV format before uploading but sometimes it is easier to work in the Excel format to preserve formatting upon saving. When saving as a CSV file, be  sure to close the file and then open the CSV file to ensure there is no corruption upon conversion
• Ensure that you do not have any empty columns or any columns with the same header names as this can cause mapping issues later
• Ensure you have a column and data for any system required fields like Account Name or Contact Last Name
• In the Salesforce Data Loader use the Auto-Filter to spot check cells for proper values
• Use conditional formatting to spot 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 the Salesforce Data Loader Date values should be formatted as yyyy-mm-dd
  • This assumes that you have set the Data Loader settings with your time zone. If not, please see the Salesforce “Data Loader Date Values” help topic for other options
• In the Salesforce Data Loader Date/Time values should be formatted as yyyy-mm-dd hh:mm:ss
  • This assumes that you have set the Data Loader settings with your time zone. If not, please see the Salesforce “Data Loader Date Values” help topic for other options
• Excel will remove leading zeros from zip codes so use the “Special Formatting” option of “Zip Code” to put them back
  • Remember to do this again if you convert from Excel to CSV
• You can use the Special format of “Phone Number” to format phone and fax columns
• Ensure email addresses are correctly formatted. Salesforce email fields will not accept bad addresses and will stop the import of records with bad addresses like 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
• The Salesforce Data Loader does not allow importing multiple columns into one field in Salesforce. You will need to concatenate these fields before uploading
• The Salesforce Data Loader does not support Address 1, 2, and 3 so if you need to import more than one street address for a record, see the “Account Billing Address Fields” Salesforce help file
  • 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 records than 5000
• 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.

StarrData helps you get the most out of Salesforce. If you would like information on the services we offer, call us at (888) 391-4493 x103.