When working with a CSV file in Excel, you may need to apply special formatting to the file for it to import seamlessly into Retail POS.

Opening your CSV file in Excel

If you're using a version of Excel that is newer than 2016, you must first enable the legacy Text Import Wizard:

1. Open a new workbook in Excel.
2. Click File > Options.
3. Click the Data tab.

4. In the Show legacy data import wizards section, check the From Text (Legacy) box.

   

5. Click OK.

You can now proceed with importing your CSV file into Excel.

1. Open a new workbook in Excel.

2. From the top header menu, select Data.

   

3. Click Get Data > Legacy Wizards.

4. Select From Text (Legacy).

   

5. Select your CSV file from your computer and click Import.

   

6. Select Delimited and click Next.

   

7. Under Delimeters, select Tab and Comma.

   

   If you're using the European version of Excel, select Tab and Other and enter a semicolon (;) in the Other field instead.

8. Click Next.
9. Select each of the columns listed below one at a time and click Text under Column data format. To confirm the Text data format was applied, Text will appear above the column headers. This step prevents the series of numbers in the columns from being converted to scientific format (for example: 21+E):
   • UPC
   • EAN
   • Custom SKU
   • Manufacturer SKU
   • System ID (if updating existing items)
   • Any other columns that contain a series of numbers
10. Click Finish.

11. Click OK.

    

Now that you've successfully opened your CSV file in Excel, save it as an XLSX.

Saving your CSV file as XLSX in Excel

1. Click File > Save As.

   

2. Give your file a meaningful file name, including the date, for your records.
3. From the File Format, select Excel Workbook (.xlsx).
4. Click Save.

You can now update your XLSX file and proceed with importing.

Formatting a CSV file in Excel

1. Open the CSV file in Excel.

2. Select the first column.

   

3. Click to convert Text to Columns.

   

4. Select Delimited.

   

5. Click Next.

6. Select Comma or Other and type a comma character.

   

7. Click Finish.

Fixing truncated numbers or scientific notation in Excel

Truncated numbers can occur when using Excel to edit CSV files if your organization uses a SKU format with long digit numbers or numbers with a leading 0.

When you export your Retail POS products to a CSV and open the file in Excel, it may treat the SKU column as a scientific number field and remove the leading zero and truncate the SKU. When you re-import the file, Retail POS may treat this as a new SKU code and add a duplicate or incorrect product.

To manage this, you can either open the file in Google Sheets, or follow the steps below to fix truncated SKU codes in Excel:

1. Export your product list as a CSV and open the file in Excel.
2. Select the SKU column you need to fix.

3. Click Format > Format Cells.

   

4. In the Number section, navigate to Category: and from the list select Number.

5. Set the Decimal places to 0.

   

6. Click OK.

The SKU code in Excel should now be in the correct format.

What's next?