Convert CSV to JSON

A cell has no type. A JSON value has five.

Everything in a spreadsheet export is characters. The row 01234,41,TRUE, holds four cells and not one of them announces what it is supposed to be. JSON, on the other end, insists: a value is a string, a number, a boolean, null, or a nested structure, and the choice is written into the punctuation. Converting one into the other means answering a question the source file never asked, once for every cell.

That is where these tools differ from each other, and it is the only place they can. Reading commas is solved. Deciding that 41 is a quantity while 01234 is a postal code is not, and a converter that answers it silently hands you a file whose damage shows up two weeks later in someone else’s import log. This page puts every one of those answers on screen, on rows from your own file, before there is anything to download.

CSV and JSON, in brief

CSV

Full name	Comma-Separated Values
Extension	.csv
Format type	Plain text table, one record per line
MIME type	text/csv

JSON

Full name	JavaScript Object Notation
Extension	.json
Format type	Nested text data, six value kinds
MIME type	application/json

One type per column, or the column is text

Guessing per cell is the common approach and it produces output that looks correct in a preview: five hundred records where qty is a number, and three where it is the string "N/A" because somebody typed that in April. Nothing is wrong with the JSON. Everything is wrong downstream — a warehouse loader, a typed struct, a schema validator and a dataframe all want a field to mean one thing, and each of them fails at a different point with a different message.

So a column here is typed only when every non-empty cell agrees. A single disagreement makes the whole column text, and the value responsible is quoted back to you, so the decision is one you can inspect rather than one you have to reverse-engineer. Blank cells never count as disagreement; they are absence, not a conflicting opinion. When the guess is not what you wanted, each column carries its own selector — auto, text, number, boolean — and the sample line under it updates the moment you change it.

Zeros at the front, and digits past the fifteenth

Two number problems account for most of the corruption in this direction, and both of them are invisible until much later.

The first is the leading zero. 01234 is a ZIP code, an account reference, a product code or a phone number, and its first character carries meaning. Read as a number it becomes 1234 and there is no way back. Any column with a leading zero anywhere in it therefore stays text here. If you happen to know the column really is numeric, force it and you get the number — the point is that nobody does it on your behalf. A GTIN is one such identifier: keep it as text here, and hand the intact twelve digits to the UPC generator only when you need the barcode image. When the field is an internal ID rather than a retail GTIN, the barcode generator keeps its exact letters and digits in a Code 128 symbol.

The second is quieter. JavaScript holds roughly fifteen significant digits, so the nineteen-digit identifiers that Snowflake, Twitter and many order systems hand out do not survive being read as numbers: 12345678901234567890 comes back as 12345678901234567000. This tool detects that a value cannot make the trip intact, writes it as a string, and shows you the number it refused to produce. It is the same promise the JSON to CSV converter makes traveling the other way, and the two pages are deliberately consistent about it, because a round trip that changes your identifiers is worse than one that fails.

Three shapes, and the one your pipeline actually wants

An array of objects is the default and the right answer when a person or an application will read the whole thing at once. It is also the wrong answer for a data pipeline, because a reader cannot process record one until the closing bracket has arrived.

NDJSON — one object per line, no wrapping array, no commas between records — is what log collectors, BigQuery loads and batch APIs expect, and it streams: a million records can be read line by line with constant memory, and a truncated file still yields every complete record before the break. The keyed object is the third form, turning the file into a lookup table on a column you choose. It is the only shape that can lose a record, since two rows sharing that value cannot both survive as keys, so the collision is counted and the offending value is named rather than left for you to discover. The record count next to the result is the number of records the file you are about to download actually contains, which in that shape is the number of distinct keys and not the number of rows that went in. Two hundred rows on fifty keys is fifty records, and saying two hundred would have been a promise the download could not keep.

What CSV to JSON does with a broken file

