I love Data Loader.
Seriously, it is one of my favorite things. I remember my excitement when, as a baby admin, I downloaded Data Loader and discovered the magic of updating thousands, tens of thousands, hundreds of thousands of records at a time!
I also remember the intense frustration of my biggest Data Loader mistakes. There are so many settings and formatting quirks that are just too easy to miss! Here are my Top 10 Data Loader gotchas:
Batch size: You may want to adjust the batch size (found in Data Loader Settings) of an insert or update, based on how much automation and code your org has in place. If a lot of workflows, processes, and/or Apex code will be firing during your mass update, it’s a good idea to reduce the batch size to avoid getting too many errors. Whenever I get “record locked” or “Apex CPU” errors in my Data Loader error log, I reduce the batch size by half and reload the records that errored out. Pro tip: want a bigger batch size? Check the Bulk API box and you can update thousands of records per batch instead of hundreds.
Null values: This is a great feature with a big gotcha. If you want to insert null values into fields during your update, check the Insert Null Values box. It will delete any values in the fields that you’ve left blank in your upload file. Here’s the gotcha: that box stays checked until you uncheck it! Always take a look at your Data Loader Settings before you begin a upload, to see if you’ve left that box checked. Vote for this idea to get a warning when the box is checked.
Lead assignment: Many admins don’t know this, but you can run a set of leads through your lead assignment rules by entering the Assignment Rule ID into a box in the Data Loader Settings. Awesome, right? Sure… until you go back to do a lead update and accidentally reassign over 400,000 leads because you left that ID there. (In case you were wondering – yes, that is a true story!) Again, this is in your Data Loader Settings – always make sure the Assignment Rule setting is blank if you do not want to reassign leads while you are mass updating them.
Sandbox vs. production: Don’t forget to change the login URL when you switch from your production org to sandbox, and vice versa! Otherwise you will be slamming your head against the wall, wondering why your username and password aren’t working.
Security token: If you are using password authentication, don’t forget to add your security token at the end of your password. Pro tip: I have a folder in my inbox that is specifically for security token reset emails, because I have so many Salesforce orgs and sandboxes that I update with Data Loader.
Missing objects: Can’t find the object you are looking for in the object list? Check the Show all Salesforce objects box – it will expand the list to include objects such as Opportunity Product, Contact Role, Price Book Entry, and many more. Check out this great idea for a setting to default this box to be checked – and vote it up!
Date formatting: My least favorite mass uploads include date fields – or worse, date/time fields. Unfortunately, the super-detailed documentation that existed in the past has been removed. But I did find this question in the Answers community that offers a great answer (including screen shots of the lost documentation) for both date fields and date/time fields. Note: this is for US-formatted dates.
Converted leads: I almost called this one “files that you get from other people” – because one of the biggest gotchas of all is bad data. And your users won’t always know the best way to export the data you need for a mass update (non-.csv-formatted files, no record IDs, etc.). But converted leads is one of the biggest causes of errors that I’ve ever had, so it gets its own place on the list. Make sure that when you export leads from a report, you’ve filtered out converted records. And if you got the file from another user – make sure that they did the same!
Validation rules: This one bites me in the backside almost every time I mass update records. In a perfect world, we would never create a validation rule without first going back and making all existing records meet the rule. Any of you live in that perfect world? Yeah, me neither. If you are updating old records, you may get a lot of validation rule errors – so make sure you’ve deactivated any rules that you think might be troublesome. Warning: don’t forget to turn those rules back on afterward! Vote for this idea to disable validation rules during Data Loader updates.
Email alerts: Do you have workflow rules or processes that send email alerts when records are created or updated, or when a specific field is changed? Make sure you are aware of any alerts that may be fired by your mass update, so that you can shut them off if necessary.
Whether you are an experienced admin or just starting out, these are easy mistakes for any of us to make! Watch out for the gotchas and your data loading will be much easier.
Bonus gotcha: Make sure you download the latest version of Data Loader (Setup | Data Management | Data Loader) before TLS 1.0 encryption is disabled in March!
Converted Leads! I would rather that Salesforce by default not show converted leads and make you select a setting if you want to see converted leads.
I completely agree!! Especially since most of my users don’t know to add “converted = false” criteria when they run a leads report. :/
Great, helpful article for someone like me that is just getting reacquainted with Data Loader after using Demand Tools for years with a previous employer. Your link to the updated version is broken, though. Tried to click through to make sure I have the updated version but it takes you to an error page.
Thanks Justin! I’ve removed the wonky link. ?
Great post. Your blog is amazing overall and your tips and tricks are some of the best I’ve ever come across!
Thanks Matt, that’s very kind of you! ?
You can also include checks of custom settings in your validation rules, to disable them for yourself only.
“Validation rules:… If you are updating old records, you may get a lot of validation rule errors – so make sure you’ve deactivated any rules that you think might be troublesome. Warning: don’t forget to turn those rules back on afterward!”
We normally build in Admin bypass rules into our validation rules =) && $Profile.Name != “System Administrator”
Great solution to that issue! (but first make sure that only admins have the admin profile…)