Issue with scientific notation (on SKU, UPC, EAN etc.) in CSV/XLS

When you are importing products to GeekSeller our system may show you a message that your UPC or EAN code was converted to a scientific notation (eg 8.4665E+12).


This means when you converted the file into a CSV file, your software converted to a shorter version.

Solution 1: In many cases, it is enough to make your columns wider before you save the file.


 

Solution 2: If the problem persists even after implementing Solution 1 try the following:

  • Open as an excel sheet (or convert if you can’t open as one)
  • highlight the (barcode/scientific notation) column
  • Go into Data / text to columns
  • Page 1: Check ‘Delimited’ / Next
  • Page 2: Check ‘ Tab’ and change ‘Text Qualifier’ to ” / Next
  • Page 3: Check ‘Text’ rather than ‘general’
  • Finish

This should convert them all to display as the long number. You can then save it as a CSV file and the numbers won’t be converted/formatted into scientific numbers.

Or similarly select the column -> Home tab -> Number subtab -> change Drop down menu “General” to “Text”

Source.

CSV vs. XLS

When uploading products which have numbers that have leading zeros, or you have a lot of issues with scientific notation, try to use XLS which in a more strict way preserves the values. Why not using XLS always? CSV is a very light format, when you upload a large file with 1000s of SKUs a difference between a size of CSV and XLS file can be significant.

For OpenOffice users, how to opening a csv file:

  • User character set Unicode UTF-8
  • Columns are separated by Comma
  • Select all fields (a small top left corner cell in the Fields section) and convert their type to Text

If you want to do some calculations on values which were previously converted to text:

Use formula =VALUE(A2)

When you edit a file and click on a filed, in the formula section you will notice an apostrophe sign in front of the number. This means it is seen by your software as text.

To convert it to value use =VALUE(A2)

In this case B2 cell is really a formula, not a number 0. To have a number 0 as a value. Copy it and use Paste Special…

Then make sure Formulas is not selected, but have selected text and numbers

The new pasted value, when selected will be shown just as value 0 in the formula section: