Why does my exported CSV data get converted to weird formats?

When exporting CSV data from your store, you may sometimes notice that large numbers are converted into scientific or exponential numbers in the cells of your spreadhseet program.

For example, when exporting the customer records CSV file, and a customer’s phone number is listed as 8008286650; upon opening up the file in Excel, you’ll have the number shown as 8.01E+09 or something similar.

A similar issue occurs when numerical data in your store contains zeroes at the start of the number, but upon opening the file in your spreadsheet program, the leading zeroes are removed.

An example of this can be seen if your products have part or MFGID numbers like 000986543219; but upon opening the exported file in the spreadsheet program, the numbers are shown as 986543219.

Unfortunately, saving the file in your spreadsheet program and re-uploading the CSV data to your store will then permanently change the record(s) to those displayed values.

To be clear, the reason these problems occur is not due to the way 3dcart creates your export data. Instead, this is due to the way Excel and other spreadsheet programs open a CSV file and display the data therein.

Basically, spreadsheet programs are designed to be used for calculation purposes, so they tend to apply mathematical formats to numbers when the CSV file is opened directly into the program. A CSV file is nothing more than a text file with its data values separated by commas (hence the name “comma separated values” or CSV).  These text files can be difficult to read as just straight text.  Fortunately, CSV files have the advantage of being read in a spreadsheet program, which allows the file to be read in organized columns and rows.

Opening the CSV file in a Spreadsheet

Unfortunately, when opening a CSV text file directly into a spreadsheet program like Excel, the data – if numerical – is converted by the program to be shown in a mathematical format. So numbers with leading zeroes are converted to whole numbers and numbers that are long integers such as phone numbers and UPC code numbers are converted by the spreadhseet application into exponential numbers like 8.01E+09.  It’s not that the CSV file exported from your store is formatted incorrectly, but rather that the spreadsheet program applied numerical formatting to those cells when opening it directly.

To work around this problem, the best thing is to not open the file directly into the spreadsheet program, but rather import the text file (CSV) data into the spreadsheet program while it is already open. During the import of text data, your spreadsheet program will give you the option to have the spreadsheet program format the data as “text” so that mathematical formatting is not used on your CSV file’s numbers.

The exact steps differ depending on the spreadsheet program being used but the basic process is as follows:

  1. Export your CSV file from the 3dcart Shopping Cart Software Online Store Manager

You will have a CSV file on your computer. Most CSV files will open up directly into your computer’s installed Spreadsheet program. However, do NOT double-click the file to open it. Instead, proceed with the following steps.

  1. Open up your spreadsheet program independently
  2. Create a new workbook or blank spreadsheet in the program.
  3. Look for your spreadsheet program’s import functions*

When importing the data you may be given the option to select the data type. If so, select comma separated. Furthermore, as part of the import you should be given the option to select the formatting that will be used for the imported data’s display.

  1. Complete your import.

Your workbook/spreadsheet file will now be formatted in text only and preserve your numerical data as it was generated by the store