CSV vs JSON: Which to Use and How to Convert
The real differences between CSV and JSON, when each fits, and the conversion traps: leading zeros, dates, encodings and nested data.
Editorial disclosure: Pyalm publishes and maintains the products and free tools discussed on this site. Regulatory statements are linked to primary sources where applicable. No independent professional review is claimed unless a reviewer is explicitly named.
CSV and JSON are the two formats you meet most often when moving data between spreadsheets, databases and APIs. Neither is better; they solve different problems, and most bugs come from converting between them carelessly.
Quick comparison
| CSV | JSON | |
|---|---|---|
| Structure | Flat table: rows and columns | Nested objects and arrays |
| Types | Everything is text | Strings, numbers, booleans, null, arrays, objects |
| Opens in Excel | Yes | Not directly |
| Typical use | Exports, imports, reporting, data science | APIs, configuration, documents |
| Size | Compact for tabular data | Larger (keys repeated per record) |
| Standard | RFC 4180 (loosely followed) | RFC 8259 |
Choose CSV when
- The data is naturally a table with the same columns on every row.
- The consumer is a spreadsheet, accountant or import tool.
- You need a very compact, streamable format.
Choose JSON when
- Records have nested or variable structure (an order with line items).
- Types matter — numbers versus strings, true versus "true".
- It's going to or from an API or a front-end.
For very large JSON datasets consider JSON Lines (one object per line) so you can stream and process records one at a time.
Common conversion traps
- Leading zeros. A phone number or ID like 007 becomes the number 7 if you infer types. Keep identifiers as strings.
- Dates. CSV has no date type. 03/04/2026 is 3 April in the UAE, India and UK and 4 March in the US. Use ISO 8601 (2026-04-03) when exchanging data.
- Delimiters. Many European and Gulf Excel locales use semicolons because the comma is the decimal separator. Detect the delimiter instead of assuming.
- Quoting. Fields containing commas, quotes or line breaks must be quoted, with embedded quotes doubled.
- Encoding. Use UTF-8. Excel often needs a BOM to open UTF-8 CSV with Arabic or accented characters correctly.
- Large numbers. Long IDs lose precision when read as floating-point numbers, in JavaScript and in Excel (which shows only 15 significant digits).
- Nested data. Flattening an object into columns (stock.qty) works; arrays of objects don't flatten cleanly — keep them as JSON in a cell or normalise into a second table.
- Empty values. In CSV, an empty cell could mean null, empty string or missing. Decide a convention.
A safe conversion routine
- Inspect the first lines and identify the delimiter and header.
- Decide per column whether it's a number, boolean, date or string (IDs, postcodes, phone numbers are strings).
- Convert.
- Validate the result: row counts match, no columns shifted, special characters survived.
- Spot-check a few records against the source.
Convert in your browser
The free CSV ⇄ JSON Converter auto-detects delimiters, can switch type detection off to protect identifiers, flattens nested JSON into dotted columns and runs entirely in your browser — useful when the data is customer or financial data you'd rather not paste into a random website. Format and validate the JSON side with the JSON Formatter and read how to format and validate JSON. If you then need types for the JSON, try JSON to TypeScript.
Building data exports for business users
If you build exports for accountants or operations teams, give them CSV with ISO dates, a header row, UTF-8 with BOM and a predictable column order. Accounting data usually ends up in a ledger anyway — if you're integrating or replacing spreadsheets, see custom software vs off-the-shelf and replacing Excel with custom software, or talk to Pyalm about a build.