CSV to JSON conversion: the details that break it

CSV is flat by definition: a grid of rows and columns with no nesting, no types, and no self-description beyond an optional header row. Every value is text until something interprets it. JSON is the opposite — a tree of typed values where objects nest inside arrays inside objects, and a number, a string, a boolean, and null are four different things.

Conversion is therefore a translation between philosophies, not just syntaxes. Going CSV to JSON, the converter must invent structure: decide that the header row supplies keys, and decide what type each cell should become. Going JSON to CSV, it must destroy structure: flatten nested objects into column names and force every typed value back into text. Understanding that asymmetry explains almost every surprise you will meet in practice.

It also explains when each format wins. CSV opens directly in Excel and Google Sheets, streams line by line through tools that never load the whole file, and stays compact for tabular data. JSON carries structure and types faithfully and is what nearly every modern API speaks. Data teams routinely live in both, converting at the boundary.

The naive approach — split each line on commas — fails the moment a value contains a comma, and real data is full of them: addresses, company names, free-text notes. The CSV rules in RFC 4180 exist for exactly this. A field containing a comma, a double quote, or a line break must be wrapped in double quotes, and a literal double quote inside a quoted field is written as two double quotes in a row.

So a compliant parser cannot split lines at all. It walks the file character by character with a small state machine: outside quotes, a comma ends the field and a newline ends the record; inside quotes, commas and newlines are ordinary characters, and a lone double quote flips the state back while a doubled quote emits one literal quote. This is also why a quoted field can legally contain a line break — one record can span multiple physical lines, which breaks every line-count assumption a naive script makes.

Delimiters are the next layer. In much of Europe the decimal separator is a comma, so Excel in German, French, and many other locales writes CSV files delimited by semicolons instead. Tab-delimited files are common from database dumps, and pipes show up in legacy feeds. A good converter either detects the delimiter by sampling the first rows or lets you set it explicitly. Finally comes type inference: since CSV stores only text, the converter inspects each value and decides whether 42 becomes the number 42, TRUE becomes a boolean, and an empty cell becomes null or an empty string. Inference is what makes the JSON output immediately usable — and it is also the source of the most dangerous silent errors, which the next section covers.

These are the failure modes that show up over and over in production data work. Scan your output for them before shipping it anywhere:

The reverse conversion only works cleanly on one shape: an array of objects with scalar values. Each object becomes a row and the union of all keys becomes the header. Objects missing a key get an empty cell — order data where only some records have a discount field converts fine, with blanks where the field is absent.

Nesting is where decisions start. A nested object like an address inside a customer is usually flattened with dotted or underscored column names — address.city becomes a column of its own. Arrays are worse: three phone numbers per contact either explode into phone_1, phone_2, phone_3 columns, get joined into one delimited cell, or force the whole export into multiple rows per record. None of these options is wrong, but each one changes what the spreadsheet consumer has to do with the file, so pick deliberately rather than accepting whatever default appears.

Escaping on output mirrors parsing on input: any value containing a comma, quote, or newline must be quoted, with internal quotes doubled. This is also the moment to think about the audience. If the file is destined for Excel in a European locale, a semicolon-delimited export will open correctly where a comma-delimited one lands in a single column. A file destined for a data pipeline should stay comma-delimited and RFC 4180 clean.

The CSV-to-JSON direction dominates application development. A client exports contacts from Mailchimp or a product list from Shopify as CSV, and the web app you are building wants JSON to seed a database or feed an import endpoint. Charting is another regular: analytics platforms export CSV, while Chart.js and D3 want arrays of objects, so the converter sits between the export button and the visualization. Frontend developers also use it to fabricate realistic mock API data from a spreadsheet a stakeholder already maintains — fifty product rows become a fixtures file in seconds.

The JSON-to-CSV direction dominates reporting. An API returns usage records or a MongoDB collection exports as JSON, and the person who needs the data lives in Excel. Flatten it to CSV and the conversation moves from an engineering ticket to a pivot table. Support and operations teams do this constantly with webhook logs and audit exports.

Whichever direction you convert, verify before you trust. Check that the output row or object count matches the input, spot-check the first, last, and one middle record field by field, and look specifically at the columns prone to inference damage — anything ID-like, anything date-like, anything with leading zeros. A thirty-second check catches the misaligned-row and mangled-type problems that otherwise surface days later inside another system.