The CSV file format, explained
CSV looks like the simplest format there is, until a comma turns up inside a name. These are the rules that decide how every cell is written and read, with a lab where you can watch them work.
The four rules that matter
- One record per line. Lines end with CRLF according to RFC 4180, but every modern parser accepts a plain LF too.
- Fields are split by a delimiter. Usually a comma. Semicolons are common where the comma is the decimal separator (much of Europe and Latin America), and tabs give you TSV.
- Fields with special characters are quoted. If a field contains the delimiter, a double quote or a line break, it is wrapped in double quotes.
- Quotes inside quotes are doubled.
Katherine "Kay" Johnsonis written as"Katherine ""Kay"" Johnson".
That’s all of RFC 4180. Most CSV bugs come from something that ignores rule 3 or 4, like splitting on commas with a regular expression or line.split(','), which breaks the moment a value contains a comma or a line break.
What CSV does not have
- Types. Every value is text.
00742,3-4andTRUEare only guesses until something interprets them, which is where Excel’s famous gene-name-to-date conversions come from (Ziemann et al., Genome Biology, 2016). - An encoding declaration. Use UTF-8. Add a BOM if the file has to open cleanly in Excel on Windows.
- Nesting. Trees must be flattened first. See the explainer on the JSON to CSV page.
Loading the interactive CSV quoting lab…
Stepping through a parser one character at a time is the quickest way to make the rules stick. If you like learning this way, ahaboo has narrated interactive explainers on how everyday things work, from the seasons to compound interest.
Delimiter cheat sheet
| Delimiter | Common name | Where you’ll see it |
|---|---|---|
, | CSV | Default in US/UK locales, most APIs and databases |
; | CSV (European) | Excel exports in locales that use a decimal comma |
| Tab | TSV | Copy/paste from spreadsheets, bioinformatics, mysql -B output |
| | Pipe-delimited | Legacy mainframe and healthcare (HL7-adjacent) exports |
Work with CSV files
Open and clean a file in the CSV viewer, or convert it: CSV to Excel, CSV to JSON, TSV to CSV, CSV to SQL.
Questions people ask
What is a CSV file?
A plain-text file that stores a table: one record per line, with fields separated by commas (or another delimiter). The format is described in RFC 4180 (2005), although many real-world CSV files differ from it slightly.
When does a CSV field need quotes?
Only when it contains the delimiter, a double quote or a line break. Quoting other fields is allowed but optional. A double quote inside a quoted field is written as two double quotes.
Why does Excel open my CSV in one column?
Excel picks the delimiter from your operating system’s list-separator setting. In many European locales that is a semicolon, so a comma-separated file lands in one column. Use Data → From Text/CSV, or convert the file to .xlsx first.
What encoding should a CSV use?
UTF-8. For Excel on Windows to recognise it, the file may need a byte-order mark (BOM) at the start; without one, Excel may assume the legacy ANSI code page and garble accented characters.
Is a CSV header row required?
No. RFC 4180 makes it optional and signals it only through the MIME type parameter header=present. In practice most tools expect one, which is why TableForge treats the first row as the header unless you untick the option.