Import/Export CSV Files to Excel

To transfer a large amount of product, order, or customer information between Store and Excel, import and export data with the help of specially formatted spreadsheets.

Online Store uses CSV (comma-separated value) files to perform this kind of task. Excel reads CSV files by default. But in some cases when opening a CSV file in Excel, it is possible to see scrambled data that’s impossible to read.

Quick solution: when exporting data from the store, choose the same CSV-file delimiter that is set in Excel (usually it’s a comma).

Opening CSV Files in Excel

When data from the store is exported, it downloads as a .csv file on the computer. Use Microsoft Excel to view and edit CSV files.

To open a CSV file in Excel correctly, it is crucial that the delimiter (separator character) selected while exporting data from the store matches the delimiter set in Excel. The store supports three delimiters in CSV files: commas, semicolons, and a tabulation.Use any delimiter (comma, semicolon, or tab) when exporting from the store and manually select the same delimiter when opening a CSV file in Excel.

To open a CSV file in Excel:

  1. Open a new Excel document in Excel → New:

  1. Navigate to Data tab:

  1. Choose From Text:

  1. Locate and select the CSV file that should be edited and choose Get Data:

  1. From the newly-opened window choose Delimited, then click Next:

  1. Check the box next to the type of Delimiter – the same was used when the file was exported from the store (a semicolon, a comma, or a tab). If the right delimiter was chosen, on the preview that data is parsed into columns. Then click Next:

  1. Select General Data Format profile and click Finish:

  1. Choose where the data should be put — on the existing sheet or on a new one, then click OK:

That’s it! CSV will look similar to this after it is uploaded:

Note: The steps described in the instructions can slightly alter for different operating systems and their versions. For more details on how to open a CSV file in Excel on different versions of Windows refer to the article in Microsoft Office support helpdesk.

Exporting CSV Files From Excel

After having edited the CSV file in Excel, save the file in a CSV format and then import it into the store to update store data.

To save a CSV file in Excel:

  1. Open an Excel document.
  2. In Excel top menu go to File → Save as.
  3. Type the file name into the Save As field.
  4. Set File Format as Comma Separated Values (.csv).

  1. Click Save.

That’s it! A CSV file is saved in the selected directory on the computer.

Now import that data to store. Refer to Importing products and Importing customers to get step-by-step instructions.

When importing the CSV file back to Online Store, use the same delimiter in Online Store import settings that were set in Excel — by default, it’s a comma. If you are not sure which delimiter was used when exporting a CSV file from Excel, just go ahead with the import in Online Store with a comma as the most typical option. The store will prompt to select another delimiter if required.

Changing the Default Delimiter for Excel

When creating a CSV file Excel uses a default delimiter set at the operating system level that can be changed in device regional settings.

To change the default delimiter in Excel:

For Windows: The default delimiter for Windows is a comma. Change it to another delimiter character using Windows Region settings. Learn how to do it on different versions of Windows in the article by Microsoft Office support.

For macOS: 

In case you work with Excel on macOS, check and change delimiter settings in System Preferences Language and Region → Advanced → General → Number separators.

The configured delimiter will be applied not only tor Excel settings but to all apps and programs. Change the character back to the default by following the same procedure.

Note: Though it is possible to view and edit CSV files in Excel, try out more convenient tools suitable for such files — Notepad for Windows, TextEdit for Mac, Sublime Text, or spreadsheet editors — OpenOffice, LibreOffice, Google Sheets.