What Is CSV? – Definition and How It Works
Learn what CSV is, why quoting and escaping matter, where the format is ambiguous, and when to use it rather than JSON or Parquet.
CSV stores tabular data as lines of delimited values. Its simplicity is why it is everywhere, and its underspecification is why it causes so many quiet data problems.
1. The Basic Shape
title,price,in_stock
"Widget, large",49.90,true
"He said ""hello""",12.00,false
Values are separated by a delimiter, records by line breaks, and any value containing the delimiter, a quote, or a line break must be quoted — with embedded quotes doubled.
2. Where the Format Is Ambiguous
| Question | Reality |
|---|---|
| Which delimiter? | Comma, semicolon, tab — regional conventions differ |
| Is there a header? | By convention, not by specification |
| Which line ending? | Both conventions occur, sometimes mixed |
| Which encoding? | Not declared anywhere in the file |
| How are nulls written? | Empty string, NULL, \N, or nothing — all in use |
| Are numbers localised? | Decimal commas appear in some locales |
Nothing in the file answers these, which is why CSV interchange requires out-of-band agreement.
3. The Classic Failures
- Unquoted values containing the delimiter, silently shifting every column after them.
- Encoding assumed rather than declared, producing garbled text.
- Leading zeros stripped when identifiers are treated as numbers.
- Long identifiers converted to scientific notation.
- Date strings reformatted by a spreadsheet on open and save.
- Empty versus null collapsed into one value.
Several of these are introduced by spreadsheet software rather than by the file's producer, which makes them hard to trace.
4. Writing CSV Safely
- Use a library rather than joining strings.
- Write UTF-8, and communicate that out of band.
- Quote conservatively — quoting more than strictly necessary is harmless.
- Choose one representation for null and document it.
- Keep identifiers as text and preserve leading zeros.
- Use ISO-style dates, which no locale reinterprets.
5. Reading CSV Safely
Declare the schema rather than inferring it: specify which columns are text, which are numeric, and what represents null. Type inference is where identifiers lose leading zeros and become floats.
Validate row width, and treat a row with an unexpected column count as an error rather than padding or truncating it.
6. When to Use Something Else
| Need | Better format |
|---|---|
| Nested structure | JSON or JSON Lines |
| Types preserved | Parquet or Avro |
| Very large datasets | Parquet, for compression and column pruning |
| Streaming appends of records | JSON Lines |
| Self-describing schema | Anything with a schema |
CSV remains excellent for flat, modest-sized data exchanged with people and spreadsheets — which is a large share of real work.
7. Practical Notes
Include a header row, keep one table per file, avoid embedded line breaks where possible, and publish a short schema note alongside the file. Those four habits prevent most CSV disputes.
8. Summary
CSV is a simple, ubiquitous, underspecified tabular format whose ambiguities — delimiter, encoding, nulls, types — must be agreed outside the file itself.
Use a library, write UTF-8, keep identifiers as text, declare types on read, and move to a typed format when structure or scale outgrows what a flat text table can carry.