tooldura
Developer Tools

From JSON to CSV: Nested Objects, Arrays and the Excel Problem

T
tooldura editorial
9 min readUpdated November 19, 2026Open tool →

JSON is a tree and CSV is a grid, so every conversion between them is a set of decisions about what to do with the parts of the tree that do not fit. Most of those decisions are easy to make once you see them. The harder part comes after the file is written, when a spreadsheet opens a perfectly correct CSV and quietly changes what is in it. This guide covers both halves.

What a CSV File Is Allowed to Contain

CSV was in use for decades before anyone wrote it down. The description most tools follow is RFC 4180, published in October 2005. It is an Informational RFC, not a standard, and it describes common practice rather than enforcing it. Its rules are short.

Each record sits on its own line, ended by CRLF, and the last line may or may not have a line break. An optional header line comes first. Spaces are part of a field and should not be trimmed. A field that contains a comma, a double quote or a line break should be enclosed in double quotes, and a double quote inside such a field is written twice.

That is the whole format. There is no way to say that a column holds numbers, that a cell is empty rather than an empty string, or that a value is a list. Everything is text, and every type that JSON carries has to be written as text and guessed back later.

How Values Are Written

Comma as the delimiter. With a semicolon, the semicolon is what forces quotes and a plain comma does not.

JSON valueCSV cellWhy
"Oslo"OsloNothing special, no quotes needed
"Bo, Jr.""Bo, Jr."Contains the delimiter
"5\" screen""5"" screen"Quote doubled, then the field quoted
"two\nlines""two↵lines"A real line break, kept inside the quotes
null(empty)CSV has no null, an empty cell is the closest
truetrueWritten as the word
["a","b"]"[""a"",""b""]"An array kept as JSON text in one cell

Nested Objects Become Dotted Columns

A record like {"id": 1, "user": {"name": "Ana", "address": {"city": "Oslo"}}} has three values in it, at different depths. The usual way to fit it into a grid is to name each value by its path: id, user.name and user.address.city. Each path becomes a column, and the row holds the values.

This works well because it is reversible. A reader that knows the convention can split the header on dots and rebuild the original object, which is what the CSV to JSON converter's nested option does. It breaks down in two places. A key that already contains a dot, such as "v1.2", cannot be told apart from a path. And a record that holds both a user value and a user object cannot exist in JSON in the first place, but two different records can disagree, one with "user": "ana" and another with "user": {"name": "Ana"}, which gives a user column and a user.name column side by side.

The alternative is to keep each nested object as JSON text in a single cell. The table stays narrow and nothing is lost, but the inner values are no longer something a spreadsheet can sort or filter on.

Three Ways to Handle an Array

Arrays are the real problem, because a record can have a different number of items in each one. Every converter picks one of three answers.

1

Keep it as JSON text in one cell

["a","b"] goes into the cell as written. One record stays one row, and the value can be parsed back exactly. This is what the converter on this site does, because it is the only choice that never changes the shape of the table.

2

Spread it across numbered columns

tags.0, tags.1, tags.2 and so on. Fine for short arrays of fixed length, such as coordinates. For anything that varies, the width of the table is set by the longest array in the file, and most rows end up mostly empty.

3

Repeat the row once per item

An order with three line items becomes three rows that share the order fields. This is the right shape for analysis, and the wrong one for anything else: the row count no longer matches the record count, and two arrays in the same record multiply each other.

Columns Come From Every Record

JSON records do not have to share keys, and real API responses often leave out a key when the value is missing. A converter that takes its columns from the first record silently drops every key that first appears later. The safe approach is to scan all records, collect every key in the order it first appears, and leave a cell empty where a record lacks that key.

The empty cell carries less information than the JSON did. A missing key, null and an empty string "" all become the same empty field. If that difference matters to whoever reads the file, CSV is the wrong format for the job.

Convert JSON with the decisions made visible

Dotted columns or JSON cells, a table preview, and notes when the input needed interpreting.

Open JSON to CSV Converter →

When Excel Opens the File

Most CSV files end up in Excel, and Excel applies its own rules on the way in. None of them are bugs in the file, but all of them change what you see.

Encoding. Microsoft's guidance is that a UTF-8 CSV opens correctly when it starts with a byte order mark (BOM). Without one, Excel reads it in the computer's legacy code page and accented letters turn into pairs of odd characters. Adding the three BOM bytes fixes Excel, but other programs may then read them as part of the first column name, so add it only to files meant for Excel.

Delimiter. Excel splits a CSV on the list separator from the system's regional settings. In countries where the comma is the decimal separator, that is usually a semicolon, and a comma-separated file opens with every value in column A. Writing the file with semicolons, or importing it through Data > From Text/CSV and choosing the delimiter, solves it.

Long numbers. Excel stores numbers as IEEE 754 doubles and, by Microsoft's own documentation, changes any digits after the fifteenth to zeros. A 16-digit card number or an 18-digit ID is damaged as soon as the file opens, and saving it again makes the damage permanent.

Leading zeros. A column of ZIP codes or product codes such as 00742 is read as numbers, and the zeros disappear. Microsoft's fix for both of these is the same: import the column as Text instead of opening the file directly.

🧬

The genes that were renamed for Excel

Excel also turns text that looks like a date into a date. Gene symbols such as SEPT1 and MARCH1 became 1-Sep and 1-Mar in shared research spreadsheets, and a 2016 study of supplementary files from 3,597 papers found these errors in about one in five. In 2020 the HUGO Gene Nomenclature Committee changed 27 symbols to stop it happening, so SEPT1 is now SEPTIN1 and MARCH1 is now MARCHF1.

Cells That Start With an Equals Sign

A spreadsheet treats a cell that starts with = as a formula, and a few other first characters can do the same. OWASP lists =, +, -, @, tab and carriage return as the risky ones, and describes CSV injection: data from an untrusted source, written to a CSV, runs as a formula when someone opens the file.

The defences change the data, which is why a general converter should not apply them silently. OWASP's suggestion is to make sure no cell begins with one of those characters, for example by prepending a single quote. That protects the person opening the file and breaks any program that expects the original value, such as a phone number that starts with +. If you export user-supplied data that people will open in Excel, decide which of those two matters more for that file.

Frequently Asked Questions

Found this guide useful? Pass it on.

Related Tools

Keep Reading