CSV, short for comma-separated values, is a plain-text format for tabular data in which each line is one record and the fields of that record are separated by a delimiter, usually a comma. Among data formats, CSV is the lowest common denominator for exchanging tables: spreadsheets, databases, statistical tools and programming languages can all read and write it.
CSV predates any formal standard, and many dialects exist. The closest thing to a specification is RFC 4180, published in 2005, which describes common practice and registers the `text/csv` media type. Its simplicity is both its strength and its main weakness, because the format carries no data types, no schema and no declared encoding.
How the CSV format is structured
A CSV file consists of records separated by line breaks and fields separated by commas. The first line is optionally a header row with column names. RFC 4180 specifies CRLF line endings, but parsers in practice also accept a bare LF, which most Unix tools write.
Quoting handles the special characters. A field that contains a comma, a double quote or a line break must be enclosed in double quotes, and a double quote inside such a field is escaped by doubling it. The value She said "hi", then left is therefore written as "She said ""hi"", then left". Because quoted fields may contain line breaks, one record can span several physical lines, which breaks naive line-by-line processing.
The table summarizes the core rules and the usual deviations found in real files.
| Element | RFC 4180 rule | Common variation in practice |
|---|---|---|
| Field delimiter | Comma | Semicolon, tab or pipe |
| Record separator | CRLF | LF only |
| Quote character | Double quote | Single quote or none |
| Escaping quotes | Doubled double quote | Backslash escape |
| Header row | Optional | Present in most exports |
| Character encoding | Not mandated, US-ASCII in examples | UTF-8, UTF-8 with BOM, Windows-1252 |
Dialects, delimiters and encoding
The most frequent dialect difference comes from regional number formats. In locales that use the comma as the decimal separator, such as most of continental Europe, spreadsheet software writes semicolon-separated files under the same .csv extension. A file exported in Germany may therefore open as a single column on a computer set to US English.
Encoding is the second major source of errors. CSV has no field that declares its encoding, so the reader has to guess or be told. Many spreadsheet applications on Windows expect a UTF-8 byte order mark to recognize UTF-8, while many command-line tools treat that mark as part of the first column name. Agreeing on UTF-8 with an explicit dialect description avoids most problems.
Close relatives of CSV differ mainly in the delimiter, as shown below.
| Variant | Delimiter | Typical use | Main risk |
|---|---|---|---|
| CSV | Comma | General data exchange | Commas inside values need quoting |
| Semicolon CSV | Semicolon | European spreadsheet exports | Misread as one column elsewhere |
| TSV | Tab | Bioinformatics, database bulk loads | Tabs inside text fields |
| Pipe-delimited | Vertical bar | Legacy mainframe and banking feeds | No common quoting convention |
CSV compared with other data formats
CSV competes with richer formats that solve its weaknesses at the cost of simplicity. The choice depends on whether the data is flat or nested, how large it is and whether types must survive the exchange.
| Format | Structure | Types and schema | Best fit |
|---|---|---|---|
| CSV | Flat rows and columns | None, everything is text | Simple exchange, spreadsheets |
| JSON | Nested objects and arrays | Basic types, optional schema | APIs and hierarchical records |
| NDJSON | One JSON object per line | Basic types per record | Logs and streaming records |
| XML | Nested elements and attributes | Strong with XML Schema | Documents and formal interchange |
| Parquet | Columnar, compressed binary | Embedded typed schema | Analytics on large datasets |
| Apache Arrow | Columnar in-memory layout | Embedded typed schema | Zero-copy exchange between tools |
For analytics, the difference is substantial. A columnar format such as Parquet typically stores the same table in a fraction of the CSV size, often several times smaller, and lets query engines read only the needed columns. CSV must be parsed in full, row by row, and every value converted from text.
Working with CSV reliably
Reliable CSV handling depends on using a real parser and stating the dialect explicitly. Splitting lines on commas with string functions fails as soon as a value contains a quoted comma or a line break. Standard libraries such as Python’s csv module, the COPY command in PostgreSQL and LOAD DATA INFILE in MySQL implement quoting correctly.
Because CSV carries no types, readers infer them, and type inference is a frequent source of silent damage. The table lists typical symptoms with their causes and fixes.
| Symptom | Cause | Fix |
|---|---|---|
| Leading zeros disappear | Postal codes or IDs read as numbers | Declare those columns as text |
| Values turn into dates | Spreadsheet auto-conversion, such as gene names | Import with explicit column types |
| Garbled accented letters | Encoding read as Windows-1252 instead of UTF-8 | Specify UTF-8 on import |
| Columns shifted | Unquoted delimiter inside a value | Quote fields that contain delimiters |
| Long numbers truncated | Precision limit of 15 significant digits | Store identifiers as text |
A security issue deserves a separate warning. CSV injection, also called formula injection, occurs when a field starts with =, +, - or @ and a spreadsheet executes it as a formula on opening. Applications that export user-supplied text should neutralize such leading characters, for example by prefixing a single quote.
In plain words
A CSV file is a table written as plain text. Each line is one row, and commas mark where one cell ends and the next begins, much like a shopping list where each line reads “apples, 3, 1.20”.
Because it is just text, almost every program can open it. The catch is that the file does not say what the values mean: whether “01234” is a number or a code, or whether “3/4” is a date or a fraction. The receiving program has to guess, and sometimes it guesses wrong.
A real-world example
A logistics company sends a daily shipment list to a partner warehouse as a CSV file with about 40,000 rows and 12 columns, roughly 6 MB in size. After the partner moves to a new import system, shipments to certain regions start failing: postal codes such as 01067 arrive as 1067, and customer names with accented letters appear garbled.
The two teams agree on a written dialect: UTF-8 without a byte order mark, comma delimiter, double-quote quoting for every text field and a header row. The importer is configured to read postal codes and shipment IDs as text instead of inferring numbers. The failures stop, and the agreed dialect becomes part of the interface contract, so later changes on either side are tested against it before deployment.