CSV vs JSON: Structure, Types, Excel Delimiters and Conversion

CSV and JSON both store data as plain text, but they are built for different shapes. CSV (comma-separated values) is a flat table: each line is a record, each column a field, and the first line is usually a header. JSON is a tree: objects and arrays can nest inside each other, and every value has a type, whether string, number, boolean or null. The short answer to CSV vs JSON is that CSV suits spreadsheets and bulk imports, while JSON suits APIs, configuration and anything nested.

When you need to move data between the two, CSV to JSON and JSON to CSV convert it without the data leaving your browser; nothing you paste is sent to the server. Before converting, the JSON formatter validates and indents the payload, and the text diff checker shows exactly what changed between two exports.

CSV vs JSON at a glance

Here are the same three records in both formats. As a table:

id,name,city
1,Ana,Leeds
2,Ben,York
3,Cleo,Bath

As a tree:

[{"id":1,"name":"Ana","city":"Leeds"},{"id":2,"name":"Ben","city":"York"},{"id":3,"name":"Cleo","city":"Bath"}]

The first is 48 bytes; the second is 111 bytes without whitespace and 184 once indented with two spaces. The difference comes from repeating every field name in every record, plus the quotes and braces. In exchange, the 1 in JSON is a number, whereas the 1 in the table is just a character that some program will later decide to treat as a number.

Aspect	CSV	JSON
Structure	Flat: rows and columns	Tree: objects, arrays, nesting
Types	None; every cell is text	String, number, true/false, null, object, array
Specification	RFC 4180 (informational, 2005)	RFC 8259 and ECMA-404
Delimiter	Comma in theory; semicolons, tabs and pipes in practice	Fixed syntax
Character encoding	Unspecified, varies by file	UTF-8 required between systems
Comments	None	None
Opening in Excel	Direct, but locale-dependent	Needs a Power Query import
Streaming line by line	Easy, except for multi-line fields	One document; use JSON Lines for streams

Types: in CSV, everything is text

JSON knows six types: string, number, boolean, null, object and array. CSV knows none. Whether a cell is a number, a date or a code is decided by whatever opens the file, and that guess is often wrong. The classic spreadsheet casualties:

• Leading zeros disappear. The Boston ZIP code 02134 becomes 2134, and product codes like 007 become 7.
• Long numbers get corrupted. Excel keeps 15 significant digits, so the last digit of a 16-digit card number turns into a zero, and long IDs switch to scientific notation such as 1.23E+15.
• Codes turn into dates. Values like 1-2 or 3/4 are read as dates. The problem was common enough in genetics that the official human gene naming rules were changed in 2020 to stop gene symbols from being converted.
• Decimal commas collide. A file from a colleague in Germany, Spain or Turkey may write 12,50 for twelve and a half, which an English-locale system reads as two fields or as text.

That is why the CSV to JSON converter here does not interpret anything: every cell stays a string in the output. 007 comes out as "007" with its zeros intact. If a field should be a number or a date, convert it deliberately in your own code, where you know what the field means.

Excel, delimiters and regional settings

When someone says every row landed in column A, the delimiter is almost always the reason. When Excel opens a CSV file, it uses the list separator from the operating system's regional settings. In locales that use a decimal comma, which includes most of continental Europe and Latin America, that separator is a semicolon. The effect cuts both ways: a comma-separated file collapses into one column on those machines, and the file their Excel saves as CSV is really semicolon-separated and looks broken on yours.

1. Instead of double-clicking the file, use Excel's From Text/CSV import on the Data tab.
2. Set File Origin to 65001: Unicode (UTF-8).
3. Choose the delimiter explicitly: comma, semicolon or tab.
4. If needed, use Transform Data to set codes, ZIP codes and phone numbers to the Text type so leading zeros survive.
5. Check accented characters and the column count in the preview, then load.

Encoding is the second trap. Excel often reads a UTF-8 file without a byte order mark (BOM) using a legacy Windows code page, so café shows up as café. Saving with the CSV UTF-8 option in Excel writes a BOM and fixes this. Some people add sep=, as the first line to tell Excel the delimiter; that hint is Excel-specific and non-standard, and every other parser will treat the line as data.

