When saving xls as csv, Excel is removing the barcode apostrophe which messes up the import

[Boops]

Hi, I'm trying to import products with barcodes preceded by apostrophe, but I noticed after importing products that the barcodes all landed in shopify as 1.95E+11. I worked back through it and can see Excel is removing the apostrophe when saving as csv. What am I doing wrong 🙂

Many thanks

[K_Br4]

It's good that you've noticed it.

I searched in Google and there are at least 30 sites with barcode":"1.95E+11" in the source.

Why don't you try exporting the products with a spreadsheet app (there are free trials) and see if it works through there.

[Ablestar_Daniel · Accepted solution]

Hi @Boops,

Excel's eagerness to convert long numbers into scientific notations (like 1.95E+11) is one of its most annoying 'features'. Even if you open up a CSV file and the numbers display correctly, Excel will usually convert the numbers to scientific notation when you save them.

The easiest way I've found to fix this is to explicitly convert the column to a number type before saving. To do this:

1. Select the barcode column in your spreadsheet
2. Go to Format -> Cells...
3. Under 'Category' choose Number and set the 'Decimal places' to 0
4. Click OK and save your file to a CSV

Now your CSV should have the full numbers in them and not the 1.95E+11 version of them. You can verify this by opening up the CSV file with a plain text editor, like Notepad, and checking the numbers.

I hope this helps, let me know if you have any other questions.

Best,

Daniel

[Boops]

Thanks, method works for me. Worth noting for anyone else following this in future - you need to remove any preceding apostrophes first so your numbers are in the E+11 format, either manually or text to columns or use the 'convert to number' option under the yellow triangle in the cell - then follow the instructions above.