Using Excel for Nonprofit Data Migration

By June 13, 2016 January 4th, 2022 CRM & Salesforce, Salesforce shortcuts and tips

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:

Data-Text_to_Columns

 

  1. Create an empty column to the right of the Name column (Select | Insert Columns)
  2. Select the Name column
  3. Click Data | Text to Columns
  4. Click “Delimited”
  5. For Delimiter Select “Space” (Tab is default)
  6. Click Next and Finish
  7. If you get an error message that Data is Already There Click OK
  8. 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

  1. Create blank column to the right of the relevant data column
  2. Type “=PROPER(“ into empty cell at top of column (under title row)
  3. Select first data item in relevant column to place cell address into formula
  4. Press OK.
  5. Click handle in lower right of new cell to copy formula to entire column
  6. Create a new blank column to the right of the column with the formulas
  7. Copy the column with the formulas
  8. Use PASTE SPECIAL to paste as Values
  9. Delete the original column and the formula column
  10. 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

  1. Create an empty column to the right of the Apartment Colum and name the column “Mailing Street”
  2.  Type =Concatenate(B3,“ “,C3) in the blank field (e.g. D3):

 

B3 C3 D3
3 1 Music Row #3 =CONCATENATE(B3,” “,C3)

 

  1. Copy the formula to all cells that have apartments
  2. Copy the column with the formulas
  3. Use PASTE SPECIAL to paste as Values
  4. 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

  1. Select the Mailing Zip/Postal Code column
  2. Click Format Cells.
  3. Under Category Click “Custom”
  4. Under Type delete the word “General” and type “00000” (five zeros)
  5. 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.

Allan Huntley

Author Allan Huntley

More posts by Allan Huntley