Why Spreadsheet CSV Exports Break When You Convert Them

A CSV file looks about as simple as a data format can get — split each line by commas, treat the first line as column headers, done — and that apparent simplicity is exactly what makes the format so easy to get wrong the moment real-world data shows up in it. The naive split-by-comma approach works perfectly on a toy example and breaks on the very first row where a genuine data value happens to contain a comma of its own, which is a routine occurrence rather than an edge case: an address like "123 Main St, Apt 4," a company name like "Smith, Jones & Co," or a product description that happens to include a list are all completely ordinary values that legitimately contain the exact character a naive parser is using as a field separator.

The CSV specification solves the embedded-comma problem by allowing a field to be wrapped in double quotes, inside of which commas, and even line breaks, are simply literal data rather than structure — a properly quoted field like `"Smith, Jones & Co"` is understood as one single value containing a comma, not two separate values split at that comma. A parser that naively splits on every comma without tracking whether it is currently inside a quoted field has no way to make that distinction, and silently splits that one company name into two spurious columns — usually without raising any error at all, which is precisely what makes this class of bug dangerous: the conversion appears to succeed, and the corruption is only discovered later, often well after the malformed data has already been used for something.

A spreadsheet cell containing a multi-line note or a multi-line address is entirely normal, and when that cell is exported to CSV, the specification handles it the same way it handles an embedded comma: the entire field is wrapped in quotes, and the line break inside it is genuinely part of the field's data, not a signal that a new row has started. A parser that assumes every line break in the file marks the start of a new record, without tracking whether it is currently inside an open quoted field, splits that one multi-line cell into what looks like several separate rows — silently corrupting the row structure of everything that follows it in the file, since every subsequent row is now shifted by however many spurious extra rows the broken cell introduced.

Every single value in a raw CSV file is stored as plain text, with no built-in way to indicate whether a given value is "really" a number, a boolean, or just a string that happens to look numeric — which becomes a genuine problem the moment a converter tries to be helpful by automatically inferring types. A ZIP code like "007" or an ID like "042" has a leading zero that is semantically meaningful — it is a fixed-width identifier, and "7" and "007" are different identifiers even though they represent the same number — but a converter that eagerly parses numeric-looking strings into actual numbers silently drops that leading zero, since the number 7 has no concept of how many zeros used to precede it. A phone number like "+1 555 0100" looks partially numeric but is not arithmetic data at all, and converting it into a number produces something genuinely meaningless. The safe, correct default is to leave ambiguous values as text unless a value is unambiguously and safely numeric, since guessing wrong here corrupts data silently rather than throwing a visible, catchable error.

Beyond the CSV format's own genuine edge cases, Microsoft Excel introduces additional friction that has nothing to do with the CSV specification itself: many European and other regional Excel configurations default to a semicolon rather than a comma as the field delimiter, since a comma is already used as the decimal separator in those locales, which means a CSV file exported from one of those Excel configurations will not parse correctly with a comma-delimited parser at all. Excel also has a well-known habit of auto-reformatting values it thinks it recognises when a CSV file is opened for viewing — turning a long numeric ID into scientific notation, for instance — which is a display-time misinterpretation on Excel's part rather than a flaw in the underlying CSV file, but it is routinely mistaken for actual data corruption in the export itself.