CSV

CSV#

Comma-Separated Values. The simplest tabular format that anyone can read, write, and exchange between systems that disagree on everything else. Predates the internet; survives to outlive most of its replacements.

CSV is also the format people most often almost parse correctly.

The Format#

The intent: rows separated by newlines, fields separated by commas. The reality is messier because field values may contain commas, newlines, and quotes; that is why “just split(',')” gets the wrong answer for any non-trivial input. RFC 4180 codified a common interpretation in 2005.

id,name,email
1,Ada,operator@example.com
2,Alan,alan@example.com
3,Grace,grace@example.com

The reality is messier because.

  • Field values may contain commas.

  • Field values may contain newlines.

  • Field values may contain quotes.

  • Different tools use different conventions.

RFC 4180 codified a common interpretation in 2005, but older / national / vendor variants still exist.

RFC 4180 Rules#

RFC 4180 is the closest thing to a standard CSV specification. The rules below cover separator, line ending, header, and (crucially) the quoting and escaping rules that distinguish a real CSV parser from split(','):

  • Fields separated by commas.

  • Records separated by CRLF (\r\n).

  • Each record has the same number of fields.

  • The first record may be a header.

  • Fields may be enclosed in double quotes.

  • If a field contains a comma, newline, or quote, it must be quoted.

  • Quotes inside a quoted field are escaped by doubling: "".

id,name,note
1,Ada,"Hello, World"
2,Alan,"Line 1
Line 2"
3,Grace,"She said ""hi"""

The Dialects#

CSV is really a family of related formats. The variations below come from regional locale differences, vendor history, and pragmatic adaptations. The format itself doesn’t self-describe, so the receiver has to be told which dialect to expect.

Dialect

Notes

RFC 4180

, separator, CRLF, double-quote escaping

Excel (US locale)

same; sometimes BOM at start

Excel (European locale)

; separator (because comma = decimal)

Unix CSV

LF line endings; otherwise RFC 4180

Unquoted minimal

no quoting; breaks on any embedded delimiter

Pipe-separated (PSV)

| separator; popular in finance / log files

Tab-separated (TSV)

\t separator; TSV

The lack of self-description means the receiver has to be told the dialect. Tools that auto-detect (csvkit’s csvclean, Python csv.Sniffer) help but aren’t reliable.

Parsing Pitfalls#

The bugs every CSV pipeline runs into. The single most common is treating CSV as a “split on comma” format, which fails the moment a value contains a comma, a newline, or a quote. Embedded delimiters, locale differences, BOMs, and quoting inconsistencies are the rest of the standard landmines.

id,description
1,The price is $3,000

Naïve parsers split on every comma; this row has 3 fields, not 2. The correct CSV would be.

id,description
1,"The price is $3,000"

Other classic traps.

  • Embedded newlines, only handled correctly inside quoted fields. Splitting on \n first then on , breaks immediately.

  • Trailing whitespace, `` Ada `` and Ada are different values; some readers trim, some don’t.

  • Empty vs. missing, a,,b has three fields where the middle is empty. Whether “empty” means null, "", or zero is a schema decision your CSV doesn’t capture.

  • Quoted numbers vs. strings, "123" is text in some parsers, a number in others.

  • Locale-dependent decimals, 3,14 is “3.14” in German / French CSVs; an unquoted comma will misalign columns.

  • BOM at start, Excel writes a UTF-8 BOM (); the first column header becomes id if not stripped.

  • Mixed line endings, some rows \r\n, some \n; some parsers tolerate, some don’t.

  • Excessive escaping, \" (backslash) instead of "" (RFC-correct).

The lesson: use a real CSV parser. Don’t split(",") outside a disposable shell one-liner.

Common Encodings#

CSV files arrive in many encodings. UTF-8 is the modern default, but legacy systems still produce UTF-16, Windows- 1252, and regional Asian encodings. The standard operator move is “convert to UTF-8 at the boundary” with iconv:

  • UTF-8 (with or without BOM), the modern default.

  • UTF-16, older Excel exports on Windows.

  • Windows-1252 / ISO-8859-1, many legacy systems.

  • Shift-JIS / EUC-JP / GBK, regional legacies.

Convert to UTF-8 at the boundary.

$ iconv -f WINDOWS-1252 -t UTF-8 input.csv > clean.csv

Detect encoding when you don’t know.

$ file -i input.csv
$ uchardet input.csv

CLI Tools#

The CLI tools that turn CSV from “load it into Excel” into something operators can pipeline. csvkit is the Python- based standard; xsv / qsv are the fast Rust alternatives; Miller is the Swiss Army knife; DuckDB lets you SQL-query CSV files in place.

  • csvkit, csvcut, csvgrep, csvjoin, csvstat, in2csv, csvjson.

  • xsv, Rust; very fast.

  • qsv, xsv fork with more commands.

  • mlr (Miller), “awk for CSV / TSV / JSON”; the Swiss Army knife.

  • csvlens, TUI viewer.

  • visidata, interactive TUI for exploring CSV-shaped data.

  • DuckDB, SELECT ... FROM 'data.csv' reads CSV directly with full SQL.

  • awk / sed, only for files where the format is simple enough to trust.

Per-Language Bindings#