The converter on this site detects the delimiter for you, supporting commas, semicolons, tabs and pipes, so a semicolon file from a European colleague needs no extra settings. Output going the other way is comma-separated; if it has to open in a semicolon-locale Excel, use the import steps above.

Quoting rules from RFC 4180

Most broken tables come from delimiters or line breaks appearing inside the data itself. RFC 4180 settles this with a few rules:

• A field containing the delimiter, a double quote or a line break is wrapped in double quotes: "Pen, blue".
• A double quote inside a quoted field is escaped by doubling it: "Says ""hi""".
• A quoted field may span several lines; that line break does not end the record.
• Every record should have the same number of fields. Records are separated by CRLF, and a final line break is optional.

Put together, a small file looks like this:

sku,name,price,note
007,"Pen, blue",2.50,"Says ""hi"""
014,Notebook,4,

The converter turns the first data line into {"sku": "007", "name": "Pen, blue", "price": "2.50", "note": "Says \"hi\""}. The leading zeros survive, the embedded comma stays inside its field, and the empty note in the second record comes through as an empty string.

Broken files you will meet in the wild

• An unclosed quote swallows the rest of the file into a single field. The converter stops with an error saying a quoted field is not closed.
• Backslash escaping. Some programs write quotes as \". That is not RFC 4180 and shifts fields in most parsers.
• Ragged rows. An extra delimiter pushes values one column to the right. The converter puts surplus cells under generated names such as column_5, so the shift is visible rather than silent.
• Duplicate headers. Two columns with the same name would overwrite each other in a JSON object, so the converter renames the second one with a _2 suffix: price and price_2.

Converting in both directions

From a table to JSON

1. Paste the table into the converter; the delimiter is detected automatically.
2. Make sure the first line is a header, because it becomes the field names.
3. Check the result: one object per line, every value a string.
4. Validate it in the JSON formatter if needed, and convert numeric and date fields in your own code.

From JSON to a table

Going the other way, the real question is what happens to nested data. The converter flattens nested objects into dotted column names and writes arrays into a single cell as JSON text. Take this input:

[{"id":1,"name":"Ana","address":{"city":"Leeds","zip":"LS1"},"tags":["vip","new"]},{"id":2,"name":"Ben","address":{"city":"York"},"tags":[]}]

The output is:

id,name,address.city,address.zip,tags
1,Ana,Leeds,LS1,"[""vip"",""new""]"
2,Ben,York,,[]

Ben has no ZIP code, so that cell is empty; the column set is the union of fields across all records. The tags cell is quoted under RFC 4180 because it contains commas and quotes. If you need one row per tag, split the tags into a separate table before converting; there is no lossless way to do it inside a single flat table.

CSV vs JSON: which one to choose

• Choose CSV when the data is naturally tabular, will be opened in a spreadsheet, is headed for a bulk database import, or is too large to handle as one document.
• Choose JSON when the data is nested (orders with line items, users with addresses), when types must survive the trip, or when it is an API payload or a configuration file.
• Consider JSON Lines, one independent object per line, when you want JSON's types with the line-by-line processing that tables offer. It is common for logs and large exports.
• Use both where it helps: a table for the people who live in spreadsheets, JSON for system-to-system exchange. Many projects find that the path of least friction.

Frequently asked questions

Why does my CSV open in a single column in Excel?

The delimiter Excel expects does not match the one in the file. On machines with a decimal-comma locale, Excel expects semicolons, so a comma-separated file stays in one column. Choosing the delimiter manually in the From Text/CSV import fixes it.

Can Excel open a JSON file directly?

Current Excel versions for Windows can import JSON through Power Query on the Data tab, but nested structures need extra steps. For a flat list of records, converting to a table first is usually faster.

How do I keep numbers and dates intact?

Since the file itself carries no types, protection happens in the program that opens it. Mark sensitive columns as Text during import, and write dates in ISO 8601 form such as 2026-10-13, which most programs interpret correctly regardless of locale.

Which one is smaller?

For flat data, the table, because field names appear only once. In the example above the same records took 48 bytes as CSV and 111 as compact JSON. Compression such as gzip handles the repeated field names well, so the difference over the network is smaller.

For the JSON side in more depth, working with JSON data continues where this article stops. If your records need unique keys, read what a UUID is, and for binary content embedded inside text formats, see what Base64 is.