Exporting to CSV: Why are there missing digits on numbers?

You've downloaded your data to a CSV file.  You open it only to discover that the leading zeros on the zip codes are gone, or the trailing zeros after a decimal place are gone. Why?

Data & Insights exports all the digits to the CSV file, but not the datatypes.  So when the file is opened Microsoft Excel takes over and sets all the datatypes to "General". Anything that looks like a date is formatted as a date, and anything that looks like a number is formatted as a number. This doesn't seem to happen to everyone, depending on their installation of Excel, but here is a workaround if it happens to you.

To preserve all the digits in text-formatted numbers, you have to import the downloaded CSV file as raw data into a new Excel spreadsheet, set the column datatypes as needed, and then save the new file as an Excel workbook. Excel (XLSX) files will preserve these formats, CSV files won't.

Below is a more detailed walkthrough.

1) Download the CSV, but don't open it. (If you do open it, don't save it.)

2) Open a new blank Excel workbook.  In a sheet, select the DATA ribbon, and then choose "From Text". Browse to the unopened CSV file.  Hit the "Get Data" button to import it.  Wait a bit if it is a big file.



3) This opens the Import Wizard.  

Wizard Step 1 
Choose "Delimited"



Wizard Step 2 
Choose "Comma" as the delimiter. Check to see that the data is separated as expected.



Wizard Step 3
Select a column, then choose the type of data you want to import it as.  For numbers you want treated as text - zip codes, phone numbers, lat/long, ID numbers - anything you don't want to do calculations on - choose TEXT. You can also choose date or other formats for the appropriate columns. Any column unspecified will be imported as "General".


Hit Finish.

4) Check the values in the spreadsheet to make sure all the expected zeros are there, and check the column types to make sure they are text.

5) Save your file as an EXCEL file (XLSX). This will preserve the text formatting.  If you save it as a CSV, all your hard work will revert back to "General".

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request



Article is closed for comments.