Tips for Data Loading, and the best (free!) options
Updated: Jul 1, 2020
What’s Data Loading?
Entering data for a record in Salesforce is part of the platform’s core functionality. But what if data needs to be added, updated, or deleted for five records? Or 25? Or 8 million?
Depending on the amount of data that we’re talking about, it could be mildly irritating, a major pain in the bum, or simply impossible to make those changes manually. Data Loading is the term we use for making mass updates to our data, and there are a number of tools available to help us do it.
The most common use cases for a Data Loading tool are:
Exporting data from an org,perhaps to migrate to another system, make a back-up, or create a “sample” of our data that we can then import into a sandbox org.
Inserting data, either from another system, another org.
Deleting multiple records.
Updating multiple records.
Unsurprisingly Salesforce’s AppExchange is a good place to look for Data Loading apps. Most of them are not free, and as we all like free stuff (and there are dozens of apps) I’m only going to talk about a few of the options that are free (but still with great features).
Data Import Wizard
Salesforce has it’s own built-in Data migration tools, called Data Import Wizard (for importing data) and Data Export Wizard (for, well, you know). They’re accessed within your org’s Setup, and are really easy to use because the formatting is similar to the native editing environment in Salesforce.
This is an external application which can be used via the web app at dataloader.io or the desktop app. The web app has free and paid plans so be sure that the free plan meets your needs.
Jitterbit’s Cloud Data Loader
Part of Jitterbit’s Harmony platform, but can be used in isolation at no cost. There’s no web app but installing the desktop app is so easy even I could do it.
So which is numero uno?
The specific use case, as well as personal preference, has a big impact on which tool would be most suitable. Some people I know really like the Data Loader desktop app; I’m more of a fan of Jitterbit’s offering. But they do have some limitations which no amount of fanboyism can ignore, so here’s a summary of the features of the three tools.
Data Loading — Best Practices
No matter which option you use, how you use it is obviously very important. So here are my top 6 best practices for importing and exporting data to your Salesforce org:
If you’re updating or deleting data, make a backup of your data first. Always.
Test a batch of records first. Five should do the trick. If you’re importing or inserting records, remember not to include those five records when you do the actual import!
If something goes wrong you can use the Mass Delete feature to delete up to 250 records at a time without the need of a Data Loading tool.
Disable workflow processes, validation rules and Apex triggers. If your Sales Director gets an auto email notification whenever a record is added, and you mass-insert 50,000 of them, he’s not gonna be a happy chap.
Ensure that roles, profiles and sharing rules permit the required levels of access before performing the data import, especially if the data is sensitive.
Create a data template for each object, typically the field names. If records are already in the database, exporting a record will give you a CSV file with the required fields (i.e. your data template!)
We all love a nice, smooth data load, don’t we? There are some things that can be done to help make this a reality. Here are my top 6 tips on cleaning data before it’s inserted or updated to your org:
Consolidate values. Have one common value, so instead of “SF”, “San Francisco” and “San Fran” all referring to the same thing, have one true value.
Use existing picklist values. Replace values in your data that are not in the object’s picklist value set with values that are.
Use the correct parent record IDs, so child records will be related to the correct parent when they are imported.
Reduce the possibility of importing dirty data by using Lookup fields, automated field updates (with a Process Builder for example), or formula fields in your org; you can then omit the data values for those fields from the import completely!
Replicate the object’s field names in the data import file’s column names. This will help the “automapping” feature of the data loading tool to map the columns to the right fields.
Include the Record Owner ID for each record if possible. Otherwise, all imported records will get the user who imported the data as the Record Owner.
Using Excel to help clean your data
Excel can be used in a number of ways to clean your data. Here are my top 6:
The Sort function to sort by a column; this then helps to manually see duplicates.
Filter helps to see records with similar field values, which can then be consolidated to a single value.
If the source data file contains formulas, Save As CSV saves the resulting values, not the formulas.
Find and Replace helps to standardise fields by replacing or deleting data (for example removing letters so only numbers remain).
Concatenate combines values from more than one column, which is useful for things like combining a phone area code and phone number if they are in separate columns. (And Text to Columns does the opposite).
Format Cells helps standardise values. So if you need to ensure every value in a column starts with a 0, this is your guy.