Formatting standards ·
Structuring JSON and CSV so machines and Excel both survive
CSV is a rectangle with quoting rules (RFC 4180). JSON is a typed tree. Excel is a locale-sensitive display layer that will destroy leading zeros and dates. Structure data for parsers first, then export a spreadsheet. Do that in the tab so a “cleaner API” never sees the customer list.
RFC 4180 versus Excel TSV
Comma, CRLF, quotes doubled. European Excel often expects semicolon. Sniff on parse; declare on unparse. Do not mix tabs and commas in one file without a spec.
UTF-8 BOM
EF BB BF. Helpful for Excel, harmful for some JSON and hash pipelines. The local CSV editor can toggle it on export.
Types: strings versus numbers versus dates
ISO 8601 strings survive. Excel serial dates do not round-trip through CSV reliably. Phone numbers and ZIP codes are strings. Flattening nested JSON (silo 1) produces extra columns; name them stably.
JSON Schema versus header rows
If consumers are programs, JSON + schema. If consumers are analysts, CSV plus a README of types. Markdown tables are for docs, not 100k rows.
Local edit loop
Parse, fix, export. Bank-statement CSV is the privacy-sensitive version of this loop. Do not re-upload to a second cleaner.
Frequently asked questions
Should I add a UTF-8 BOM to CSV?
Excel on Windows often needs BOM to detect UTF-8. JSON.parse will reject a BOM. Keep two export modes.
How do I stop Excel from turning IDs into numbers?
Tab-separated with a leading apostrophe is a hack. Better: document “open with Google Sheets / import as text” or keep JSON for IDs.
Is the first row a schema?
Only by convention. JSON Schema or a separate types file is real schema. First-row headers collide with data that looks like headers.