The standard CSV libraries by language. Most languages ship something in their stdlib; the third-party options in the table are the ones that handle dialects, encoding, and streaming better than the stdlib defaults.

Language

Library

Python

csv (stdlib), pandas.read_csv, polars.read_csv, pyarrow.csv

Go

encoding/csv (stdlib)

Rust

csv (BurntSushi)

JavaScript / TypeScript

csv-parse, papaparse, fast-csv

Java

opencsv, commons-csv, univocity-parsers

C# / .NET

CsvHelper

Ruby

CSV (stdlib)

PHP

str_getcsv, League\Csv

For very large files (multi-GB), use streaming parsers (pyarrow, polars lazy frames, xsv, qsv) rather than loading everything into memory.

DuckDB Tricks#

DuckDB makes CSV practical for ad-hoc analytics by exposing SQL directly on top of CSV files, no load step, no schema declaration, full SQL semantics over pipe-separated, semi- colon-separated, multi-file globs. For analytical workloads, the standard pattern is “convert CSV to Parquet once and query that”:

-- Auto-detect everything
SELECT * FROM 'data.csv' LIMIT 10;

-- Force the dialect
SELECT * FROM read_csv('data.csv',
                       delim='|',
                       header=true,
                       types={'amount': 'DECIMAL(12,2)'});

-- Multi-file
SELECT * FROM 'data/*.csv';

-- Stream into Parquet
COPY (SELECT * FROM 'huge.csv')
     TO 'huge.parquet' (FORMAT PARQUET, COMPRESSION 'zstd');

For analytical workloads, convert CSV to Parquet once and query that.

Where CSV Wins#

The reasons CSV outlives every “modern replacement” proposal. Universal compatibility, human readability, spreadsheet export, line-oriented streaming, and Git- friendly diffing are why CSV is still the default for public data publication and inter-system exchange.

  • Universal compatibility, every tool, every language, every era.

  • Human readable, you can less it.

  • Easy export from spreadsheets, non-technical collaborators can produce it.

  • Streaming, line-oriented; works with pipes.

  • Diff-friendly, text; Git tracks changes line by line.

  • Public-data exchange, governments, statistics offices, finance filings.

Where CSV Loses#

The cases where another format would serve better. CSV’s lack of types, nesting, schema evolution, and binary efficiency become painful as data grows; analytical workloads at any non-trivial size belong in Parquet, not CSV.

  • Type information, everything is text; numbers, dates, booleans need conventions or out-of-band schema.

  • Nested data, arrays / objects don’t fit naturally.

  • Size, much larger than columnar / binary alternatives.

  • Performance, 5-50× slower than Parquet for analytics.

  • Schema evolution, header drift across files is painful.

  • Quoting overhead, escaping rules trip up many integrations.

When to Use CSV#

The use cases CSV is genuinely best at. Most boil down to “someone non-technical needs to look at this” or “we want a text format anything can ingest.” For long-term tabular storage at scale, switch to Parquet.

  • Data export for non-technical users (spreadsheet-bound).

  • Inter-system exchange with tools you don’t control.

  • Public data publication, universal access.

  • Small / medium ad-hoc data, < a few GB.

  • Logs / append-only writes, when you need streaming.

For long-term storage of tabular data at scale, use Parquet. For wire / API formats, use JSON.

A Stricter Alternative: TSV#

If you control both ends of the pipe, tab-separated is usually a better choice; tabs almost never appear in real data, so quoting is rarely needed and parsers can be split('\t') simple again. See TSV for the full picture.

Pitfalls Recap#

The condensed checklist. If a CSV pipeline is failing, walk this list; it covers the bulk of what goes wrong with parsing, dialect, encoding, BOMs, schema, and downstream analytics.

  • Don’t split(','), use a real parser.

  • Settle on a dialect; communicate it.

  • Specify the encoding; convert to UTF-8 if you can.

  • Strip BOMs.

  • Treat the schema as out-of-band (a separate doc, JSON Schema, or named columns).

  • For analytics, convert to Parquet once.

Workflow#

Extract, parse, filter, save. csvkit, mlr (Miller), or awk on the command line; pandas or polars for anything larger than memory.

Extract and inspect.

$ csvstat users.csv                           # per-column stats
$ csvcut -n users.csv                         # list columns with indexes
$ mlr --csv head -n 5 users.csv

Parse and filter.

# rows where status == active
$ csvgrep -c status -m active users.csv

# project columns
$ csvcut -c name,email,status users.csv

# Miller: predicate + column projection
$ mlr --csv filter '$age > 30' then cut -f name,email users.csv

# awk fallback (assumes no embedded commas in fields)
$ awk -F, 'NR==1 || $3=="active"' users.csv

Save.

$ csvgrep -c status -m active users.csv > active.csv
$ mlr --csv filter '$age > 30' then cut -f name,email users.csv > over30.csv

Python with pandas for non-trivial work.

import pandas as pd
df = pd.read_csv("users.csv")
active = df[df["status"] == "active"][["name", "email", "status"]]
active.to_csv("active.csv", index=False)

See Also#

  • TSV, the tab-delimited cousin.

  • Parquet, the columnar replacement for analytical CSVs.

  • JSON, the modern wire format.

Contents