Using a data import tool may not be a personally transformative experience, but it should be transformative for your data. Jitterbit is another good and free choice you have. Its features may not change your world, but they’ll allow you to do some neat things when updating or adding information to Salesforce. If you’re a data geek like me, that always provides a little thrill.
On the spectrum of ETL (Extract – Transform- Load) tools which I’ve discussed here already, I’d place Jitterbit ahead of the Apex Data Loader and Dataloader.io. It doesn’t have all of the advanced functionality of Apsona or Demand Tools, but for purely importing data, you’ll be hard pressed to find a better tool.
Like Demand Tools and the Data Loader, Jitterbit is a desktop application. You can download it for free from the Jitterbit site. Jitterbit also provides more advanced integration functionality if you purchase their Professional edition.
What’s it do?
The free Jitterbit Data Loader does all of the core tasks you’d expect from an import tool. You can insert new records, update existing records, and perform upserts (matching to existing records based on an ID, while inserting any that aren’t found). Exports are done through Queries, where you can select the fields to include and any conditions to limit what’s exported. You can also delete records, and Jitterbit is smart enough to know based on the IDs you specify, which Salesforce object to delete the data from.
The interface is very intuitive, and the various wizards walk you through the set-up steps for each operation. Field mappings are drag and drop, and there is an Automap button. In addition, Jitterbit gives you the ability to refresh your Salesforce meta data while still in the Field Mapping step, which is useful if you spot a field that’s missing from Salesforce and want to add it as a custom.
Similar to the Data Loader, you can access Success and Failure files after each operation is run. You can also choose an “All or None” option if you want the import to fail if it encounters any error.
What sets it apart?
Connections: Not only does Jitterbit let you create and save a connection to your Salesforce org, you can set up multiple connections and easily switch between them. This could be useful if you have different sandboxes you need to connect to, or if you, like me, have lots of different Salesforce orgs that you regularly log in to.
Scheduling: Do you want to create a job once and then forget about it? Jitterbit is perfect for that. You can specify the operation, the source object, and the target file, and set up a schedule for it to run on, either limited to a date range or forever.
Flexibility: You’re not limited to importing data from a CSV file located on your hard drive. Jitterbit offers the ability to connect to a file stored on an FTP server or via a File Share. For everything but Bulk Load operations, you can also use an Oracle, SQL or MySQL Database as your source and target files.
Transforms: This is the big thriller for the data geek. Do you get source files that range from not-quite to nowhere-close-to ready for import? Of course, you do. Maybe the data was pulled from a Neon database, where they store their phone numbers and area codes in separate fields, and their Date of Birth info is kept in a separate day, month, and year fields. Or it’s a spreadsheet where users have input the first and last names or the addresses all in the same field.
In its field mapping step, Jitterbit allows you to define “Transforms.” These are expressions you can build with the field references and a set of functions (similar to what you’d find in Excel) that will alter the values at run time. You can even use them to import the same value to all records.
Double-click on a field in the target object and a new window will open up where you can define your Transform. The name of the field you mapped from the source file will appear between <trans> tags. Below is a list of functions you can apply to it. You can even add other fields from the source file to your expressions. Jitterbit gives you the ability to test it before saving.
The most useful transforms, at least based on my experience so far, are the String functions such as LEFT, RIGHT, MID, TRIM, REPLACE and SPLIT. There’s a lot more there, and the truly geeky will want to explore and test them all. Unfortunately, Jitterbit’s free Data Loader doesn’t allow conditional transforms. You’ll need the Professional edition for that.
