Troubleshoot CSV Exports

The most common issue with exported CSV files relates to how spreadsheet programs such as Excel, Numbers, or Google Sheets 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.

  • How to import a CSV file and properly format the data in Google Sheets.

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 be unable 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).

Working in Excel

Why does Excel format data incorrectly?

The Issue

CSV files are an efficient way to store large amounts of data, but the data may not format correctly when opened into a spreadsheet application such as Excel.

When you open a CSV file, Excel will try to format the data in the file based on the content in the cells but, it isn't always successful. This can result in:

  • 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. Importing allows you to define how the data is formatted.

Import a CSV file into Excel

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.

  1. Download and save the CSV file from ShipStation. Do not open the file.

  2. Open Excel, and start a new, blank worksheet.

    Excel Blank Workbook option selected with red arrow pointing to it.
  3. Click the Data tab.

    Excel interface with Data tab highlighted by red square
  4. Select the From Text option.

    Excel interface with From Text icon highlighted by red square
  5. Select the desired CSV file.

    CSV file selected in Windows Excel with arrow pointing to 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.

  6. Make sure Delimited - Characters such as commas or tabs separate each field is selected, then click Next.

    Excel Text Import Wizard pop-up with Delimited option selected for original data file type.
  7. Check both the Tab and Comma boxes.

    Check the data preview to make sure your data has been separated correctly.

    Excel Text Import Wizard pop-up with "Tab" selected and "Comma" unselected for delimiters.
  8. Click Next.

  9. 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.

    Excel Text Import Wizard pop-up with Text option selected for column data format. Red arrow points to highlighted Text column
  10. 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.

Import a CSV File to Google Sheets

As with Excel, importing a CSV file into Google Sheets will prevent the data contained in the CSV file from being formatted improperly. This section covers importing a CSV file and, if needed, how to format data in Sheets.

Import a File

  1. Open a blank Google Sheet.

  2. Go to File > Import.

    In Google Sheets, go to File > Import
  3. Select Upload.

    Upload is selected in Sheets.
  4. Drag and drop the CSV file you wish to upload into Sheets or select Browse to select the file from your computer.

    The file will be uploaded automatically once selected.

  5. Verify that the Separator Type is set to Comma and that the box for Convert text to numbers, dates, and formulas is unchecked. Then, click Import data.

    SHEETS_ImportFile_MRK.png

The data contained in the CSV file will be imported into the spreadsheet and should be formatted correctly.

Format Data in Sheets

If needed, data can be formatted in Google Sheets. Here's how:

  1. Select the cells that contain the data you wish to format.

    EXCEL_SEL_TrackingNumbers_MRK.png
  2. Go to Format > Number and select the correct formatting for the cells you have selected.

    In Sheets, go to Format > Number and select the correct format.

Formatting Tracking Numbers

To correctly format tracking numbers that are displayed incorrectly as scientific notation:

  1. Go to Format > Number > Custom Number Format.

  2. Select the first option in the list. The sample is displayed as 1235.