Why does some data in CSV exports get converted to strange formats?

When exporting comma separated value (CSV) data, you may sometimes notice that some data is converted into scientific or exponential numbers.

For example, an exported a phone number may appear as 7.04E+09 or something similar.

A similar issue occurs when raw data in your system contains leading zeros, with these being removed when you open the CSV file.

For example, a cost centre in the format 00012345 will end up being displayed as 12345.

Opening your CSV file in a Spreadsheet

Unfortunately, if you open a CSV file using a spreadsheet program, such as Excel, numerical data may be converted by the program as above. Also, if you then save the CSV file again, the data will be permanently converted to the displayed values, rather than what you actually want.

For clarity, the reason these problems occur is not due to the way Advorto exports your data. This is actually a function of Excel and other spreadsheet programs and how they open a CSV file and display the data.

The good news is that provided you haven't saved the CSV file after Excel already converted the data, the data will be actually in the correct format in the original CSV file (you can see this if you open the CSV file in Notepad for example).

So, if you need to open the CSV file in a spreadsheet, you need to import the data instead. See below for how to do this in Excel.

Importing your CSV file to a Spreadsheet (Excel 2013)

  1. Open a blank workbook
  2. In the Data menu, select From Text
  3. Select the CSV file and click Import
  4. Click Next and check Comma as the only delimiter
  5. Click Next again and in turn, select each column with a phone number or with data with preceeding zeros and change the Column data format to Text
  6. Click Finish and say OK to putting the data in $A$1 in your new workbook

Other spreadsheet programs should have similar import wizards.

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


Powered by Zendesk