CSV and JSON Conversion, Explained

After reading this you will know exactly how a row of CSV becomes a JSON object, how types are guessed, how nested objects flatten back into columns, and which edge cases silently corrupt data if you are not watching.

What the two formats actually store

CSV and JSON both hold tabular-ish data, but they disagree about structure and types. CSV is a grid of text. Every cell is a string until something decides otherwise. JSON is a tree with named fields and real types: numbers, booleans, null, strings, and nested objects or arrays.

Take one record. In CSV it looks like this, with a header row and one data row:

name,age,active
Ada,36,true

The same record as a JSON array of objects looks like this:

[{"name":"Ada","age":36,"active":true}]

Notice what changed. In the CSV, 36 and true are the three characters 3, 6 and the five characters t, r, u, e. In the JSON, 36 is a number and true is a boolean. Nothing in the CSV forced that. The converter chose to promote them. That single choice, promote or keep as string, is the source of most conversion surprises.

When to convert, and when not to

Convert CSV to JSON when a program expects records with typed fields: a REST API body, a config file, a JavaScript array you will iterate. JSON keeps numbers as numbers, so age > 30 works without a parse step.

Convert JSON to CSV when you want to open data in a spreadsheet, diff it line by line, or feed a tool that reads rows. CSV is smaller for wide flat tables because it writes each field name once in the header instead of on every record.

How type detection decides

There is no formula with Greek letters here, but there is a precise rule, and precision is what saves you. A cell is promoted from string to a typed value only if its entire text matches one of a small set of patterns. A partial match stays a string.

The usual rules, checked in order:

Boolean

The exact text true or false (and often TRUE/FALSE) becomes a JSON boolean. yes, 1, and T do not.

Number

Text that parses fully as a number becomes a JSON number: 36, -4.5, 1e3. A trailing space or a stray letter cancels it.

Null

An empty cell may become null, or stay an empty string "", depending on the setting. These are not the same value.

String

Everything else, including 007 if the tool preserves leading zeros, and anything with quotes or letters.

The leading-zero case matters. 007 as a number is 7, and the zeros are gone forever. Phone numbers, zip codes and product IDs are the classic victims. If a column is an identifier rather than a quantity, keep it as a string.

A worked example with the demo data

Load the demo data and you get a small CSV like this, three fields and two rows:

name,age,active
Ada,36,true
Linus,54,false

Now the reverse. Feed that JSON array back in and ask for CSV. The converter collects the union of keys across all objects to build the header, then writes one row per object. With identical keys in both records you get back exactly the original three-column CSV. The 36 is written as the text 36 again, so a clean round trip returns to where you started.

Flattening nested objects into columns

CSV has no place for a value inside a value. So a nested JSON object is flattened into dotted column names. Consider this record:

{"name":"Ada","address":{"city":"London","zip":"NW1"}}

As CSV it becomes three columns, with the nested keys joined by dots:

The dotted path is a convention, not a standard. When you convert that CSV back to JSON, a good converter reads address.city and rebuilds the nested object. But if a real key in your data literally contains a dot, the rebuild guesses wrong and splits it. Watch for that.

Arrays are harder. An array of objects with different lengths cannot flatten cleanly into fixed columns. Some tools emit tags.0, tags.1 and leave gaps; others serialize the whole array into one cell as JSON text. Check which your data got, because the two shapes read back differently.

Reading the output and checking it

After any conversion, verify three things by eye before trusting the file.

1. Row count. A CSV with a header and 100 data lines should give a JSON array of length 100. If you got 99, a line without a trailing newline or an embedded newline inside a quoted field probably merged two rows.
2. Types. Open the JSON and confirm that numbers appear without quotes and identifiers appear with quotes. If your zip codes lost their leading zeros, type detection was too eager.
3. Column count on the way back. Extra columns in JSON to CSV usually mean records had inconsistent keys and the union grew.

Try the type-detection switch yourself

Common mistakes

Wrong delimiter. If your file uses semicolons but the converter splits on commas, every row becomes a single wide column. European exports often use ; because the comma is the decimal separator there. Set the delimiter to match the file, not your habit.

Unquoted commas inside fields. A cell like Smith, Ada must be quoted as "Smith, Ada", or it splits into two columns. This is a property of the source file. If the file is malformed, no converter can guess the intent.

Assuming numbers survive. A number written as 1e3 in CSV becomes 1000 in JSON. A number with a trailing space stays a string. Neither is a bug; both surprise people.

Confusing empty with null. An empty cell can become "" or null. Downstream code often treats these differently, so decide deliberately.

Encoding. If names with accents show up as garbled bytes, the file is probably not UTF-8. Fix the encoding at the source before converting.

Related tools

To inspect or clean a CSV before converting, use the CSV Viewer & Editor to sort, filter and drop columns. If your data starts in a spreadsheet, run the Excel → CSV Converter first, one sheet at a time. To mask names or IDs before sharing a file, the Data Anonymizer hashes or fakes selected columns. And to look inside columnar files without writing Python, try the Parquet & Feather Preview.

Related tools

Auto Analyst: Instant Statistical Report

Drop a CSV or Excel file and get a statistician’s report: data problems, distributions, every real relationship, key drivers and Simpson’s paradox warnings, in plain English.

CSV Viewer & Editor

View, edit, sort, filter and extract columns from CSV files.

Data Visualizer

29 chart types — heatmap, treemap, dumbbell, slope, candlestick — with rich styling and annotations.

Excel → CSV Converter

Convert XLSX/XLS sheets to clean CSV, per sheet.

JSON Table Viewer

Paste JSON, get a sortable, searchable table.

Markdown Table Generator

Paste spreadsheet data, get an aligned Markdown table.