csv2json: a tolerant stdlib converter (BOM, delimiters, ragged rows, dup headers)

I kept hitting the same three failures when converting client CSVs to JSON, so I wrote them out of the tool instead of patching each dataset once.

What breaks in practice, and what this does

1. Excel writes a UTF-8 BOM (\ufeff) on the first header. json.dumps then emits "\ufeffid" and every downstream key lookup misses. The reader strips the BOM before parsing and normalises headers to lowercase snake_case.
2. Delimiters are not always commas. Commas inside quoted fields, ; from European locales, and tab-separated exports all appear in the same week. The delimiter is sniffed from the first non-empty line with csv.Sniffer and falls back to a comma count if sniffing throws.
3. Ragged rows are the quiet killer: a short row shifts nothing but silently nulls trailing fields, a long row raises or poisons the dict. Short rows are padded with null; extra cells land in _extra so nothing is lost without a trace.
4. Duplicate header names (price, price) currently produce last-wins data loss. They become price and price_2, deterministically in column order.
5. Types stay strings unless --types is passed; with it, integers, floats, booleans and null/empty are coerced, and anything ambiguous stays a string rather than becoming a wrong number.

Interfaces

csv2json.py in.csv out.json for files, --ndjson to emit one object per line for streaming loaders, --columns a,b,c to project and order keys, and --delimiter ';' to override sniffing. Output is json.dump(..., ensure_ascii=False) with indent=2 so diffs stay readable in review.

Testing

Five unittest cases cover BOM + quoted-comma round trip, semicolon sniffing, ragged short and long rows, duplicate headers, and --types coercion with an ambiguous value. All pass on Python 3.13 with no third-party dependencies, which matters when the converter has to run inside a locked-down pipeline box.

Source: https://github.com/ciperx/csv2json — raw file returns 200 and the tests run with python3 -m unittest -v.

If your exports come from a system I have not listed (SAP ALV, Salesforce report, NetSuite saved search), the failure mode is usually a quoted newline or a trailing totals row. Tell me which one and I will add a case with the fixture rather than guessing.