Converting JSON to CSV correctly
JSON is a tree and CSV is a flat grid, so every conversion makes decisions: which columns to create, what to do with nested objects and lists, and how to escape commas inside values. Get the quoting wrong and a spreadsheet splits one name across two columns.
Convert a JSON array to CSV
Step 1: decide the columns
CSV needs one header row, but JSON objects in the same array often have different keys. A safe converter takes the union of all keys, in the order it first sees them, and leaves a cell empty where an object lacks that key. In the sample, the first object has id, name, city, tags and the second adds note, so the header lists all five and the first row ends with an empty note cell.
Converters that read only the first object’s keys silently drop any field that appears later. Check the column count against the data before you trust an export.
Step 2: quote values the RFC 4180 way
RFC 4180 is the closest thing CSV has to a standard. Its quoting rules are short:
- If a value contains a comma, a double quote, or a line break, wrap the whole value in double quotes.
- Inside a quoted value, write each double quote twice.
- Separate records with a carriage return and line feed (CRLF).
| Value | CSV cell |
|---|---|
Lee, Sam | "Lee, Sam" |
Ana "Ace" Diaz | "Ana ""Ace"" Diaz" |
| A two-line address | The whole value in quotes, line break kept inside |
null or missing | Empty cell |
Escaping a quote with a backslash, as in \", is a JSON habit. Most CSV readers do not understand it.
Step 3: handle nesting
There are three common choices for a nested object such as "city": {"name": "Leeds", "zip": "LS1"}:
- Flatten with dotted names: columns
city.nameandcity.zip. Best for spreadsheets. This is the tool’s default. - Keep it as a JSON string inside one cell. Nothing is lost, and another program can parse it back.
- Split into a second CSV file linked by an id. This is the database approach and the right one for long lists.
Arrays such as "tags": ["a", "b"] have no clean column form, because each row may hold a different number of items. The tool writes them as a JSON string; for analysis, consider one row per tag in a separate table.
Step 4: guard against formula injection
Spreadsheet programs treat a cell that starts with =, +, - or @ as a formula. If exported data comes from users, a value such as =SUM(A1:A9) runs when someone opens the file, and crafted formulas can do worse. The usual defence, recommended by the OWASP security project, is to prefix such text values with a single quote so the spreadsheet shows them as text. The tool does this only for strings, so a real number such as −5 stays a number.
Common mistakes
- Building CSV with string joins.
values.join(",")breaks on the first comma inside a value. Always quote. - Ignoring the delimiter of the reader. In locales that use a comma as the decimal mark, spreadsheets often expect semicolons. Check the import settings.
- Losing leading zeros. A zip code such as
02134becomes2134when a spreadsheet guesses it is a number. Import that column as text. - Character encoding. Save as UTF-8. Some spreadsheet programs only detect UTF-8 when the file starts with a byte order mark.
Questions
Can I convert CSV back to JSON?
Yes, but types are lost: CSV has no difference between the number 7 and the text “7”, and an empty cell could mean null, missing or an empty string. Keep a note of each column’s type.
Is my data uploaded?
No. The conversion runs in your browser and nothing is stored.