CSV vs JSON: Common Data File Formats

Data has to travel. It moves from a spreadsheet to a database, from a website to a phone app, or from one team to another by email. When it travels, it usually travels as a plain text file, and two formats dominate: CSV and JSON. Both are easy to open in a text editor, both are free of any single vendor, and both are good at different things.

This entry shows what each format looks like, what it handles well, and where it causes trouble. One small food truck menu is written in both formats so you can compare them line by line. It covers the formats themselves. How to tidy the values inside them is a separate topic, covered in data cleaning basics.

What CSV is

CSV stands for comma-separated values. A CSV file is plain text with one record per line and the fields of each record separated by commas. The first line often holds the column names, called a header.

Here is the food truck menu as CSV:

item,price,vegetarian
Bean burrito,7.50,yes
"Mango, chili salad",6.00,yes
Fish taco,8.25,no
"The ""Big"" Nacho",9.00,yes

Every row has the same three fields. CSV is flat: it describes a single table, nothing more. That is why spreadsheets and databases can import and export it easily. A table maps directly onto it.

RFC 4180 is the document that describes the format. It is an informational RFC, not a formal standard, and it says plainly that CSV existed for years before it was written down. Programs follow it loosely, so files from different tools may differ in small ways.

The quoting rules

The menu above shows the two rules that cause the most confusion. According to RFC 4180, a field that contains a comma, a double quote or a line break should be wrapped in double quotes. That is why "Mango, chili salad" is quoted: without quotes, the comma would split it into two fields. A double quote inside a quoted field is written twice, which is why the last item appears as "The ""Big"" Nacho".

What JSON is

JSON stands for JavaScript Object Notation. It is a text format built from a few pieces: objects (named values in curly braces), arrays (ordered lists in square brackets), strings, numbers, and the words true, false and null. RFC 8259 defines it, and despite the name, it is used with almost every programming language.

The same menu in JSON:

[
  { "item": "Bean burrito", "price": 7.5, "vegetarian": true, "allergens": ["wheat"] },
  { "item": "Mango, chili salad", "price": 6.0, "vegetarian": true, "allergens": [] },
  { "item": "Fish taco", "price": 8.25, "vegetarian": false, "allergens": ["fish", "wheat"] },
  { "item": "The \"Big\" Nacho", "price": 9.0, "vegetarian": true, "allergens": ["milk"] }
]

Three things stand out. Every value has a visible type: 7.5 is a number, true is a true or false value, and "Fish taco" is text. Each record names its own fields, so the file explains itself. And the allergens field holds a list, which a flat CSV row cannot do cleanly.

Programs read and write JSON with simple tools. In JavaScript, JSON.parse(text) turns a JSON string into data you can work with, and JSON.stringify(data) does the reverse. MDN documents both, and most other languages have a similar pair of functions.

Side-by-side comparison

Where each one breaks

CSV pitfalls

• No types. The text 007 in a CSV is just characters. A spreadsheet may turn it into the number 7 and drop the zeros, which damages values like postal codes and ID numbers. Importing such columns as text avoids this.
• Dates are ambiguous. 03/04/2026 means different days in different countries. A format that puts the year first, such as 2026-03-04, is much safer.
• The delimiter varies. Some programs, in some regional settings, write semicolons or tabs instead of commas. If a file opens as one wide column, check the separator.
• Line breaks and encoding. RFC 4180 describes lines ending in CRLF, but some tools write other endings. Text in unusual characters needs a consistent encoding, and UTF-8 is the safe choice.
• No nesting. A list of allergens must be squeezed into one cell with a made-up separator, and every consumer has to know the trick.

JSON pitfalls

• Strict punctuation. Keys and strings need double quotes. Trailing commas are not allowed. MDN notes that JSON is stricter than JavaScript object syntax, so code that works in a script may fail as JSON.
• No comments. The grammar defines no comment syntax, so notes inside a JSON file are errors in strict parsers.
• Big numbers. RFC 8259 says whole numbers up to 2 to the power of 53, minus 1 (about nine quadrillion), in either direction, are safe to exchange between programs. Larger values can lose precision. MDN shows JSON.parse rounding a 20-digit number. Store long identifiers as strings.
• Repetition. For a big flat table, repeating every field name on every record wastes space.
• Not spreadsheet friendly. Opening it in a sheet usually needs an import step or a conversion.

Which one to choose

Pick CSV when the data is one flat table, a person may open it in a spreadsheet, or you need a simple export that any tool can read. Reports, product lists and survey results are typical.

Pick JSON when records have nested parts or lists, when types matter, or when a program is on the other end, such as a web service sending data to an app. Settings and configuration data fit too.

Many projects use both. A shop might export sales as CSV for the accountant and send the same sales as JSON to its website. Because both are plain text, converting between them is a common, safe task, as long as you decide in advance how nested values will be flattened.

Common mistakes

• Splitting CSV lines on commas by hand. Quoted fields can contain commas, so use a proper CSV reader.
• Opening a CSV in a spreadsheet and saving it back. The program may change leading zeros, dates and long numbers without warning.
• Assuming CSV and JSON hold the same information. Nested data is lost or distorted when flattened.
• Adding comments to JSON. Strict parsers reject them. Put notes in a separate field or a separate document.
• Forgetting the encoding. Names with accents or other scripts turn into garbled text if the reader guesses the wrong encoding.
• Using single quotes in JSON. Only double quotes are valid.

FAQ

Is CSV the same as an Excel file?

No. CSV is plain text with no formatting, formulas or multiple sheets. A spreadsheet file is a more complex container. Spreadsheets can open and save CSV, but only the values survive.

Is JSON a database?

No. JSON is a way to write data down. Some databases store JSON documents, but the format itself has no searching, rules or multi-user handling. The relational vs NoSQL databases entry explains where documents fit.

Which format is smaller?

For plain tables, CSV is usually smaller, since field names appear once. For nested data, JSON can be more compact than a workaround in CSV. Size rarely decides the choice.

Can JSON have more than one top-level item?

A JSON text is a single value, but that value can be an array, as in the menu above. Wrap many records in one array.

Which should I learn first?

Learn CSV first, because it matches how spreadsheets look. Then learn JSON, which you will meet quickly once you work with web services. The step from sheets to databases is covered in spreadsheet vs database. More basics are in the Data and Databases area.