What is CSV?

CSV (Comma-Separated Values) is a plain-text format for tabular data: rows of records, each split into fields by a delimiter, usually a comma. It predates JSON by decades — spreadsheet programs have exported CSV since the 1970s — and remains the default hand-off format between spreadsheets, databases, and just about any tool that deals with rows and columns.

There is no single official CSV standard. RFC 4180 describes the common conventions most parsers follow, but it is informational, not enforced, which is exactly why CSV files from different sources sometimes disagree on quoting, line endings, or delimiters.

Anatomy of a CSV file

The first line is usually a header row naming each column. Every line after it is one record:

name,age,city
Alice,30,Paris
Bob,25,Lyon
Chloé,41,Marseille

Fields are separated by the delimiter, records by a line break. There is no type system: every value is text until something downstream decides otherwise. 30 is not a number in the CSV itself — it is the two characters 3 and 0, interpreted as a number only by whatever reads the file next.

Quoting and escaping

Plain fields need no quotes. A field only needs double quotes when it contains the delimiter itself, a double quote, or a line break — otherwise the parser cannot tell where the field ends:

name,address,age
Alice,"12 Rue de Paris, 3rd floor",30

Without the quotes, the comma inside the address would look like a third field and shift every column after it. To put a literal double quote inside a quoted field, double it:

quote,speaker
"She said ""hello"" and left.",Alice

That field decodes to She said "hello" and left. — RFC 4180's rule is simple but easy to get wrong when writing CSV by hand: forgetting to double an embedded quote produces a file that looks fine until a parser hits it and either throws an error or silently misaligns columns.

Delimiters: comma, semicolon, tab

The comma is the default, but not universal. In locales where the comma is the decimal separator (most of continental Europe: 3,14 instead of 3.14), Excel exports CSV with a semicolon delimiter instead, to avoid colliding with decimal numbers. A file that opens as one giant column in a US-locale spreadsheet is often just semicolon-delimited CSV from a European one.

TSV (Tab-Separated Values) sidesteps the whole problem: tabs rarely appear inside real data, so quoting is almost never needed. It is common in bioinformatics and command-line tooling, less common as a spreadsheet export default.

CSV vs JSON

Converting between the two means picking a shape: turning a JSON array of flat objects into CSV rows is straightforward, but nested objects or arrays inside a field either get flattened, dropped, or serialized as a JSON string inside a single CSV cell — there is no lossless general mapping between the two formats.

Common pitfalls

Excel rewrites your data on open

Opening a CSV in Excel is not a neutral preview — Excel applies its own type-guessing on the way in. A ZIP code like 07430 becomes 7430 (leading zero dropped), a 16-digit ID number gets displayed in scientific notation and loses precision beyond 15 significant digits, and a date-like string such as 03-04 silently turns into a date. None of this touches the underlying file until you save — but it is a frequent source of "the CSV looks wrong" reports that are actually Excel's rendering, not a bad export.

Encoding and the BOM

A CSV file is just bytes; nothing in it declares its own character encoding. If a file uses UTF-8 and contains accented characters (é, ü), Excel on Windows may misread them unless the file starts with a UTF-8 byte-order mark (BOM). Other tools expect the opposite and treat a BOM as three stray characters at the top of the first cell. There is no encoding setting that satisfies every consumer at once.

Ragged rows

Nothing enforces that every row has the same number of fields. A row with a missing trailing comma has one column fewer than the header; a row with an extra unescaped comma has one more. Strict parsers reject these files outright, lenient ones silently misalign columns from that row onward.