Why Excel Turns Your CSV IDs into 1.23E+18

You export an order report to CSV, send it to someone, and they tell you the IDs are wrong. You open the file in a text editor and the IDs are perfect. You open it in Excel and they read 1.23457E+18.

The file is fine. Excel applies type inference to every cell during import, guesses wrong on several common patterns, and — this is the part that hurts — if the file is then saved, the guess is written back and the original values are gone.

A CSV that triggers three problems at once

Here is a real export, exactly as the converter produces it:

order_id,postcode,sku,qty,placed
1234567890123456789,01234,SEP2,3,2024-03-11
1234567890123456790,00501,MAR1,1,2024-03-12

Every value there is correct. Double-click it in Excel and you get something like this instead:

Column	In the file	What Excel shows	Recoverable?
`order_id`	1234567890123456789	1.23457E+18	No — digits past the 15th are replaced with zeros.
`postcode`	01234	1234	No — the leading zero is gone.
`postcode`	00501	501	No.
`sku`	SEP2	Sep-02 or 02-Sep	No — and the rendering depends on locale.
`qty`	3	3	Fine.
`placed`	2024-03-11	A date, reformatted to machine locale	Value survives; the text form does not.

Why each one happens

Long numbers

Excel stores numbers as IEEE 754 doubles and displays at most 15 significant digits. A 19-digit order ID, a Twitter-style snowflake ID, a credit card number or an IMEI all exceed that. The display switches to scientific notation and the low-order digits are replaced by zeros — not hidden, replaced.

This is the same constraint that affects JSON itself, for the same reason. If your IDs are long enough to break Excel they are long enough to break any JavaScript consumer too; JSON and large numbers covers that side of it.

Leading zeros

A cell that looks like a number is converted to one, and numbers do not have leading zeros. Postcodes are the usual casualty — 00501 is a real ZIP code (Holtsville, NY) and becomes 501. Also affected: product codes, bank sort codes, phone numbers with a trunk prefix, and anything zero-padded for sorting.

Date coercion

Anything resembling a date becomes one. The famous case is genetics: SEP2 and MAR1 are gene symbols that Excel converts to dates, which corrupted a measurable fraction of published supplementary data — the HGNC eventually renamed the genes rather than keep fighting it. If Excel can beat an entire scientific field, it will beat your SKU column.

Three ways to stop it

1. Import, do not open

This is the only method that fixes the file for the person receiving it without changing the data. In Excel: Data → From Text/CSV, then in the preview set the problem columns to Text before loading. On older versions it is the Text Import Wizard, step 3. Nothing is coerced, and you also get to choose the encoding.

The catch is that it depends on the recipient doing it. If you cannot rely on that, use one of the next two.

2. Make the values obviously not numbers

If the consumer is a person with Excel rather than a program, the pragmatic move is to stop the column looking numeric at the source — prefix order IDs with a letter, or emit the ID as ORD-1234567890123456789. Ugly, and completely reliable.

The widespread ="1234567890123456789" trick does survive a double-click, but it is an Excel formula rather than CSV, so anything that is not Excel sees the formula text. Only reach for it if you are certain Excel is the only consumer.

3. Do not use CSV

If the recipient needs the data rather than a spreadsheet, send JSON and skip the whole category. CSV has no type system — every cell is text, and the reader guesses. That guessing is the entire problem.

Two more things that bite

Delimiters are locale-dependent. In locales where the comma is the decimal separator — much of continental Europe — Excel expects semicolons and will put every row into a single column. The JSON to CSV converter lets you switch the delimiter:

order_id;postcode;sku;qty;placed
1234567890123456789;01234;SEP2;3;2024-03-11

Encoding. On Windows, a UTF-8 file opened by double-click may be read as the system code page, mangling accents and emoji. Importing via Data → From Text lets you pick UTF-8 explicitly, which is another reason to prefer it.

Catch it before you send it

The failure mode here is that nobody notices. A quick check before exporting: run the source document through the analyzer, which reports fields whose type changes between records and fields that hold identifier-shaped values. Anything it flags as an identifier is a column to protect.

And the general rule, which removes most of this permanently: an identifier is a string, not a number. IDs, postcodes, SKUs, account numbers and version strings should be strings in your API, in your database and in your CSV. You never do arithmetic on them, and typing them as numbers buys nothing while risking exactly this.