Comma-Separated Values

IBM origins and the missing standard

In 1972, IBM's Fortran compiler implementations introduced list-directed input/output, a feature that used commas to separate data values in sequential records. This was not designed as a file format — it was a programming language feature for reading and writing data conveniently. But the resulting comma-delimited files proved so useful for data exchange that the pattern spread to CP/M systems in the late 1970s and to MS-DOS database exports (dBASE, Lotus 1-2-3) through the 1980s.

By the mid-1990s, CSV was everywhere — used by every spreadsheet, database export tool, and data pipeline — yet no standards body had ever defined it. The format existed purely as convention, with each implementation making its own decisions about delimiters, quoting, encoding, and line endings.

RFC 4180: thirty years too late

In October 2005, Yakov Shafranovich published RFC 4180, titled "Common Format and MIME Type for Comma-Separated Values (CSV) Files." The RFC is unusual in two respects. First, it is classified as "Informational" rather than "Standards Track" — it describes existing practice rather than prescribing a standard. Second, it arrived more than 30 years after CSV was already ubiquitous, by which point countless incompatible implementations existed.

RFC 4180 defines seven rules: records are separated by CRLF line breaks; an optional header row may appear first; each record contains the same number of fields; fields are separated by commas; fields containing commas, double quotes, or line breaks must be quoted; double quotes inside quoted fields are escaped by doubling them; and spaces are considered part of the field value.

These rules are sensible but incomplete. The RFC says nothing about character encoding, maximum field length, how to handle null values, or what happens when field counts differ between rows. It mentions only the comma as a delimiter. The result is a specification that covers the common case but leaves every edge case undefined.

Delimiter chaos

The name "Comma-Separated Values" is misleading. In countries where the comma serves as the decimal separator — Germany, France, Italy, the Netherlands, Brazil, and much of Europe — using a comma as the field delimiter would make numeric data ambiguous. Is "1,234" a number (one thousand two hundred thirty-four in English notation) or two fields ("1" and "234")?

Microsoft Excel resolves this by using the operating system's list separator setting, which is configured in Windows Regional Settings. In a French Windows installation, the list separator is a semicolon, so Excel's "Save as CSV" produces semicolon-delimited files with a .csv extension. A German user receiving this file in an English-locale system opens it in Excel and sees the entire row crammed into a single column, because English Excel expects commas.

Tab-separated values (TSV) sidestep the delimiter problem entirely by using the tab character, which never appears in normal text data. TSV files sometimes use the .tsv extension but are also saved as .csv, adding another layer of confusion. The pipe character (|) appears in enterprise data exports for similar reasons.

The encoding nightmare

CSV has no mechanism to declare its character encoding. There is no header, no metadata block, no byte-order mark requirement. A CSV file could be UTF-8, Windows-1252, ISO-8859-1, Shift-JIS, GB2312, or any other encoding, and no information within the file itself reveals which one.

RFC 4180 does not address encoding at all. The MIME type registration allows an optional charset parameter (text/csv; charset=utf-8), but this only applies when the file is served over HTTP — it provides no help for files on disk or attached to emails.

In practice, Excel on Western Windows systems produces Windows-1252 encoded CSV. Excel on Japanese systems may produce Shift-JIS. When saving as "CSV UTF-8 (Comma delimited)," Excel prepends a UTF-8 byte-order mark (EF BB BF) — the only reliable encoding signal, but one that some parsers treat as data rather than metadata, inserting a visible garbage character at the start of the first field.

Quoting rules and edge cases

RFC 4180's quoting rules handle the common cases: fields containing commas, double quotes, or line breaks must be enclosed in double quotes, and literal double quotes inside quoted fields are escaped by doubling them. A field containing the text He said "hello" becomes "He said ""hello""" in the CSV.

Edge cases abound. What about a field that contains only whitespace? RFC 4180 says spaces are significant — they are part of the field value, not ignorable padding. But many CSV producers trim whitespace silently, and many consumers strip it on import. What about an empty field? Two adjacent commas (,,) represent an empty field, distinct from a quoted empty string (""). Whether parsers distinguish between these varies by implementation.

The most dangerous edge case is the embedded newline. A quoted field can span multiple lines — this is a single field containing a line break. Parsers that process CSV line-by-line without quote-state tracking will split this into two records, corrupting every subsequent row in the file.

Excel destroys data: the number one CSV pain point

Microsoft Excel is the world's most common CSV viewer, and it silently corrupts data every time it opens a CSV file. Excel scans the first rows of each column, infers a data type, and applies that type to the entire column — with no user warning and no undo path.

Leading zeros are stripped: ZIP code 01234 becomes 1234, product code 007 becomes 7, phone number 0501234567 becomes 501234567. Large numbers convert to scientific notation: a 16-digit credit card reference 1234567890123456 becomes 1.23457E+15. Text strings that resemble dates are converted: "MAR1" becomes March 1, "1-2" becomes January 2, gene names like "SEPT7" become September 7 — a problem so widespread in genomics that the Human Gene Nomenclature Committee renamed 27 human genes in 2020 specifically to avoid Excel corruption.

Simply opening a CSV file in Excel and saving it — without making any manual changes — can irreversibly corrupt the data. The safe approach is to use Data > Get & Transform > From Text/CSV, which presents an import dialog where column types can be set to Text before loading.

W3C CSV on the Web

In 2015, the W3C published a set of recommendations collectively known as CSVW (CSV on the Web). These define a JSON-LD metadata file that accompanies a CSV file and declares column names, data types, encoding, delimiter, null values, and transformation rules. The metadata file enables machines to parse CSV unambiguously — solving every problem RFC 4180 left open.

Adoption has been limited. Government open data portals in the UK and parts of Europe use CSVW metadata, but the mainstream data science and business analytics ecosystems have not embraced it. Most CSV files continue to ship without metadata, relying on human knowledge and heuristic detection.

TSV: the tab-delimited sibling

Tab-Separated Values (TSV) use the tab character as the delimiter and have their own IANA registration: text/tab-separated-values (assigned in 1993, predating CSV's text/csv by 12 years). TSV avoids the comma-as-decimal-separator problem entirely and eliminates the need for quoting in most cases, since tabs rarely appear in data values.

TSV is the standard format for bioinformatics data exchange, linguistics corpora, and database bulk import/export operations. Despite its advantages, TSV never displaced CSV in the spreadsheet ecosystem, largely because Excel defaults to comma separation and the .csv extension is more widely recognized.

Why CSV persists

JSON, XML, Parquet, and Avro all provide richer structure, explicit typing, and unambiguous parsing. Yet CSV remains the most common data interchange format. The reasons are simplicity and universality: a CSV file can be created with echo, read by any text editor, imported by every database, and processed by every programming language's standard library. No special tooling, no schema definition, no compilation step. For quick data exchange between humans who can visually inspect the result, nothing beats a spreadsheet exported as CSV.