4 Best Practices to Prepare Your File for Import

Are you doing a data import and unsure of how to start? Or, do you simply need a point of reference as you make some data updates? This article begins my series on Data Imports, which aims to help you with your data work whether you are inexperienced, or have done multiple data exercises. This particular article will help you prepare your file for import.

1. Map column headers to fields and rows to records.

This may seem obvious but sometimes we all forget this. For example, let’s say that you want to track “Updated Forecast Date” on Opportunities. This data has been sitting in Excel, on post-its, or maybe in your Sales Managers’ heads for far too long. So you go through the trouble of putting together a spreadsheet for import to update the existing Opportunities in Salesforce with these dates. However, you forgot to first create the field and so end up with 2,548 errors instead of 2,548 successes because there was no field in which to place that date.

2. Check for duplicates!

Whenever you insert new data, please check for duplicates. From the data cleanliness perspective, it is much better to clean up your data outside of Salesforce rather than in it. Be sure to check your file for duplicates and also check your rows against existing records in your org. Searching on email address is usually helpful.

3. Make use of your Excel formulas.

We always say that in Salesforce, formulas can do your heavy lifting; the same goes for your work in Excel. While most are familiar with VLOOKUPS, IF, SUM, etc., not everyone is creative with those formulas. Combine them! Here’s an example to get you thinking…

Sales Rep Mark Benioff is leaving my Company and we need to reassign his Accounts. He had so many accounts in Massachusetts that we’ve decided to split them up among different sales reps. However, he only had a few Accounts in Connecticut, so we’re assigning all of them to Tim Cook.

Instead of sorting, typing, copy-pasting… so on and so forth, the following logic can help me associate the Accounts with their new owners.

=IF(billing state is not blank, IF(billing state is MA, VLOOKUP(new sales rep,in sheet with user ids, bring back the column with the id, only bring back if exact match), assign to Tim Cook).

I used two IF statements and a VLOOKUP to take out a lot of manual work. These formulas can be more complex as well and this basic structure can help get you started.

4. Work from an .XLSX and then save a copy as a .CSV.

As you may have experienced, that formula we architected above wouldn’t be saved in a CSV file. If you reopened the file, you would find a column of static values instead of formulas. Additionally, CSV files cannot save multiple sheets. Yet, in order to upload data, either with the Import Wizard, the Data Loader, or third party tools, you need to use a CSV file.

So I prefer to use one XLSX file from which to do my work in, and then save a copy as a CSV file when I am ready to import my data. This way, your work is always reference-able in the future.

There are so many other tips, tricks, and things that are specific to certain kinds of data imports, but these four things are universal to all data exercises on all objects. If you found this helpful, be sure to check out the next article for a checklist to go through before and while importing your data.