CSV
CSV (comma-separated values) is a plain-text format for tables: one record per line, fields separated by a delimiter (usually a comma), and an optional header row with column names. Every spreadsheet, database and programming language can read and write it, which is why it is still the most common way to hand data from one system to another.
order_id,city,amount,created_at
1,New York,250,2026-11-01T10:15:00Z
2,Toronto,120,2026-11-01T10:17:42Z
3,"New York, NY",450,2026-11-01T10:20:05ZHow it works
There is no single CSV standard, but RFC 4180 describes what most tools agree on:
- Records end with a line break (CRLF in the RFC, LF in practice).
- Fields are separated by commas.
- A field that contains a comma, a quote or a line break must be wrapped in double quotes.
- A double quote inside a quoted field is written twice:
"He said ""hi""".
Everything else is convention: the header row, the encoding, the delimiter, how NULL is written, and the date format. CSV stores only strings; every type is a guess made by the reader.
Why you never split on commas
$ sed -n 4p orders.csv | parse
Naive split vs a real CSV parser
- One order whose city contains a comma. The writer correctly wrapped it in quotes.
- Naive split on commas gives 4 fields. Every column after the city shifts, and amount now holds text.
- A real CSV parser tracks quote state, so the comma inside quotes is data, not a separator.
- The same parser also handles escaped quotes and line breaks inside quoted fields.
Strengths and weaknesses
| Strength | Weakness |
|---|---|
| Opens anywhere, readable by humans | No types: 007, 1e5, TRUE and dates are all guesses |
| Streams well, easy to append | No schema: a new column in the middle shifts every reader |
| Compresses well with gzip or zstd | Slow to parse; every query reads every byte |
| Universal import/export format | No standard for NULL, encoding, delimiter or nested data |
Common mistakes
- Splitting lines yourself. Use the language’s CSV module, DuckDB, Spark or
COPY, neversplit(","). - Trusting type inference. Excel turns the ZIP code
02134into the number2134and gene names into dates. Declare column types when loading. - Ignoring encoding. Ask for UTF-8. Files from Excel often come with a byte-order mark (BOM) or as Windows-1252, which garbles accented and non-Latin text.
- Using CSV as the storage layer. Fine for landing files. For anything queried repeatedly, convert to Parquet.
- Empty string vs NULL.
,,and,"",may mean different things to different tools. Agree on a NULL marker.
Load it safely
import duckdb
duckdb.sql("""
SELECT * FROM read_csv('orders.csv',
header = true, delim = ',', quote = '"',
columns = {'order_id': 'BIGINT', 'city': 'VARCHAR',
'amount': 'DECIMAL(12,2)', 'created_at': 'TIMESTAMPTZ'})
""").write_parquet('orders.parquet')