|
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 Shift4Shop 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.
Tip
To verify that your exported data is in fact formatted correctly, try opening the CSV file into a text program like notepad. You will see the exported data exactly as it is formatted by the Shift4Shop software before the format is changed by your spreadsheet program.
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:
- Export your CSV file from the Shift4Shop Ecommerce 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.
- Open up your spreadsheet program independently
- Create a new workbook or blank spreadsheet in the program.
- Look for your spreadsheet program's import functions*
*Note
This is the part that will differ depending on the specific spreadsheet program you are using. Some versions of Microsoft Excel will have an "Import Wizard" located in the "Data" menu. Other versions will have the import functions located directly in the program's main screen. Please refer to your spreadsheet program's documentation for information on importing data into the spreadsheet.
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.
Tip
Before selecting the format type, be sure to select all of the columns in the file, then proceed:
- Select "text" for the format that the data will be displayed in.
- 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.
|