Someone in finance opens the export, scans it, saves it, and emails it back. Nothing looks wrong to them. Two days later the reconciliation fails on eleven rows, and every one of those supplier IDs is missing its leading zero.
Excel changes CSV data on open. Not when you edit a cell. On open, by default, on a file nobody typed a character into, and the changes get written back the moment somebody presses Ctrl+S out of habit.
A CSV is a text file with no type information in it. Every value in it is characters. Something has to decide whether 00742 is the text "00742" or the number 742, and when you double-click the file, that something is Excel's guesser.
The four ways Excel changes CSV data
Leading zeros come off. 00742 becomes 742. This eats zip codes, account numbers, SKUs, sort codes, and any identifier a system pads to a fixed width. The row still looks fine. It just no longer joins to anything.
Long numbers get truncated and reformatted. Excel stores 15 significant digits and replaces everything after the fifteenth with zeros. An 18-digit order reference loses its last three digits and then gets displayed as 8.80123E+17. People assume the E is a display setting and that widening the column will fix it. Widening the column shows you the zeros.
Anything shaped like a date becomes one. 3-4 turns into 3 April. Product code MAR1 turns into a date. Genomics has spent years on this: the naming committee eventually renamed 27 human genes because Excel kept converting the old symbols, and a scan of published papers found the error in about a fifth of those with supplementary gene lists. If that is happening to people whose whole job is careful data handling, it is happening to your operations team.
Encoding gets guessed too. A UTF-8 file without a byte-order mark, opened by double-click on Windows, is read in the system code page. Müller arrives as Müller. This one at least has the courtesy to look broken.
Nobody catches it, and the reason is boring
The file opens. The row count is right. The headers are intact. Those are the three things a person checks before deciding a file is fine, and all three survive.
Everything that changed changed into something plausible. 742 is a perfectly reasonable number. 8.80123E+17 reads like a formatting quirk rather than data loss. A date in a column of dates raises nothing at all.
And there is no before. A CSV keeps no record of what a value used to be, so the corruption has no signature to search for. It surfaces later as a join that returns fewer rows than it should, usually in a report somebody is presenting.
Turn the conversions off
Recent versions of Excel for Windows have a setting for this. File > Options > Data > Automatic Data Conversion, where you can switch off the leading-zero stripping, the 15-digit truncation, and the conversion of anything around an "E" into scientific notation. There is also a checkbox that warns you when a CSV you are opening is about to be converted.
Turn all four on for anyone who touches data files. It takes a minute.
Then be clear about what you have actually fixed. That setting lives on one installation of Excel. It protects the person who set it, on the machine they set it on. The file itself is exactly as vulnerable as it was, and it will get opened by someone else, on a laptop nobody configured, probably in a hurry.
Hand over files that Excel cannot damage
If a file leaves your system with identifiers in it, stop shipping it as a CSV.
- Send `.xlsx` instead. Column types are stored in the file, so there is nothing left to guess. This solves most of it and costs nothing.
- If it has to be CSV, tell people to import rather than open. Data > From Text/CSV, then set the identifier columns to Text before loading. Double-clicking skips that dialog entirely, which is why the problem is so common.
- Write CSVs with a UTF-8 byte-order mark when Windows is the destination. Three bytes, and the accented characters survive.
There is a deeper fix, which is to stop routing data through a desktop at all. When a file moves from one system to another by way of somebody's Downloads folder, that person is part of your infrastructure, and they are the part with no tests and no logging. A scheduled job that reads from one system and writes to the other never opens anything in Excel, never guesses at a type, and never has a bad Tuesday. That is most of what we do when we build data pipelines, and it is the same reasoning behind monitoring a pipeline properly rather than finding out from a client.
If the file has already been through Excel
Assume it is unrecoverable and go back to the source.
Padding IDs back out to five characters looks like a fix and is really a guess. It only holds if every identifier in the system is the same length, which is exactly the assumption that produced the leading zeros in the first place. Truncated digits are simply gone; there is nothing in the file to reconstruct them from.
Re-export from the system of record, and treat the re-export as the cheap part. The expensive part is finding out which downstream reports have been quietly wrong since the file first landed, and whether anyone made a decision on them.
Then find the step where the file passed through a desktop, because that step is scheduled to run again next month.
The version of this you can do tomorrow
Pick your two or three most-shared exports. Look at the columns that carry an identifier, and check whether the current output would survive being opened.
If it would not, change the export to .xlsx and tell the people receiving it what changed and why. That is a half-hour of work, and it removes a class of bug that is almost impossible to diagnose after the fact.
Sources: Microsoft Learn on floating-point precision, Microsoft Support on automatic data conversions, Ziemann et al., Genome Biology, 2016.
