CRM Data Imports, CSV, and Leading Zeros

If you import data into CRM from CSV files via the CRM Import Untility or a tool like Scribe Insight, one frequent headache is columns with leading zeros.  This most frequently comes up in postal codes or phone numbers that don’t have any formatting characters, just numbers.

When you have an Excel spreadsheet and save as text or .csv format, the leading zeros are preserved.  So if the value in your spreadsheet is 09352, when you export to CSV, it will still be 09352, and if you import to CRM, the leading zero will be there.

If you reopen the csv file in Excel, it interprets the data to determine what format the column should be—general, numeric,etc.  So when it sees the 5 digit postal code with leading zero, it interprets it as a number, not displaying the leading zero.  It will display the leading zero if you have the full postal code with a hyphen and four trailing characters, like 09352-1111, it will display the leading zero, because the presence of the hyphen tells it that it is a text value, not a numeric value. 

The problems arise if you edit the csv file in Excel and make changes.  If you reopen the csv file and save it, the leading zeros will be stripped out of the csv file, and your import will create postal codes without the leading zero.

To avoid leading zero import issues, I recommend the following best practices:

1.  Make all edits in Excel format, then save to CSV.

2.  If you need to edit the csv file, do so in a text editor, such as notepad.

3.  If you still have issues, edit the trouble rows in notepad and place double quotes around the values with leading zeros.

 

Technorati Tags: CRM,Data Import,Scribe Insight,Microsoft CRM

Comments

[AdamV]

The problem with Excel is on opening CSV files, not saving them. It makes assumptions when you open CSV files, and treats data that looks like numbers as numbers, dropping leading zeroes as it goes along. Another related problem is if you have fields with long numbers in them (such as serial numbers or product codes) which may get truncated as Excel only supports 15 significant digits (strictly "truncated" is the wrong word because they are padded to the right length/order of magnitude, but you lose the detail at the end, which is the point).
To get round this, you need to force Excel to let YOU choose what data type is in which column. The easiest way I have found to do this is change the file extension to .txt, open the file with a file>open and you get the data import wizard. Explicitly declare any possible problem fields as TEXT (or just do this for all fields if that works for you).
Another option I have seen discussed is to add a dummy row of data to your CSV where every field has a text entry such as "DELETEME" - Excel won't treat your columns as numeric if even a single entry is text. This is more annoying (for me anyway) because you need to edit it in and out in a text editor.

[Joel Lindstrom]

True, Adam. The zeros get stripped out when Excel opens the file. My point was that the problem doesn't become permanent unless you save the file. If you just open a csv to view in Excel and don't save, the zeros will still be in the csv.

Good suggestion on saving as text.