The Data Trap - 5 Simple Rules to Make Your Life Easier

Successfully move your data from one program to another

Chances are you've moved data from one program to another before. Perhaps you exported data from your POS system and imported it into Excel to take a closer look. Then perhaps you uploaded that same data into your new financial system.  Or you downloaded customer data from your financial program and want to upload it into MailChimp or Constant Contact for email marketing. If you've ever tried to do this yourself and found yourself in a big mess, you're not alone. Here are a few little tricks to make sure you move nice, clean data the next time around.

One Item Per Column

Let's say you exported your customer data from your Point-of-Sales (POS) system to Excel, and now you want to import it into Greenrope (a terrific Intelligent CRM system). Maybe all the components of the physical address - street, city, state, and zip - ended up in one column. That just won't work! Modern databases operate on a rule that says "one type of information per column." An address, as we just pointed out, is four pieces of information (five if there's a second street option). A name is made up of three pieces of information (first, middle, last). Before you can push that data into your modern database application, you'll need to separate that information into separate columns. 

Yes, this is a bit tiresome, because it's going to be a manual process unless you have some good Excel data management skills. So some people will decide to just pick a column (most often Street or City) in the new database and push all the data into that one bucket. Which means that you won't be able to use the new database for important filters like "send this email campaign only to people in a specific zip code," or "show me all my Facebook respondents from a particular state."

One of the most important data processing rules is one type of information per field/column. You may be able to force a database to accept the wrong information into a field/column, but you won't be able to make it work properly afterward.

No Special Characters

What you see in most software programs is very different from what is happening behind the scenes, in the code. In particular, quotation marks and slashes (/ or \) are often used at the code level, and when they come into the program as part of your data they can cause weird errors to occur. Not always, but often enough to say just don't do it.

Another thing that happens behind the scenes is something called masking. Some programs show a phone number as (888)555-1212 and others will show it as 888-555-1212. Newer programs will create the mask (preferred format) for you, so all you need to enter is 8885551212 and the program will put in the dashes and parentheses automatically. Want to make an unsearchable mess of your phone numbers? Import numbers with a bunch of different characters, including parentheses, dashes, and slashes. If you find yourself staring at a spreadsheet filled with a wild array of phone number formats, use the spreadsheet's "find and replace" function to find each special character and replace it with a blank (nothing). 


Dates are always tricky to import and export. The best thing to do when importing dates into a new program is to look up the date format that the program uses (some programs will allow you to choose which format you prefer). Whatever format the new program uses, make sure the dates in your date column are in the same format before importing.

Email Addresses

Email became popular after many of the business software programs in use today were written. As a result, email fields were added after-the-fact and they were often just text fields, which means you could put anything you wanted in them. If you have one of those older programs, your email addresses probably include things like:

info@margaritaville (office), thomas@magarativille (home) (some old programs wouldn't allow the @ sign)

info@margaritaville (use only on weekends)

Modern databases are designed to allow you to launch email just by clicking on the address. If the email address isn't formatted properly and/or contains information in addition to the address, you won't be able to use the launch feature. Be sure that all email addresses contain an @ sign followed by a domain name, and that they contain no spaces. Also, remove all notes that are in the same column with the email address, as these will also get in the way of using automated email features.

Match Column Names

It can be very confusing to know which columns from your spreadsheet go into which columns (usually referred to as "fields") in your new program. A super easy way to figure this out is to do some manual data entry in the new program and then export the data. What you'll see in your export are the preferred Field Names at the top of each column. Once you know if your new program uses "FN" or "First Name," for instance, you can put the proper Column Headers in the file you are preparing. This will make importing the spreadsheet data a cinch.

Creating Your Own Spreadsheets

These guidelines are also important if you are creating a spreadsheet from scratch. Set up your columns so you have only one piece of data per bucket. Here's a good example of an address:

  • An address: Tiffany Jackson, Project Coordinator, SupportWerx, 1658 N. Milwauke Avenue, Suite 3703, Chicago, IL 60647. Phone: 877-239-8820 Fax: 312-637-9610
  • An address in a database:
FN  LN Title Company Street 1 Street 2 City State Zip Phone Fax
Tiffany  Jackson  Project Coordinator  SupportWerx  1658 N. Milwauke Ave. Suite 3703 Chicago IL 60647 8772398820 3126379610

Even if you think you will not use this data anywhere else, we recommend that you always construct your spreadsheets this way. So many companies start with a spreadsheet, build it over time, and then eventually want to port it into a new program. If you start with good clean data structure you'll save time and frustration down the road.