Thomas Edison famously said that “Genius is 99% perspiration and 1% inspiration.” If he’d been a Salesforce user, he would have said, “Data upload is 99% preparation and 1% migration.” Data migration is the task of transferring large amounts of data into Salesforce from another platform. To accomplish this you will typically use Microsoft Excel or another spreadsheet program that allows you to create “common-separated value” or CSV files. In this blog we will review some of the commonly used tools that are built into Excel to make it easier to prepare your data BEFORE you mass-migrate it into Salesforce, ensuring that the data you migrate is as accurate as possible. After all, you don’t need to be a genius to know the expression, “Garbage in, garbage out”!
NAMES MUST BE STORED IN SEPARATE CELLS
Some database products expect you to put a person’s full name into a single cell. Salesforce, however, requires that the First Name and Last Name be separated.
BEFORE:
Name
Buzzina Lightyear
Destiny Husband
Adam Levine
Kelly Preston
Before you start trying to copy and paste each last name separately into a new cell, try this:
Excel Tip: Text-to-Columns
Use Data | Text to Columns to separate data into two columns:
- Create an empty column to the right of the Name column (Select | Insert Columns)
- Select the Name column
- Click Data | Text to Columns
- Click “Delimited”
- For Delimiter Select “Space” (Tab is default)
- Click Next and Finish
- If you get an error message that Data is Already There Click OK
- Rename fields as “First Name” and “Last Name”
AFTER:
First Name | Last Name |
Buzzina | Lightyear |
Destiny | Husband |
Adam | Levine |
Kelly | Preston |
FIXING UPPER AND LOWER CASE
For good or ill, Salesforce does not have a mind of its own. Therefore when it’s time to print out mailing labels for the annual appeal, the names will appear on the envelopes just the way they appear in Salesforce:
BEFORE:
First Name | Last Name |
Jennifer | Aniston |
BRAD | PITT |
aNGELINA | jOLIE |
To make the case of your records consistent use the following function:
Excel Tip: =PROPER
If your data is not consistent as regards case, you can fix it in Excel with the =PROPER formula
- Create blank column to the right of the relevant data column
- Type “=PROPER(“ into empty cell at top of column (under title row)
- Select first data item in relevant column to place cell address into formula
- Press OK.
- Click handle in lower right of new cell to copy formula to entire column
- Create a new blank column to the right of the column with the formulas
- Copy the column with the formulas
- Use PASTE SPECIAL to paste as Values
- Delete the original column and the formula column
- Rename the new column with the title of the original column
AFTER:
First Name | Last Name |
Jennifer | Aniston |
Brad | Pitt |
Angelina | Jolie |
JOINING TWO COLUMNS
We saw above that some data, like names, needs to be separated. You may also find data that needs to be joined together. In Salesforce’s Mailing Street field, for example, it is best practice to include both the street and apartment or suite number, while many other products separate these:
BEFORE
Street Address | Apartment |
1 Music Row | #3 |
1 Love Tonight Street | #14 |
Excel Tip: =CONCATENATE
- Create an empty column to the right of the Apartment Colum and name the column “Mailing Street”
- Type =Concatenate(B3,“ “,C3) in the blank field (e.g. D3):
B3 | C3 | D3 | |
3 | 1 Music Row | #3 | =CONCATENATE(B3,” “,C3) |
- Copy the formula to all cells that have apartments
- Copy the column with the formulas
- Use PASTE SPECIAL to paste as Values
- Delete the original column and the formula column
AFTER:
Mailing Street |
1 Music Row #3 |
1 Love Tonight Street #14 |
AND JUST FOR US NEW ENGLANDERS:
In New England, we have a”0” as the first number of our zip codes, e.g. 02114. That does not ordinarily present a problem, but one of the characteristics of CSV files is that they represent zip codes as ordinary numbers, and so they won’t tolerate a number beginning with “0”. (Note, this is not an issue with 9-digit zip codes like “02114-2474”).
So the LAST THING you should do before migrating your data is to make sure the zip codes are fixed:
BEFORE:
Zip/Postal Code |
98756 |
2110 |
2378 |
90210 |
2589 |
Excel Tip: Zip Code Formatting
- Select the Mailing Zip/Postal Code column
- Click Format Cells.
- Under Category Click “Custom”
- Under Type delete the word “General” and type “00000” (five zeros)
- Click OK
Note that if you close your CSV and re-open it, the “0’s” will disappear again. Fortunately once you get them into Salesforce, you will not have to do this again.
AFTER:
Zip/Postal Code |
98756 |
02110 |
02378 |
90210 |
02589 |
Excel includes many other tools that can be useful in data preparation. These are just a few, but we use them just about every time.