Excel to CSV Keeps Mangling Your Data: Why It Happens and How to Stop It

In 2020 a committee of geneticists gave up arguing with Microsoft and renamed 27 human genes. SEPT1 became SEPTIN1. MARCH1 became MARCHF1. The gene called "deleted in esophageal cancer 1", DEC1, became DELEC1. None of this was biology. Excel kept reading those symbols as dates, and after a survey of 18 journals found errors in 704 of 3,597 papers carrying supplementary spreadsheets, renaming the genes turned out to be easier than changing the software.

Your data is less famous than the human genome and just as easy to wreck.

Saving a sheet as CSV feels like the safest thing you can do to a file. Plain text, nothing proprietary, opens anywhere. But the save isn't a copy. It's a translation, and Excel makes a run of decisions on your behalf about numbers, dates, characters and separators. Most stay invisible until somebody downstream mentions that all the ZIP codes are four digits long.

Here's what actually changes, why, and the checks that catch it while you can still fix it.

What an Excel to CSV Save Actually Writes to Disk

Microsoft's documentation is blunt about the scope: the CSV format saves only the text and values as they are displayed in the cells of the active worksheet. Active worksheet. Not the workbook, the one tab you happen to be looking at. Twelve tabs in, one tab out, no warning that the other eleven stayed behind.

Everything that isn't a cell value goes too. Charts, images, conditional formatting, data validation rules, merged cells, rotated text, hyperlinks. Formulas are replaced by whatever they currently evaluate to.

The phrase to sit with is as they are displayed. CSV records what the cell shows, not what it holds. A cell storing 3.14159265 that you formatted to two decimals lands in the file as 3.14. The precision was genuinely there. The export threw it out because the display said otherwise.

ZIP Codes Lose Their Leading Zeros

Type 00123 and Excel stores 123. Microsoft documents this as intended: leading zeros carry no meaning on a number, so they're dropped.

The part people get wrong is when it happens. Most blame the export. The export is usually innocent. The damage happened when someone opened the CSV: Excel read the text 07030, decided it was the number 7030, and the save later wrote back exactly what was sitting in the cell. Round-trip a file through Excel twice and the second save can't recover what the first open destroyed.

So formatting the column as Text afterwards fixes nothing. Microsoft is explicit that number formats only apply to values entered after the format is set. 7030 formatted as text is still 7030. The zeros aren't hiding, they're gone.

What works, in order of how much I'd trust it:

1. Don't let Excel parse it. Data > Get Data > From File > From Text/CSV, then set the column type to Text in the preview before loading. This is the only method that protects you on import.
2. Format the column as Text first, then paste or type. Fine for building a file from scratch.
3. Apostrophe prefix — typing '07030 marks the cell as text. Workable for a handful of cells, miserable for four thousand.
4. =TEXT(A1,"00000") in a helper column, if the true values are recoverable. For ZIP codes they often are. For account numbers they aren't.

The 15-Digit Wall

Excel keeps 15 significant digits. Not 15 digits after the decimal, 15 in total, anywhere in the number. This follows the IEEE 754 floating point standard rather than being an Excel quirk, and Microsoft applies it to numbers typed in and to numbers read from CSV and TXT files alike.

Past the fifteenth digit, everything is rounded to zero. A 16-digit card number ending in 1 comes back ending in 0. Nothing warns you. The number still looks like a card number, it's simply no longer that card.

Longer identifiers get a second insult: Excel displays them in scientific notation, and since CSV writes what's displayed, 1.23457E+18 is what lands in the file.

Long identifiers belong in text columns from the moment they enter the building. There is no repair for a truncated account number.

Dates Are the Worst Offender

The gene story is funny until it's your product catalogue. Anything that resembles a date gets converted on sight: 1-5, 3/4, MAR1, SEPT1, 2-10. Excel is guessing, it's confident, and it doesn't ask.

Real dates carry a quieter problem. 03/04/2026 is the 3rd of April in London and the 4th of March in Chicago, and a CSV holds no locale information to settle it. Whoever opens the file next applies their own regional settings to your ambiguous string.

