CSV is compact and opens in any spreadsheet. JSON keeps types and nested structure. Each one fails in its own predictable ways, from lost leading zeros to rounded IDs. Here is how to choose, what goes wrong, and how to convert without losing data.
Almost every data job eventually means moving records between a system that speaks JSON, such as an API, a database export or a web app, and one that speaks CSV, such as a spreadsheet, a reporting tool or a bulk import screen. Both formats are plain text and both look simple. The problems appear later: a postcode loses its leading zero, a description with a comma splits into two columns, an order ID comes back with the last digits replaced by zeros.
This guide compares the two formats, lists the specific ways each one breaks, and explains how to convert between them safely.
Convert in your browser: our JSON ↔ CSV Converter works in both directions, flattens nested objects into dotted columns, handles quoted fields properly, and can add the marker Excel needs to show accented characters correctly. Your data is not uploaded.
The same data in both formats
Two customer records in JSON:
[
{"id": "00417", "name": "Ada Lovelace", "vip": true,
"address": {"city": "London", "postcode": "W1 2AB"},
"tags": ["math", "early adopter"]},
{"id": "00418", "name": "Grace Hopper", "vip": false,
"address": {"city": "New York", "postcode": "10001"},
"tags": ["navy"]}
]The same records as CSV, flattened:
id,name,vip,address.city,address.postcode,tags.0,tags.1
00417,Ada Lovelace,true,London,W1 2AB,math,early adopter
00418,Grace Hopper,false,New York,10001,navy,The CSV is shorter and easier to scan, but some information has gone. Nothing says id is text rather than a number, vip is now the word “true”, and the list of tags has become numbered columns, with an empty cell where Grace has no second tag.
Side by side
| CSV | JSON | |
|---|---|---|
| Shape | A flat table: rows and columns | Any structure: objects, lists, nesting |
| Data types | None. Every value is text. | Strings, numbers, true/false, null, objects, arrays |
| Dates | Text, in whatever format was written | Also text (no date type), usually ISO 8601 |
| Size for tables | Small: column names appear once | Larger: every record repeats every key |
| Opens in a spreadsheet | Yes, by double-clicking | Not directly |
| Standard | RFC 4180, informational only; many dialects | RFC 8259, strict and widely followed |
| Best for | Spreadsheets, reports, bulk imports, data science | APIs, configuration, nested or varied records |
Where CSV breaks
Commas, quotes and line breaks inside values
A value containing the delimiter, a double quote or a line break must be wrapped in double quotes, and any quote inside it doubled: "Smith, John", "He said ""hi""". Code that builds CSV by joining strings with commas gets this wrong, and the file splits rows and columns in the wrong places. A good converter handles it automatically, in both directions.
No types, so spreadsheets guess
Because CSV has no types, Excel and other spreadsheets guess each cell when they open the file, and the guesses change your data:
- Leading zeros vanish. ZIP code
02139becomes2139; an ID00417becomes417. - Long numbers are rounded. Excel keeps 15 significant digits. A 16-digit card or account number, or an 18-digit order ID, has its last digits replaced with zeros, often displayed in scientific notation like
1.23457E+17. - Codes become dates. Values like
1-2,MAR1orSEPT2are converted into dates. The problem was common enough in genetics research that several human genes were officially renamed in 2020 to stop it.
Saving the file again from the spreadsheet makes these changes permanent. To avoid them, use Excel’s Data › From Text/CSV import and set sensitive columns to Text, rather than double-clicking the file.
Delimiters and regional settings
In countries that write decimals with a comma, such as most of Europe, Excel expects CSV files to use semicolons. A comma-separated file then opens with everything in column A. Choose the delimiter your recipients’ software expects; our converter offers comma, semicolon, tab and pipe.
Character encoding
Excel on Windows opens a CSV in the computer’s legacy character set unless the file starts with a byte-order mark (BOM). Without one, UTF-8 text such as café or € shows up as café or €. Adding a BOM fixes Excel, but a few programming tools then see an invisible character at the start of the first column name. Our converter adds the BOM to downloads by default, with a box to turn it off.
No nesting
A customer with three addresses, or an order with a variable number of items, does not fit one row. You either flatten it into numbered columns (items.0.sku, items.1.sku…), repeat the parent data on one row per item, or split it into two related files.
Formula injection
A cell starting with =, +, - or @ may be run as a formula when the file is opened in a spreadsheet. If a CSV contains text typed by the public, such as names or comments, a malicious entry can trigger formulas on the computer of whoever opens the export. Systems that export user data to CSV should neutralise such cells, commonly by prefixing them with a single quote.
Where JSON breaks
Strict syntax
JSON allows no comments, no trailing commas and no single quotes. {"a": 1,} is invalid, and so is {'a': 1}. Hand-edited JSON fails on these constantly. Paste it into our JSON Formatter to find the line and column of the error. For configuration that people edit by hand, YAML is often friendlier, and our YAML to JSON converter moves between the two.
Very large numbers
The JSON format puts no limit on numbers, but JavaScript, and many tools built on it, store them as 64-bit floating point. Integers above 9,007,199,254,740,991 (253 − 1) silently lose precision: 9007199254740993 is read as 9007199254740992. Large database IDs are the usual victims. APIs that use such IDs often send them as strings too, which is why some include both id and id_str.
No dates, no NaN
JSON has no date type, so dates travel as strings, and both sides must agree on the format. ISO 8601 (2026-10-03T16:00:00Z) is the safe choice. JSON also cannot represent NaN or Infinity; serialisers turn them into null or refuse.
Size and huge files
Repeating every key in every record makes JSON larger than CSV for table-like data. A single giant JSON array also has to be read in full before most tools can use it. For millions of records, JSON Lines (one object per line) can be processed one record at a time.
Duplicate keys
The standard says keys should be unique but does not say what happens when they are not. Most parsers keep the last value; some keep the first or reject the document. Never rely on duplicates.
Which should you use?
Choose CSV when the data is a flat table, the reader is a person with a spreadsheet, or the destination is a bulk import, a BI tool or a data-science notebook. Keep IDs and codes as text, and agree on a delimiter and encoding.
Choose JSON when the data is nested or varies from record to record, types matter, or the reader is a program, an API or a configuration system.
Many systems use both: JSON between programs, and a CSV export for people who need to sort and filter.
Converting without losing data
- JSON to CSV: decide how to handle nesting. Flatten to dotted columns for a readable sheet, or keep nested values as JSON text in a cell if the CSV will be read back by a program. Make sure every key from every record becomes a column, not just the keys in the first record, or data in later records disappears.
- CSV to JSON: decide whether to convert values to numbers and true/false. Converting is convenient, but it strips leading zeros from IDs and postcodes. If the CSV has dotted headers, they can be rebuilt into nested objects.
- Check the round trip. Convert a sample one way and back again, and compare it with the original. Our Diff Checker shows exactly what changed.
The JSON ↔ CSV Converter does all of this. Columns are the union of every key in every record, quoted fields follow RFC 4180, type conversion is optional, and dotted headers can be rebuilt into nested JSON.
Frequently asked questions
Is JSON or CSV smaller?
For flat, table-shaped data, CSV is usually much smaller, because column names appear once in the header instead of in every record. Compression narrows the gap, since repeated keys compress well.
Can CSV store nested data?
Not directly. Nested objects have to be flattened into extra columns (such as address.city) or stored as a JSON string inside a cell. Data with lists of varying length, such as orders with line items, usually fits JSON better.
Why did Excel change my numbers?
Excel guesses the type of every CSV cell. It removes leading zeros, turns some codes into dates, shows long numbers in scientific notation, and keeps only 15 significant digits. Import the file through Excel’s data import and set those columns to Text.
What is JSON Lines?
JSON Lines (also called NDJSON) puts one complete JSON object on each line, with no surrounding array. A program can read it one record at a time, so it suits logs and very large exports.