CSV, TSV, and the Quiet Ways Spreadsheet Data Gets Corrupted
A CSV that exported clean and came back with dates where an ID column used to be, or leading zeros quietly stripped from a code, feels like something broke, and it's natural to assume a mistake was made along the way. Often nothing was. CSV looks like the simplest file format there is. Commas and line breaks. It's simple enough that it went two decades without a real standard, and the gaps that left are still corrupting data today.
There was no real standard until 2005
"Comma-separated values" had been in informal use since the early days of computing, with every application implementing its own slightly different rules for the hard cases. What happens when a field itself contains a comma, or a line break, or a quotation mark. And no shared specification to settle disagreements. RFC 4180, published in 2005, was the first attempt to formally document common practice: fields containing commas or line breaks should be wrapped in double quotes, and a literal quote inside a quoted field is represented by doubling it. Plenty of tools still don't fully follow it, which is why a CSV exported from one program can silently misparse in another.
Leading zeros disappear for a related but separate reason. Spreadsheet programs guess a column's data type from its contents, so a value like 00423 looks like a number worth reinterpreting, and the zeros vanish the moment the file is opened, not because the CSV itself lost anything. The underlying text file is usually still correct; it's the next program that opens it and starts guessing that does the damage.
The delimiter isn't even always a comma
In locales where the comma is the decimal separator. Most of continental Europe, for one. Spreadsheet software typically uses a semicolon as the CSV delimiter instead, specifically to avoid colliding with commas inside numbers. That means a CSV file can be entirely correct and still fail to import properly if it's opened on a system with different regional settings than the one that exported it, because the two disagree about which character separates columns in the first place.
Encoding mismatches turn correct characters into garbage
A CSV file doesn't carry explicit information about its own character encoding unless the exporting tool adds a byte-order mark. A file saved as UTF-8 but opened by a program assuming Windows-1252 or Latin-1 will render accented and special characters as garbled multi-character sequences. A well-known failure mode called mojibake. Even though every byte in the file is completely intact. The data isn't actually corrupted; the two ends just disagree about how to translate bytes into characters.
The most-cited real-world case, and what to actually do about it
Anyone who's watched a plain code turn into a date without being asked has already run into a version of this exact bug, usually with lower stakes. The clearest documented example of table-format corruption isn't hypothetical. Microsoft Excel autocorrects any cell that looks like a date, and a number of standard human gene symbols, SEPT1, MARCH1, and others. Matched that pattern and were silently rewritten as calendar dates on open, with no warning. The problem was widespread enough in published genomics datasets that the HUGO Gene Nomenclature Committee, the body responsible for official human gene names, renamed around 27 genes in 2020 specifically to stop Excel from mangling them. An unusual case of a data format's quirks forcing a change to actual scientific nomenclature.
If the goal is opening a CSV without it getting silently rewritten, import it explicitly through a text or data import flow instead of double-clicking it open, so nothing gets auto-guessed into a date or a number. If the goal is producing a CSV that opens cleanly for someone else, quote any field that could be misread (an ID with leading zeros, a gene symbol, a plain code) and specify UTF-8 with a byte-order mark if their software needs the hint. If the goal is just figuring out why a file already looks wrong, check delimiter and encoding before assuming the data was lost: in most cases it wasn't, it's still sitting there correctly, just displayed by something that guessed wrong.
Convert between CSV, TSV, and other table formats without losing quoting, encoding, or delimiter details using the table converter.