I use Salesforce Dataloader a lot – I’m currently using v 60.0.2 . It’s a fantastic tool but there are a few tricky things to remember when using the desktop app. Below I explain how to avoid issues.
Data Loader allows users (or rather those with permission) to create, update and delete Salesforce records in bulk. Bulk uploads are ideal for data migrations, converting lists (for example Leads) in Excel into Salesforce records, and tidying up data en masse.
I love Data Loader because:
- It’s easy to use for all objects (custom and standard) – with a drag-and-drop interface
- I can upload up to 5 million records – though I think the most I’ve ever uploaded at once is nearer 1 million rows of data
- After upload, you can access a detailed Success and/or Error log, which explains why certain rows may have failed to be uploaded
- It works in test and production/live environment
- Once you have mapped your file to a Salesforce object you can easily save the mapping as a mapping file to be referenced next time
- Data Loader can also auto-match your file columns to Salesforce fields
Top Salesforce Data Loader Tips
Data Loader is a great tool and is being updated and improved all the time, however, there are always things that may catch you out. To save time trying to understand and fix any issue loads, I have created a list of Data Loader tips to help Salesforce Data Loader users.
(1) All data needs to be uploaded using CSV files – simply convert any .xls file into CSV files when saving the document from Excel. Please bear in mind that there are various types of CSV files – see below. Using the wrong format may throw errors from Data Loader.
- CSV (comma delimited). This format saves data in a comma-separated text file that can be used in another Windows program. TIP: this is the simplest format and the one I use to avoid CSV error issues.
- CSV (Macintosh). Used on the Mac operating system.
- CSV (MS-DOS). Used on the MS-DOS operating system.
- CSV UTF-8 (comma delimited). It is Unicode Transformation Format 8-bit encoding that supports many special characters. If your data uses special characters either replace these special characters and use the standard CSV format or try this CSV UTF-8 format.
(2) When searching for Object names, be sure to tick the ‘show all objects’ tickbox or you might miss what you’re after. Often Data Loader users will complain that their custom objects are not available, but then the object appear once this box is ticked. Saying that not all objects may be visible if you are using an older version of Data Loader. If your custom object isn’t visible then look to update your version of Data Loader.
(3) SQL 101 errors – if you get this error when uploading then take a look at the Settings in Data Loader and change the batch size to 100. Smaller batches result in fewer of these types of errors, however, smaller batches do take slightly (seconds) longer to run.
(4) Data preparation is the most important thing. Using Data Loader can create many types of errors, most of which could have been avoided if more time had been spent cleansing, mapping and preparing data before attempting a data upload. Similar to painting a house, most of the effort is in preparation. The painting (or uploading) is the fun part. Things in your data to look out for are:
- Do you have two columns with the same name?
- Have you already mapped the columns in your data to fields in Salesforce? Best not to guess during upload.
- Have you copied your data down to the bottom row of the spreadsheet? Classic error.
- For checkboxes, use “1” or TRUE for checked and “0” (zero) or FALSE for unchecked.
- Check the format of all your columns. Number fields won’t accept text. Date fields need to be in the correct format. Emails need to be in the correct format. This may all sound basic but people do forget to check.
(5) Use the correct data formats. I always use the Military Time Zone format: yyyy-mm-ddThh:mm:ss.sssZ
This format always works. Users often face issues with dates not importing correctly. Users may be set to different time zones, US and UK date formats get confused, and the wrong dividing character is used, these are all prime examples of common errors. But use the Military Time Zone format and you’ll be pleased.
See: Issues With Uploading Dates Using Salesforce Data Loader? – Ian Jamieson
(6) Use Salesforce 18 character IDs – nice and simple. Don’t use 15-character IDs, it won’t let you.
(7) You can only upload to one object at a time. If your data covers multiple objects, you will need to upload to each object in the correct order as dictated by your data.
I hope these points prove helpful with your Salesforce Data Loader dilemmas. Please let me know in the Comments below if you have any further suggestions on how to improve your Salesforce data-loading experience.
