Developer guide
CSV Cheat Sheet: Commands, Tools & Formatting Rules
CSV looks trivial until a value contains a comma, a quote, or a newline. This cheat sheet covers the rules that matter, the commands that save time, and the exact quoting patterns that keep every parser happy — with copy-paste examples throughout.
Written by Benjamin Rotshtein
Updated
- When must I quote a field?
- When it contains a comma, a double quote, or a newline. Quote whole fields — never try to escape just the offending character.
- How do I write a literal quote?
- Double it:
""inside a quoted field is one literal quote. This is the RFC 4180 rule every real parser expects. - Best quick tools?
- Python's csv module for parsing, csvkit for the CLI, jq for JSON. For browser-safe conversion, use the converters on this site.
The formatting rules that always apply
- One record per line; fields separated by commas.
- A field containing a comma, quote, or newline must be enclosed in double quotes.
- Inside a quoted field, a literal quote is written as two quotes:
"". - The header row is optional but conventional; treat the first row as headers unless told otherwise.
- A trailing newline at the end of the file is normal and harmless.
- Save as UTF-8 (with BOM for Excel on Windows).
Quoting, shown correctly
name,notes Alice,plain value Bob,"contains, a comma" Carol,"says ""hi""" Dana,"spans two lines"
Every line above is valid CSV. The third row embeds quotes (doubled), and the fourth embeds a real newline inside the quoted field. A naive split(",") breaks all three — always parse with a real CSV parser.
RFC 4180 in one paragraph
RFC 4180 is the closest thing CSV has to a standard. It defines the common MIME type, mandates CRLF line endings, permits an optional header row, and — most importantly — specifies the quoting rules above: fields with commas, quotes or line breaks get wrapped in double quotes, and embedded quotes are doubled. Real files in the wild often skip the CRLF and header-row parts, but the quoting rules are the ones you must always follow.
Command-line one-liners
Peek at the first rows:
head -5 file.csv
Validate and pretty-print (Python stdlib):
python -c "import csv,sys; [print(r) for r in csv.reader(sys.stdin)]" < file.csv
CSV to JSON with csvkit:
in2csv --format csv file.csv | csvjson > file.json
Convert with Python's csv + json modules:
python -c "
import csv, json, sys
rows = list(csv.DictReader(open('file.csv', encoding='utf-8')))
json.dump(rows, open('file.json','w'), indent=2)
"Filter with jq (JSON output):
in2csv --format csv file.csv | csvjson | jq '.[] | select(.age > 30)'
Tools worth keeping on hand
- Python csv: the reference parser, in the standard library, handles quoting and newlines correctly.
- csvkit: in2csv, csvcut, csvstat, csvsql — the SQL-like CLI suite for CSV.
- jq: pairs with csvjson for filter/map/transform on converted JSON.
- pandas:
read_csvwithquoting=csv.QUOTE_ALLfor DataFrame work. - Our browser tools: the JSON to CSV converter and CSV to JSON converter handle quoting and encoding with nothing leaving your browser.
Frequently asked questions
When must a CSV field be wrapped in quotes?
When the value contains a comma, a double quote, a newline, or (to be safe) leading or trailing spaces. Everything else can stay bare. Quoting the whole field keeps the parser from misreading the delimiter.
How do I escape a double quote inside a quoted field?
Double it. The sequence "" inside a quoted field means a literal quote character. So the value He said "hi" becomes "He said ""hi""" in CSV. This rule comes from RFC 4180.
Can a CSV field contain a newline?
Yes. A field that wraps across lines must be quoted, and the newline inside the quotes is part of the value. This is legal CSV, but some tools handle it poorly, so keep it quoted and test in the tool that reads it.
What is the fastest way to validate a CSV on the command line?
Use Python's csv module: `python -c "import csv,sys; [list(r) for r in csv.reader(sys.stdin)]" < file.csv`. It raises on malformed rows. csvkit's csvclean and in2csv are the next handiest options.
How do I convert CSV to JSON with Python?
The standard library does it: read with csv.DictReader, then json.dump the list of dictionaries. Use `python -c` for a one-liner or the csvkit tool in2csv, or use our browser-side converter which never uploads your data.
What encoding should my CSV use?
UTF-8 with a BOM when the file will be opened in Excel on Windows. The BOM tells Excel the encoding so it doesn't guess and mangle accented or non-Latin text. See our CSV encoding guide for the full fix.