Real exports arrive damaged in a small number of recognizable ways, and all of them are survivable if the tool says what it did. Two columns headed the same thing cannot both be called that in an object, so the second is numbered. A blank header still names a real column full of values, so it is named after its position instead of vanishing. Rows that carry more fields than the header names — usually an unescaped quote or separator inside a value — keep their surplus under numbered keys rather than losing it, and rows that stop early are padded rather than shifted left, which is the difference between a gap and a file where every value after column three belongs to the wrong field.

A quotation mark that is opened and never closed is the one damage that looks like success. Every line after it is inside that value as far as any parser is concerned, so a file of a thousand rows converts to one record and nothing about the output says why. Here the opening mark is reported with the line and column it sits on and the first of the text it swallowed, because that position is the single edit that fixes the file. The rows are not guessed back into place: where the quoting is ambiguous, a parser that repairs it is a parser that decides on your behalf which of your commas were separators.

Each of those messages quotes the text it found rather than citing a row number. That is deliberate. Our grid has none of the blank rows a spreadsheet draws, so what we would call row two is often your row three, and a number you cannot check against your own screen is worse than no number at all. Text you can search for. If you would rather look at the grid first, the CSV viewer opens the same file as a sortable table and flags the same hazards.

Three things only the file itself can tell you

Paste a table and it converts exactly the same way. But the paste box cannot report on encoding, on a byte order mark, or on line endings, and this is not a limitation we chose. A browser text area rewrites every CRLF to LF as it accepts the text, and reading a file as a string quietly swallows a byte order mark; by the time any code here could look, both facts are gone. So a dropped file is read as raw bytes and decoded here instead, which is why the encoding control and those three readings appear only in that mode. A carriage return inside a quoted value is real data, it ends up in your JSON as \r\n, and you deserve to know that before you diff the result against something else.

A carriage return that is neither part of a CRLF nor inside quotes has a stated fate rather than a quiet one. In a file whose lines end with a line feed, a bare carriage return is a character in your data, so it stays exactly where it is and is reported with its line and column; deleting it would have joined the text on either side of it into one word without saying so. In a file that has no line feed anywhere — what Excel for Mac wrote for years — the carriage return is the line ending, and it is read as one, which is the difference between a table and a single row holding the entire export.

The encoding itself is the older half of the same problem. A file written as UTF-8 and read as Windows-1252 turns Malmö into Malmö, and once that has happened the mangled characters are what your JSON will contain. Detection runs first and the override exists because bytes written in Latin-1 can pass as UTF-8 purely by chance, and in that one situation nothing but your own eyes can settle it. The check on the result runs on whichever encoding is selected, including one you picked yourself: a warning that switched itself off the moment you overrode the detection would have been silent in precisely the case where the choice can be wrong. Where the bytes cannot be that encoding at all — an odd number of them for UTF-16, or none of the zero bytes UTF-16 text is half made of — the file says so instead of the rows simply disappearing. And a file that is not text at all is named for what its first bytes are, a PNG or a SQLite database or a zip archive, rather than being called a workbook on the strength of the commonest case. If the file started life as a workbook, Excel to CSV is a cleaner first step than whatever Excel’s own save dialog produced, and the guide to the CSV format explains why the format has no standard to appeal to in the first place.

Where the work happens

Conversion runs in this page. The parsing, the type decisions and the writing are function calls in your own browser, there is no endpoint here that accepts a file, and a document larger than half a megabyte moves to a background thread so the tab stays responsive rather than freezing. Several other CSV to JSON converters work this way too, and say so honestly — the difference worth choosing between is not where the code runs but whether the file tells you what it decided. Watch the network panel while you drop a file if you want the first part confirmed; read the column list if you want the second. When the JSON that comes back needs inspecting or reindenting, the JSON formatter takes it from here.

Types are also the reason this page and the CSV to XML converter are two pages rather than one output setting. XML has no types at all — everything inside an element is character data, so 41 and "41" are the same thing there and none of the decisions above exist. What it has instead is a grammar for names: a column called First Name is a perfectly good JSON key and not a legal XML element name, so that page spends its screen space on what happens to your headers rather than to your values.