A CSV is a text file. It holds the characters your system wrote into it, nothing more. A spreadsheet is not a viewer for it. It is a parser. When you double-click the CSV, Excel reads every value, decides what type each one is, and some of those decisions change the value. It happens before you touch a cell, and nothing on screen marks which cells were rewritten.
Four conversions change values, and Excel names all four
That is the useful part. Under File, Options, Data, Automatic Data Conversion on Windows, or Excel, Preferences, Edit, Automatic Data Conversion on Mac, sit four checkboxes you can turn off (Microsoft documents them here):
- Remove leading zeros and convert to number. Unit 0012 arrives as 12.
- Keep the first 15 digits of long numbers. Excel carries 15 significant digits, so a 16-digit account number has its last digit replaced with a zero.
- Convert digits surrounding the letter E into scientific notation. A unit labelled 2E4 becomes 20000.
- Convert date-like combinations of letters and numbers into a date. A period column reading 2024-09 becomes a day in September 2024.
The 15-digit ceiling is the one to sit with, because Microsoft is blunt about it: past the 15th digit, the digits are rounded down to zero, and reformatting the column afterwards does not bring them back. The number is not hidden. It is gone.
The fields this hits are the fields you match records on
Look at what is in that list. Unit identifiers. Bank transit and account numbers. Cheque numbers. Period labels in a rent roll. Fractional civic addresses like 12 1/2. These are not the columns you do arithmetic on, they are the columns you line two exports up by, which is why the damage shows as a failed match rather than a wrong total. Unit 0012 from your accounting export and unit 12 from your maintenance export are two different strings, and every tool downstream agrees they are different. It compounds the problem covered in unit numbers as a join key.
Opening the file is harmless. Saving it is not.
Reading does no damage. The grid is a copy. The problem starts when someone opens the export, corrects one typo and presses save, because Excel writes out what is in the grid, and the grid now says 12. The original characters get overwritten by a person who did one small, correct thing. No warning, no diff, no second copy. That is the failure mode of every workflow where a scheduled export gets opened, tidied by hand, and uploaded somewhere else.
Prevent the parse instead of cleaning up after it
Three moves, in order of how much they cover:
- Turn the four conversions off. The setting is per application, not per file, so do it once on every machine that touches exports, not only yours.
- Import rather than open. Data, then From Text/CSV, and set the identifier columns to Text before you load. Those four checkboxes do not govern a Power Query import, so the column type is where you hold the line there.
- Make your identifiers unparseable. If you control the export, a short letter prefix on unit and account codes puts them out of reach of every numeric and date rule.
What does not work is formatting the column as Text after the fact, or eyeballing the sheet. A dropped leading zero looks exactly like a unit that never had one.
Treat the spreadsheet as an editor, not the record
The habit is small: the file your system produced is the record, and the spreadsheet is one application you opened it in. When the two disagree, the file is right. Keep the original export somewhere nobody writes to, and a changed value stays a question you can answer.