Write dates as YYYY-MM-DD. ISO 8601 sorts correctly as plain text, can't be misread by region, and survives nearly every parser. If a column holds codes rather than dates, format it as Text before the data arrives — that's the only thing that stops the autocorrect.

Your Excel to CSV Export Uses Semicolons? That's Windows, Not Excel

Open a CSV from a German or Brazilian colleague and every row sits in one column. Nothing is broken.

Excel doesn't hardcode the comma. It uses the List separator from your Windows regional settings. Where the comma is the decimal separator — much of Europe, much of South America — the list separator defaults to a semicolon, because a file full of 1,5 and 2,75 separated by commas would be unreadable. The file is still named .csv. It just isn't comma separated.

To change it: Control Panel > Clock, Language, and Region > Region > Additional settings > List separator. That changes what Excel writes for every file, so if you're producing one file for an American API and keeping the rest local, convert that single file instead of flipping the machine-wide setting.

Encoding, or Why Café Becomes Café

Two entries in the Save As dropdown look almost identical: "CSV (Comma delimited)" and "CSV UTF-8 (Comma delimited)". They are not the same file.

Plain CSV writes in your system codepage. Accented characters, currency symbols and anything non-Latin get mangled or replaced by question marks. CSV UTF-8 writes real UTF-8 with a byte order mark at the front, and that BOM is what lets Excel reopen the file correctly later. Microsoft says as much: a UTF-8 CSV opens normally if it was saved with a BOM, and if it wasn't, you need Data > Get Data > From File > From Text/CSV to tell Excel the encoding by hand.

There's a nastier version of this. Open a UTF-8 CSV that came from somewhere else, edit a cell containing an accent, save, and Excel can write the file back in ANSI with the accents already corrupted. The file went in valid and came out broken, and the only clue was in bytes nobody looked at.

Cells That Start With = Are a Security Problem

This one rarely comes up in spreadsheet advice and belongs in any conversation about exporting data other people supplied.

When a spreadsheet opens a CSV, any cell beginning with = is treated as a formula. OWASP tracks this as CSV injection, and the trigger characters are =, +, - and @, plus their full-width equivalents in some locales. A customer who types a formula into your "company name" field has written code that executes on the machine of whoever opens the export.

OWASP's mitigation is to prefix any cell starting with those characters with a tab character inside the quoted field. Their own guidance carries a warning worth repeating: Excel can strip quoting and escape characters when a file is saved and reopened, so an escaped formula may go live again on the next round trip. If you're exporting user-supplied text, sanitise it as it leaves your system rather than trusting the spreadsheet.

An Excel to CSV Checklist That Takes 30 Seconds

1. Confirm which sheet is active. That's the only one going.
2. Widen every column. Anything showing ##### or E+ is about to be exported wrong.
3. Check ID and code columns for lost leading zeros and for anything over 15 digits.
4. Set date columns to YYYY-MM-DD.
5. Pick CSV UTF-8 if there's a single accent, symbol or non-Latin character in the file.
6. Know whether your machine writes commas or semicolons, and whether the recipient expects the same.

Check the File Before You Send It

The export is not the last step. Open the result in something that isn't Excel, because Excel will re-parse it and show you a cleaned-up version of the problem you're trying to find.

A text editor answers most questions in ten seconds: comma or semicolon, quoted fields or bare, and whether the first line starts with a stray character where the BOM sits. If you want to see it as a grid without a spreadsheet re-typing everything, you can open the CSV without Excel in the browser — it reads the file client side, works out whether the separator is a comma, semicolon, tab or pipe by counting them in the header row, and shows you the columns as they genuinely parsed. Two things to know about it: it decides the delimiter from the header line alone, and it trims leading and trailing spaces off each field, so it's a check on structure rather than a byte-exact view.

What you're looking for is simple. Do the column counts match the header. Are the IDs the length they should be. Did the accents survive.

If you own the data that feeds the spreadsheet, the better fix is upstream: keep identifiers as text from the start, keep dates in ISO format, and stop treating Excel as a storage format for things that only look like numbers. A ZIP code isn't a number. Neither is a phone number, an account reference or a barcode. Each one is a string that happens to be spelled with digits, and every tool that forgets that will round it.