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:05Z

How 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
RAW LINE 3,"New York, NY",450 line.split(",") → 4 FIELDS 3 "New York NY" 450 amount = ' NY"' csv.reader(...) → 3 FIELDS 3 New York, NY 450 amount = 450 Quote inside a field is doubled: "He said ""hi""" → He said "hi"
  1. One order whose city contains a comma. The writer correctly wrapped it in quotes.
  2. Naive split on commas gives 4 fields. Every column after the city shifts, and amount now holds text.
  3. A real CSV parser tracks quote state, so the comma inside quotes is data, not a separator.
  4. 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, never split(",").
  • Trusting type inference. Excel turns the ZIP code 02134 into the number 2134 and 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')

Learning Resources