SpunkArt
Data, text and encoding

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:

  1. If a value contains a comma, a double quote, or a line break, wrap the whole value in double quotes.
  2. Inside a quoted value, write each double quote twice.
  3. Separate records with a carriage return and line feed (CRLF).
ValueCSV cell
Lee, Sam"Lee, Sam"
Ana "Ace" Diaz"Ana ""Ace"" Diaz"
A two-line addressThe whole value in quotes, line break kept inside
null or missingEmpty 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.name and city.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 02134 becomes 2134 when 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.