[vc_row][vc_column width=”1/1″][vc_column_text]Powerful data migration tools may not seem like tech-crush material, but when those tools save you countless hours of tedium, you may find yourself suddenly enamored. Thanks to Trish and Thomas, you’ve recently mastered some cool Excel tricks, like concatenating and separating data columns, and that’s a great start. Let’s take your relationship with data to a higher level.
In this post, I’ll show you how to get data into Salesforce by using one of the available ETL (Extract Transform Load) tools. As the name implies, ETL apps allow you to export data, make modifications to it, and then bring it back into Salesforce. They give you access to all Salesforce data objects, and don’t limit you to 50,000 records, so they’re much more powerful than the Salesforce Import Wizards.
Ask different consultants what ETL tools they use, and you’re likely to get a few different answers. Some of us show a fierce loyalty to our particular favorite. Personally, I like to pick and choose, and use different tools for different jobs. Each has its strengths, and most can, on their own, do almost all of what you’ll need. There will be those special occasions, however, when you face a particularly tricky situation, and need to seek the comfort of another’s embrace.
This isn’t a marriage or a religion, it’s okay to play the field a little, and remain tool-agnostic. To that end, I’ll look at the apps I use most, and the chores I like to use them for.
First up, it’s the Apex Data Loader, a desktop app that you can download via the “Data Loader” link found under Setup>Administration Setup>Data Management.
The Apex Data Loader is one of my stand-by’s. It’s a solid, no-frills workhorse that’s really good at the “E” and the “L” (exporting and importing). The import comes in three flavors: insert, update, and, perhaps most importantly, upsert. What’s an upsert? It’s a linguistic mashup of “update” and “insert.” In plain English, it means, if you include the Salesforce ID or an external ID with your records, Data Loader will try to find a match for it. If it finds the ID, it will update the existing record. If it doesn’t, it will create a new record.
Upserts are great if you need to regularly update records with information that comes from an external system. You’ll of course need to make sure you load that system’s IDs with the data when you first create the records. If you want to make use of the insert aspect of the upsert, you’ll also need to include all of a data object’s required fields (for example, the last name of a Contact), or it will generate an error for any new data. If you want to use an External ID for updates, rather than the Salesforce ID, you need to use upsert even if you don’t want to create any new records.
Why you may love it
What are some of the other features that might endear the Data Loader to you? Let me count the ways:
- It’s free to everyone, with no applications or licenses necessary. You just need a Salesforce login.
- It generates a pair of success and error CSV files with each operation. If you have errors, the Error file includes a column with useful record-by-record messages, such as whether an email was in the wrong format. This will usually help you track down what went wrong, though some messages are clearer than others. The Success file appends the Salesforce ID to every record that was successfully created. This is very handy for running updates later or fixing something that wasn’t imported correctly on the first try.
- There’s an Automap button for field mapping. If you use the Salesforce labels (or field names) as the column headers for your import file, this will greatly speed up the mapping process.
- You can save your field mappings to a file (.sdl format), which makes repeating the same operation in the future a snap.
- You can perform mass delete operations (for unlimited records, and all tables, unlike the Salesforce Mass Delete feature), but you need to use the Salesforce ID for the records you want to mark as deleted.
- The interface is very basic, and, for the most part, easy to figure out. If you like things a little retro, this will scratch your itch.
But nobody’s perfect
Like any good relationship, there are some flaws with which you’ll need to come to terms, if you turn to the Data Loader:
- The application is external to Salesforce. It’s installed on the desktop, and you’ll need to check for updates yourself (the current version is 27.0.1).
- There’s no support, other than a help file, so this is definitely a better option for the autodidact demographic. To see the Data Loader’s documentation, you can go here.
- It remembers your username, but it does not store passwords, so you’ll have to enter your password (plus security token, depending on your network access settings) each time you start the app.
- You can only import CSV files. This isn’t a huge liability, but some of the other ETL options out there do give you more flexibility when it comes to file formats.
- As I mentioned above, it’s really good at the “E” and the “L” but doesn’t do any real “T” (Transforming, or modifying data). That you’ll need to do with a program like Excel (where you can play with your fancy formulas), and then import it back in as a CSV.
- It only works with Windows-based machines. For Mac users, there’s LexiLoader.
- It has an extremely basic interface, with no bells or whistles. Again, the cloud-based version is a little slicker, so that may be a better option if you want a nicer looking, more intuitive app.
It’s got its quirks
It’s not obvious at first, but there are a set of menus available with some useful options. To get to them, you have to Cancel out of the initial screen that pops up when the App Starts. The most important of these is Settings, which opens up a form where you can change the defaults to, for example:
- Adjust the batch size or Time Out duration
- Insert NULL values (check this box to clear existing data from fields by importing blanks)
- Load data into a Sandbox (by changing the Server Host from https://login.salesforce.com to https://test.salesforce.com).
- Plus much, much more geeky goodness
You’ll see two different export options: Export and Export All. The former is your garden-variety, vanilla-bean type of data extraction. The latter includes any deleted (but not yet removed) records from the object exported. If you only want to export a subset of records, you can also create field-based conditions for what to include (There’s a field/operation/value selector, but these are written as SOQL queries, eg., “Select Id, Name, BillingState FROM Account WHERE BillingState = ‘IL’” , which you can edit if you know the syntax).
Note that not all tables are displayed at first in the tool. You have to check the “Show all Salesforce objects” box to see everything. Most of the standard objects and any custom objects you’ve created, will display in default mode, with the notable exception of Campaigns.
You should also generally try to use a System Administrator profile to log in, since table and field access are regulated by the profile’s security settings in Salesforce. Also, if you’ve changed the default labels for any of the objects (such as Opportunities to Donations), the customized names for the objects will appear in the list.
The Future is in the Cloud
Last year, MuleSoft debuted the Dataloader.io, a cloud-based cousin of Salesforce’s desktop app. It includes most of the functionality of the desktop app, including the ability to upsert, with a much nicer look and feel.
Other improvements include:
- The initial login screen let’s you pick between a Production or Sandbox environment, which is much better than having to mess with a settings screen.
- Field mapping is done automatically, without the need to press a button, if the app can determine a match based on the column names.
- It allows you to reference lookup fields such as Record Type by Name as well as ID.
- The Success and Error files are available to download via links on the results screen.
One setting I wasn’t able to find in the new version, was the “Insert NULL Values” option. It’s not something I use a lot, but it does come in handy occasionally, when I need to clear data from a field. It’s possible I’m just overlooking it, in which case, if you find it, please leave a comment and let me know where it is.
As I’ve mentioned, the Apex Data Loader (and MuleSoft’s web-upgrade) won’t do everything you might need, especially in the Transform category. To find and merge duplicate records, or to run updates on sets of records you select with queries, you’ll need a different ETL tool. Next up, I’ll take a look at those third-party ETL apps.[/vc_column_text][/vc_column][/vc_row]
