CSV Hacks: Nine Tricks That Make a Boring Format Do More

None of these are exotic; all of them are things you re-derive every six months.

1. Make Excel keep a leading zero

Three ways, in ascending order of reliability:

1. Quote it and pray — most parsers strip the quoting and Excel re-infers the type anyway.
2. Prefix a tab: "\t01234". Excel keeps the text and the tab is invisible. Ugly if the CSV feeds anything except Excel.
3. Force the column to text on import. Excel: Data → From Text/CSV → transform, then set Detect column types off, or change the column type in Power Query. Command line: importlib.reload() is not the fix; the fix is openpyxl/pandas.read_csv(dtype={'zip': str}).

The only durable answer is a schema on the receiving side. If you cannot change the receiver, prefix the value with a character that is meaningful (+44…, ID:01234) rather than relying on quoting.

2. Arrays in one column

Two workable encodings, and they are not interchangeable:

tags,"a;b;c"          -- cheap, readable, breaks if a tag contains ;
tags,"[""a"",""b""]"  -- JSON, escaped by doubling quotes: ["a","b"]

The semicolon form is what you want when a human opens the file. The JSON form is what you want when a program reads it, because it distinguishes an empty array from one empty string. Whichever you pick, write it in the header name (tags_json) — a column whose type is "guess" is a column that will be parsed wrong.

3. Nested objects without exploding the schema

Two conventions exist and mixing them is how a flat file becomes a database migration:

• Dot paths: address.city, address.zip — Postgres COPY will not build you an object, so this is a convention for the reader, not the type system.
• One JSON column: payload holding a whole escaped object. Larger, but round-trips exactly and keeps you from inventing 40 columns for a webhook body.

Rule of thumb: if the nesting is known and stable, use dot paths. If it is someone else's API response, use one JSON column and stop parsing it in a spreadsheet.

4. Quote only what needs quoting

QUOTE_MINIMAL/csv.QUOTE_MINIMAL keeps files smaller and diffable. Quote everything when the consumer is Excel or a spreadsheet, because you cannot control type inference on a minimal-quoted file: a column containing 1E5 and 007 will be numbers whether you wanted it or not, and NA becomes NaN in some readers before you can see it.

import csv
w = csv.writer(fh, quoting=csv.QUOTE_NONNUMERIC)  # middle ground, still ambiguous
w = csv.writer(fh, quoting=csv.QUOTE_ALL)         # spreadsheet-safe

5. Read a file you cannot open

When a CSV arrives mangled, do not open it in Excel — Excel re-writes the file and destroys the evidence. Look at bytes first:

file -i data.csv          # charset guess, frequently wrong but free
hexdump -C data.csv | head -3
head -c 400 data.csv | iconv -f latin1 -t utf-8   # try the other encoding
awk -F, '{print NF}' data.csv | sort -u           # column counts present in the file

That last line is the fastest integrity check that exists: it prints every distinct field count in the file. 2 3 means the file has two shapes in it. If the answer is a single number you did not expect, the delimiter is wrong.

6. Split a huge file without writing a program

head -1 big.csv > part.csv            # keep the header
tail -n +2 big.csv | split -l 1000000 - chunk_
for f in chunk_*; do (head -1 big.csv; cat "$f") > "$f.csv"; done

tail -n +2 skips exactly one line, which is correct only if no field contains a newline. If it does — and address data usually does — use a real splitter (csvkit's csvlook/csvgrep, DuckDB COPY … TO 'x.csv' (FORMAT csv, PARTITION FROM (SELECT …))).

7. Add a line number to a file for bug reports

awk 'BEGIN{FS=OFS=","} {print NR, $0}' data.csv > annotated.csv

Prepending NR gives you a row reference that survives sorting and filtering on the receiving end, which is the whole reason log CSVs carry one. Add it to the header, or every consumer gets an unexpected column.

8. Diff two exports that "only had one change"

diff <(sort a.csv) <(sort b.csv)        # order-insensitive
csvjoin -c id a.csv b.csv               # csvkit: column-aware, ignores row order

Plain diff on unsorted CSV is theatre — reordering rows produces a wall of changes that means nothing. Comparing column counts first (awk '{print NF}' | sort -u) tells you whether the schemas even match before you look at values.

9. Convert through the browser when the file is the problem

For a one-off, pasting a few thousand rows into the converter and taking JSON or SQL out is faster than writing a script — and the ragged-row warning tells you whether the file is trustworthy before you load it anywhere. What it is not for is a repeating pipeline: the alternatives page lists the parsers that run in CI, and patterns and gotchas covers the duplicate-header case where a browser conversion silently keeps the last value.