Stop Excel scientific notation from changing a long SKU

Why does a long SKU become scientific notation?

Microsoft’s Excel article on leading zeros and large numbers says Excel removes leading zeros and converts large numbers to scientific notation, such as 1.23E+15, so formulas can use them. The same article says Excel’s maximum precision is 15 significant digits. For a number with 16 or more digits, any digits past the fifteenth are rounded down to zero. A custom number format on that page is for codes with fewer than 16 digits. It does not bring back digits that were already replaced.

Microsoft also lists a separate automatic conversion: numerical data surrounding the letter E can be converted to scientific notation. A product code can be rewritten even when it was never meant as a count. Compare the sheet with the original text file before you treat the displayed value as the stored identifier.

How do you keep the digits that are still in the file?

1. Open a copy of the CSV in a plain text editor. If the full digit string is still there, do not save the file from the sheet that shows scientific notation.
2. When the identifier has to be opened in Excel, import the text file and set that column’s data type to Text before Excel stores it as a number. Microsoft’s article says to use Data, From Text/CSV, press Edit in the preview, select the column, then Home, Transform, Data Type, Text, and choose Replace Current.
3. Formatting cells as Text after the number is already stored does not change those stored numbers. Microsoft says the text format applies to numbers entered after the format is applied.
4. Upload the text-file copy in Golanto’s CSV validator. Golanto reads every cell as text. It does not convert a long digit string into a number.
5. Compare several identifiers with the source catalog. If the text file already contains scientific notation, or zeros where the catalog had other digits, go back to the source export.

What Golanto does with the characters you upload

Golanto’s CSV reader keeps cell text as text. It does not detect scientific notation, and it does not rebuild a SKU from a value such as 1.23E+15. When the uploaded characters match the catalog, continue in the template mapper and place that column on the identifier field in a template you supply.

Worked example

An 18-digit identifier that should stay literal text

SKU,Title
123456789012345678,Linen runner

SKU,Title
123456789012345678,Linen runner

An unchanged file is the correct outcome. Microsoft’s illustration of the scientific-notation display is 1.23E+15. That illustration is not a conversion of this synthetic SKU. If Excel has already rounded digits past the fifteenth down to zero, Golanto cannot invent the original identifier.

Official references

• Microsoft: preserving leading zeros and large numbers (opens in a new tab)

Schema review: golanto-csv-1. Examples are synthetic. Store-specific and unpublished requirements are outside these checks.

Common questions

Continue reading

• Preserve leading zeros in SKUs and barcodes
• Choose the header row in a marketplace template
• Marketplace template mapper
• CSV repair