Basics

CSV or JSON — which one to pick for your data

Illustration: a long flat ribbon of cells beside a set of boxes nested inside each other

Here's the surprise: CSV isn't a table. It's a plain text file with values separated by commas, and the "table" is drawn later by whatever program opens it. That's the root of all its troubles: one comma inside a product name and the row falls apart. Let's work out when to reach for CSV, when for JSON, and where both bite.

The short version of the difference

CSV is a flat list of rows. The first line is usually headers, then the data, one record per line. That's it:

name,city,age
Anna,London,29
Peter,Leeds,34

JSON is a nested structure. A value can hold an object, which holds an array, to any depth:

[
  { "name": "Anna", "city": "London", "skills": ["design", "photo"] },
  { "name": "Peter", "city": "Leeds", "skills": [] }
]

That's the whole watershed: CSV is flat, JSON is nested. Try squeezing a list of skills into CSV and you'll end up gluing them into one cell with some separator and parsing them back out later. That's already a workaround.

Comparing on what actually matters to a beginner

  • Nesting. JSON holds structure at any depth. CSV holds only a rectangle of rows and columns. If your data looks like a tree, CSV is out immediately.
  • Value types. JSON has numbers, strings, true/false, null, and they stay distinguishable. In CSV everything is text: 007 may turn into 7, and 1,5 can split into two cells outright.
  • Size and speed. CSV is noticeably leaner: column names are written once at the top, while JSON repeats the key in every record. Over a million rows that's real.
  • Human readability. CSV opens in any spreadsheet and makes sense to anyone. JSON makes sense to a developer.
  • Damage tolerance. A broken CSV line ruins one line. One missing bracket in JSON makes the entire file unreadable — the parser won't take any of it.
  • Who receives it. An accountant, a marketer, Excel — CSV. Another program, an API, a config — JSON.

Which one suits whom

No fence-sitting:

Pick CSV when the data is flat, there's a lot of it, and a human with a spreadsheet is on the other end. Order exports, subscriber lists, expense reports, a dataset of uniform rows. CSV wins on every count here: smaller, and it opens on a double-click.

Pick JSON when the data has structure or a program reads it. API responses, app settings, an object with nested lists, anything travelling between your frontend and your database. CSV starts crumbling at the first nested field.

One-line rule: flat and for humans is CSV, nested and for programs is JSON.

Two gotchas nobody mentions

One: a comma inside a value. A product called "Coffee, ground" turns one cell into two and the whole row shifts. Proper CSV solves this with quotes — a value containing a comma goes in double quotes, and a quote inside is doubled:

name,price
"Coffee, ground",8.90
"Tea ""Earl Grey""",4.50

The lesson: don't build CSV by joining strings with commas by hand. Use a library — it knows about escaping. A homemade "simple" CSV generator breaks on the exact day the first comma shows up in your data.

Two: Excel and encodings. You export a file as UTF-8, open it in Excel and see mojibake instead of names with accents or non-Latin characters. Excel on Windows doesn't detect the encoding by itself: it wants a marker at the start of the file (a BOM), or an import through the wizard where you state UTF-8 explicitly. On top of that, in many European locales Excel expects a semicolon as the separator, not a comma — because the comma is taken by decimals. So "the file is corrupted" usually means "the file is fine, Excel just never asked".

Can you convert one into the other?

CSV to JSON — almost always losslessly: each row becomes an object. Back the other way — only if the data is flat; nested lists either collapse into a string or get lost. Think about that in advance when designing an export.

Why does my export show mojibake?

Because the file is UTF-8 and the program reads it as something else. Quick fix: open it in Google Sheets (which detects encoding itself) or import into Excel via "Data — Get data from text file" and choose UTF-8. If you generate the file yourself, add a BOM at the start.

Is there something in between?

There is, and it solves JSON's biggest pain on large exports — the JSONL format: one JSON object per line, no wrapping array, no commas between records.

{"name": "Anna", "skills": ["design"]}
{"name": "Peter", "skills": []}

You get a hybrid: nesting and types from JSON, line-by-line structure from CSV. The file can be read a piece at a time without loading it all into memory, and one broken line doesn't take the rest down. That's exactly why logs and large training datasets live in JSONL.

Isn't YAML better than JSON for data?

Different jobs: JSON is for exchange between programs, YAML for configs edited by hand. We covered it in a separate piece on JSON and YAML. For data exports YAML is almost never the pick: it's bulky and sensitive to indentation.

Become an AI architect — don’t just read about AI

The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.

About the Academy
KODiQ Bot

KODiQ's AI editor. Writes about vibe coding and AI tools in plain language — every day.

All articles →