Programming

JSON vs CSV: Choosing the Right Data Format (And Converting Safely)

The real trade-offs between JSON and CSV, the encoding traps that silently corrupt data, and how to convert between them without losing information.

·3 min read·677 words
A glowing table grid transforming into nested tree nodes

Almost every data exchange eventually becomes a choice between JSON and CSV. Both are text, both are ubiquitous, and they are good at completely different things. Picking wrong causes weeks of quiet data corruption, so it is worth ten minutes of thought.

What each format is genuinely good at

CSV is a grid: rows and columns, one record per line. It is compact, streams beautifully, opens in any spreadsheet, and a non-technical colleague can edit it. It is the right choice for tabular exports, analytics dumps, bulk imports and anything a human will open in Excel.

JSON is a tree: nested objects and arrays with real types. It preserves structure, distinguishes numbers from strings from booleans from null, and handles optional and repeated fields naturally. It is the right choice for APIs, configuration, event payloads and anything with nesting.

The one-line rule: if your data is a table, use CSV; if your data has shape, use JSON.

Where CSV bites

There is no real standard. Comma or semicolon separators, CRLF or LF line endings, quoting conventions and header rows all vary. A file produced in one locale can be misread in another.

Everything is a string. 007, 2026-03-04 and +44 20 7946 are all just characters until something guesses. Spreadsheets guess aggressively and destructively — leading zeros vanish, product codes become dates, long identifiers become scientific notation. This is the single most common way real data gets corrupted.

Embedded commas, quotes and newlines need escaping, and hand-rolled splitters get this wrong constantly. A field containing "Smith, John" will break any parser written with a naive split.

Encoding. Files without a byte order mark or an explicit UTF-8 declaration frequently open as mojibake, mangling every non-English name in the file.

No nesting. Representing an order with several line items means either repeating the order across rows or stuffing JSON into a cell — both unpleasant.

Where JSON bites

Verbosity. Every record repeats every key. For a million-row export that is a lot of wasted bytes, though compression hides most of it.

No comments, which makes JSON a slightly awkward configuration format.

Number precision. Very large integers lose accuracy in environments where all numbers are floating point. Send big identifiers as strings.

Whole-document parsing. Without a streaming parser, a huge JSON file must be loaded entirely into memory. JSON Lines — one JSON object per line — solves this neatly and is worth knowing about.

Dates are not a type. Everyone agrees to use ISO 8601 strings; nothing enforces it.

Converting between them safely

JSON to CSV. Decide how to flatten nesting before you start — usually dotted keys like customer.address.city. Arrays are the hard part: either join them with a delimiter, expand them into multiple rows, or serialise them as a JSON string in one cell. Whichever you choose, do it consistently and document it. Collect the full set of keys across all records first, so rows with missing fields still align to the header.

CSV to JSON. Do not infer types silently. "true", "1" and "" mean different things in different systems, and guessing produces bugs that appear months later. Either keep everything as strings and cast deliberately downstream, or supply an explicit schema mapping each column to a type.

Practical rules that prevent pain

  1. Always write and read UTF-8, and say so explicitly.
  2. Always quote fields in CSV output. It costs a few bytes and removes an entire class of bugs.
  3. Use ISO 8601 for every date in both formats.
  4. Send identifiers as strings, never as numbers.
  5. Include a header row in CSV; without it the file is unreadable in six months.
  6. For very large JSON datasets, use JSON Lines so consumers can stream.
  7. Use a real parser library. Every language has one, and every hand-rolled CSV splitter is wrong in a way that will eventually cost a day of debugging.

Get these seven right and the format choice itself becomes almost boring — which is exactly what you want from a data pipeline.

#json#csv#data

Keep reading