Using Excel to edit CSV files - truncated numbers/scientific notation


Latest update: - Nick Houldsworth

If your organisation uses a SKU format which is either long digit numbers, or numbers with a leading 0, such as in the image below

sku.PNG

When you export your Vend products to a CSV, and open this directly into Microsoft Excel, it treats the SKU column as a number  field and tries to remove the leading zero and 'truncate' the SKU, so that it looks like the image below.

bad.PNG

If this happens, when you then save the re-import the file, Vend may treat this as a new SKU code, and add a duplicate or incorrect product.

The easiest way to work with such SKU numbers is to edit CSV files Open Office  (a free alternative to MS Office), which does not truncate number fields.

But it is possible to work with Excel, without truncating SKU codes, following the steps below.

1) Export your products to a CSV file  and save it to your computer

2) Open Microsoft Excel

3) Select 'Data > [Import] From Text' and navigate to the saved product export CSV file

4) Microsoft will prompt you to select what type of data file it is. At Step 1 , select 'delimited', then click 'next'

delimited.PNG

5) At Step 2 select 'Comma' and then click next

comma.PNG

6) At Step 3 navigate to and highlight the SKU column, change the Column data format  to _ text _ and then click Finish. (You may wish to apply this to the 'handle' column if you use the same naming convention)

text.PNG

7) Check the SKU code in Excel, it should now be in the correct format

good.PNG

If working in Excel, after making any changes to the CSV file, we also recommend checking the file in notepad before reimporting to Vend, to ensure the SKU codes have been correctly preserved. You can do this by right-clicking the file and opening it with Notepad

notepad.PNG

Then searching for a SKU number at random, and checking that it matches the SKU number in Vend

raw.PNG

oo

Powered by Zendesk