Steps to take if MS Excel automatically reformats your CSV file tracking numbers, zip codes, order numbers, or product SKUs, causing a CSV import fail in ShipStation.
The most common issue with exported CSV files relates to how spreadsheet programs, such as Excel or Numbers, reformat the data in the CSV file.
This article covers the steps to take to prevent Microsoft Excel from automatically reformatting your CSV file tracking numbers, zip codes, order numbers, or product SKUs, which may cause a CSV import to fail in ShipStation.
Numbers on macOS
We do not recommend opening exported CSV files from ShipStation in Numbers on macOS. Opening an exported CSV file from ShipStation in Numbers may cause the file to not be able to successfully import back into ShipStation.
If this has occurred, re-export the CSV from ShipStation and open it in another program (like Excel or Google Sheets).
The Issue
CSV files are an efficient way to store a large amount of information but do not include the cell formatting information to help spreadsheet programs know how each cell should be interpreted.
When you open a CSV file in Excel or double-click a file to open it in Excel, Excel will automatically try to format the information in the file based on the content in the cells. For cells that contain only numerical data, this includes removing preceding zeros and converting large numbers into scientific notation. When dealing with shipping and order information from ShipStation, this can cause the following data issues:
-
Long tracking numbers, order numbers, or product SKUs changing into scientific notation.
-
Postal codes, order numbers, or product SKUs with preceding zeros having the zeros removed.
The Fix
Prevent the above issue by importing the CSV file, not opening the file, into Excel so that you have control over how the data is formatted.
The steps below describe how to import a CSV file into Excel to preserve numerical data as text.
Note
The screenshots in these steps show Excel 2013. If you have an earlier version, your screen and process may differ slightly. For Excel 2016 users, you will need to restore the text import wizard to complete these steps.
If you have trouble, you may need to contact Microsoft support for help troubleshooting your particular version of Excel.
-
Download and save the CSV file from ShipStation. Do not open the file.
-
Open Excel, and start a new, blank worksheet.
-
Click the Data tab.
-
Select the From Text option.
-
Select the desired CSV file.
This will open up the Import Wizard that will walk you through the import settings.
Excel 2016 users will need to restore the text import wizard before moving on if you haven't already.
-
Make sure Delimited - Characters such as commas or tabs separate each field is selected, then click Next.
-
Check both the Tab and Comma boxes.
Check the data preview to make sure your data has been separated correctly.
-
Click Next.
-
Locate any column which contains only numbers, then change the Column Data Format to Text.
In the screenshot below, we're using Tracking Number as an example.
Mac Scroll Settings
If you're using a Mac and cannot scroll to find the columns, you may need update the General preferences for your Mac so that the Show Scroll Bar option is set to Always.
-
Click Finish and your file will be imported with the data in the correct format.
Save the File
After successfully importing the data into Excel, save the file in a more advanced format (such as .xlsx) to preserve the cell formatting so you can open the file more easily in the future.
If you save the file in CSV format again, you will need to perform the above steps every time you need to review the file to ensure numerical data is preserved.