Keeping Long Numbers Intact When Excel Opens a CSV

A number that fits in a database column does not always fit in a spreadsheet cell. A 19-digit order ID, a 64-bit snowflake ID, a long account number or a card-length identifier opens in Excel as 1.23457E+18, and when the file is saved the cell holds 1234567890123460000. The last four digits were replaced with zeros. Two different IDs can become the same value this way, which is the worst kind of damage, because the file still looks well formed and every row is present. The limit comes from how computers store numbers, not from a setting you can raise, and the answer is to stop letting those columns be numbers. The rest of the family of problems is in opening CSV files in Excel and Sheets without damage.

Where 15 digits comes from

Excel stores every number as a 64-bit double, the IEEE 754 binary format. A double has 53 bits of mantissa, which is about 15.95 decimal digits, and the conservative figure is 15 significant digits: any 15-digit decimal survives the round trip to a double and back, and beyond that the format cannot represent every value. Integers are exact only up to 2^53, which is 9,007,199,254,740,992, a 16-digit number. A 19-digit ID is far past it.

You can see what happens to one in any language that uses doubles:

v = "1234567890123456789"
print(repr(float(v)))
print(int(float(v)))
print("%.15g" % float(v))

1.2345678901234568e+18
1234567890123456768
1.23456789012346e+18

The nearest double is 1234567890123456768, so even Python's full-precision float has already changed the last three digits. Excel goes further and displays and stores only 15 significant digits, so the cell shows 1.23457E+18 and saving writes 1234567890123460000. Google Sheets and LibreOffice use doubles as well, and they cut at the same place. A spreadsheet cannot hold the number as a number, no matter what you change.

That is why the common advice to "format the cell as Number with zero decimals" only changes what is displayed. The stored value was rounded when the cell was created, and the format reveals it, nothing more.

Why it goes unnoticed

The damage is quiet for three reasons. The cell looks plausible: 1.23457E+18 reads as "a big number", and nobody counts digits. The rows are all there, with no error cell and no warning dialog. And a column of IDs is rarely summed, so a total that would reveal the problem does not exist. It is usually found when someone joins the exported file back to the source and a share of the keys fail to match, or when two customers share an ID and an invoice goes to the wrong account.

Checking for it is cheap if you keep the original. Count distinct values in both files:

SELECT count(*) AS n, count(DISTINCT id) AS distinct_ids, min(length(id)) AS shortest, max(length(id)) AS longest
FROM read_csv('orders.csv', all_varchar=true);

Run it on the file before and after the spreadsheet round trip. distinct_ids dropping, or longest changing from 19 to 19 with zero-filled tails, shows the rounding. The all_varchar=true option reads every column as text, which is what you want for checking IDs (DuckDB's CSV reading options). A second check is to look for IDs that end in a run of zeros that the source does not have: WHERE id LIKE '%0000' on a column where IDs are random digits is a strong signal.

Import the column as text

The import route lets you declare the column type before any conversion happens.

In Excel, use Data > From Text/CSV and choose Transform Data. In the Power Query editor, select the ID column, then Transform > Data Type > Text. Do it as an explicit step and delete the automatic Changed Type step if it has already guessed (importing a CSV into Excel with Power Query). Alternatively set Do not detect data types in the dialog and everything loads as text. On older versions, the Text Import Wizard has a third step where you click the column and select Text.

In Google Sheets, untick Convert text to numbers, dates, and formulas on import (importing a CSV into Google Sheets). In LibreOffice, set the column type to Text in the Text Import dialog.

If you paste from elsewhere, format the destination column as Text first. Pasting into a General column converts on arrival, and the rounding happens before you can react. For a single value, a leading apostrophe stores text. For a column of existing numbers that you need to convert after the fact, converting is too late for values over 15 digits, as the next section explains.

Keep text as text on the way out

The round trip has two ends. If you import correctly and then save as CSV, Excel writes text cells as they are, with no quotes unless needed, and the ID survives. The risk returns when the next person opens that CSV by double-clicking, which starts the conversion again. For files that will be opened by humans in Excel, one dependable option is to deliver the identifiers in a form that cannot be read as a number: a prefix such as ID-1234567890123456789, or a fixed grouping with hyphens. A separate column with the ID's last six digits helps people search without breaking anything.

When a downstream program reads the file, a JSON export is safer than a CSV, with the ID as a string, since JSON numbers have the same precision problem in JavaScript parsers (converting CSV to JSON with Python). And databases should store such identifiers as BIGINT where they fit (up to 9,223,372,036,854,775,807, 19 digits) or as text where they do not, never as a FLOAT.

When the digits are already gone

If the damaged file was saved, the digits that were lost are not in it. Zeros fill the tail of each value, and different IDs may now be identical. Repair is possible only if you have some other source of truth to rejoin against: a column kept elsewhere, a partial key such as the first 15 digits plus another field that distinguishes records, or the system that produced the file.

The practical sequence is to stop using the damaged file, go back to the source and export again, then import correctly. If the source is a database, export with a query that casts the ID to text, as in SELECT CAST(id AS varchar(20)), so that the file carries it as a string even when someone opens it carelessly. For a file you can no longer regenerate, treat the IDs as approximate prefixes and match on them together with a second field such as a date and an amount, then mark the matches as inferred.

Do not try to fix it by formatting the cell as Text and retyping the value. The retyped digits are the ones you remember, and for IDs that is a source of silent error.

A test that keeps it from coming back

Add a canary row to any file that has long identifiers: a 19-digit ID whose last digits are not zeros, such as 9223372036854775807. After every change to the process, push the canary through and read it back. If it comes out as 9223372036854780000, something in the path converts to a number. The check runs in a second and has caught more regressions than any amount of care. Pair it with the length check above, and keep a one-line note in the file's documentation: "id is text, up to 19 digits". The related checks for zeros, dates and encodings are in why Excel drops leading zeros.

For the precision rules themselves, the Microsoft specifications page lists the 15-digit precision and the largest numbers Excel handles, and the Python documentation on floating point explains the same format from the language side.