Turning an array of JSON objects into a grid looks like a job with no edge cases. It has four, and three of them corrupt data quietly rather than failing loudly. Toolsyy’s JSON to CSV converter runs in your browser as you type — this is what it is doing about each of them.
The one that is a security problem
Start with the strangest behaviour, because it looks like a bug until you know what it prevents. Paste this:
[ { "name": "=1+1", "source": "pasted from a web form" } ]and the cell comes out with an apostrophe in front of it:
"name","source"
"'=1+1","pasted from a web form"Here is why. Excel and Google Sheets decide whether a cell is text or a
formula by looking at its first character. A leading =,
+, - or @ means formula, and the
application evaluates it the moment the file is opened. The CSV itself is
inert; the spreadsheet is what makes it dangerous.
So imagine the JSON came from an API backed by a public signup form, and somebody entered a formula as their name. You export the user list, open it in Excel to skim it, and the formula runs with whatever access you have. Depending on the function it can read other cells and exfiltrate them through a crafted link, or prompt you to enable something worse. This is CSV injection, and it is a well-documented attack against the person opening the export rather than against the site that produced it.
Prefixing an apostrophe is the standard defence: spreadsheets read it as
“treat the rest as literal text” and do not display it. The cell shows
=1+1 and stays inert.
One deliberate subtlety in how the check is applied. A leading minus is
only escaped when the value is not a valid number, so a genuine
negative like -5 stays numeric and still sums correctly, while
-notanumber gets the guard. Escaping every minus would have
broken arithmetic on every export containing a negative figure.
The header has to come from every object
The obvious way to build a header row is to read the keys of the first object. It is also wrong the moment your records are not uniform, which for real API data is most of the time:
[
{ "sku": "A-100", "qty": 3 },
{ "sku": "B-200", "qty": 1, "note": "gift wrap" }
]A first-object converter emits the header sku,qty and throws
the note away — or worse, writes three values under two headings and slides
the row out of alignment. Keys are instead collected across every object
into an ordered set, so the header is sku,qty,note and the
first record simply has an empty cell at the end.
"sku","qty","note"
"A-100","3",""
"B-200","1","gift wrap"Columns appear in the order they are first encountered, so the fields of the first record lead and later-only fields follow.
Quoting: commas, quotes and line breaks
CSV has a real specification, RFC 4180, and it defines exactly two things that matter here. A field containing a delimiter, a quote or a line break is wrapped in double quotes. A quote inside a quoted field is written twice.
That doubling is the part that is easy to get wrong, because the escaping
rule people reach for by reflex is the JSON one, where an internal quote
becomes \". The two look interchangeable and are not:
value: He said "hi"
correct: "He said ""hi"""
wrong: "He said \"hi\""A conforming parser reading the second form closes the field at the quote
after said \ and everything after it lands in the wrong place.
Every field is quoted here and internal quotes are doubled, so output from
this converter round-trips cleanly back through
CSV to JSON — including values that contain a
comma, a quote and an embedded newline at once.
Values that are not strings get sensible treatment on the way in. A
null becomes an empty cell. A nested object or array is
serialised as JSON text into the single cell, because a flat grid has
nowhere else to put it — if you need nested data as columns, flatten the
JSON before converting.
The invisible byte Excel needs
This one is pure Windows history. Save a UTF-8 CSV, open it in Excel by
double-clicking, and café arrives as café.
Nothing is wrong with your file. Excel simply does not assume a
.csv is UTF-8, and falls back to the machine’s legacy
codepage, which decodes the two bytes of an accented character as two
separate wrong ones.
The fix is a byte order mark — three bytes at the very start of the file that identify it as UTF-8. Excel checks for it, and with it present reads the file correctly. The downloaded file includes one.
Worth knowing: the marker is added when you download. If you use the copy button and paste into a new file yourself, it is not there, and accented text may come back mangled in Excel. Download the file when the data has anything outside plain ASCII in it.
Why it converts as you type
There is no convert button because the work is a JSON.parse
and a string join — microseconds, and completely deterministic. A button
would add a step without adding anything else. An empty box is treated as
the starting state rather than an error, so the tool stays quiet until
there is something to say.
Nothing is uploaded. The conversion happens in the page, which matters more than usual for this particular tool: the JSON you are pasting is frequently an export of real customer records, and the safest place for it is the machine it is already on.
FAQ
Why do some cells have an apostrophe in front of them?
Because a cell whose value begins with an equals sign, plus, minus or at sign is interpreted as a formula by Excel and Google Sheets rather than as text, and a value that arrived from an untrusted source could then run when somebody opens the file. A leading apostrophe marks the cell as literal text. Spreadsheets do not display it, and the value reads normally.
What is CSV injection?
It is an attack where hostile content is placed in a field that later ends up in an exported spreadsheet. The exporter treats it as ordinary text, but the spreadsheet application evaluates it as a formula the moment the file is opened, which can leak the contents of other cells or prompt the reader to allow something worse. It is a flaw in the export rather than in the spreadsheet, which is why the fix belongs in the converter.
What JSON shape does it need?
An array of objects, which is what an API returns for a list of records. Each object becomes a row and the keys become columns. A single object can be wrapped in square brackets to make a one-row table. Deeply nested objects have no natural representation in a flat grid, so a nested value is written into the cell as JSON text rather than expanded into extra columns.
What happens if some objects are missing a field?
The header is built from the keys found across every object rather than from the first one alone, so a field that appears in only some records still gets its own column. Records without it get an empty cell in that column instead of having their remaining values slide left into the wrong headings, which is the way naive converters corrupt data.
Why does Excel show my accented characters as gibberish?
Because Excel does not assume a plain CSV file is UTF-8, and reads it in the local Windows codepage instead, which turns an accented character into two wrong ones. The downloaded file therefore begins with a byte order mark, an invisible marker that tells Excel the file really is UTF-8. If you copy the output from the box rather than downloading it, that marker is not included.
Related guides
- How to convert CSV to JSON — the return trip, and the leading-zero trap that silently corrupts zip codes.
- How to format and validate JSON — for checking the input before converting it.
- Why word counters disagree — another case of the same text producing different numbers depending on the rule.
Try it now: Free JSON to CSV Converter