JSON to CSV without breaking Excel

The conversion is the easy half. An array of objects becomes a header row and a bunch of data rows, the keys become the columns, and any competent script does it before lunch. What actually decides whether the file is any use is what happens on the other side, when someone double-clicks it in Excel and starts reading. That is where the damage gets done, and the damage is almost never caused by the converter being wrong. It is caused by the format being asked to carry information it has no way to record.

I want to go through the failure modes in the order I meet them, which is roughly the order of how often they bite.

Leading zeros and the number-shaped string

This is the one that starts arguments, because the data is arguably at fault. Consider a customer table where the account code for one branch is 007. In JSON that value might be the string "007", in which case the converter writes 007 into the file, and then Excel reads the file, decides the cell looks like a number, and displays 7. The leading zeros are gone. Nobody edited anything; the parse did it.

The same thing happens more dramatically with long digit strings. A phone number like 0412345678 is still a plain number to Excel and survives, but a sixteen digit identifier does not. Excel's numeric type carries fifteen significant decimal digits. Past that, the value is stored as a double and the last digits are lost or rounded, and long enough strings get displayed in scientific notation instead. Paste 1234567890123456 into a general cell and you get 1.23457E+15, with the trailing 6 unrecoverable because it was never stored in the first place. This is not a display setting. The data underneath the display is a float.

The fix people reach for is a leading apostrophe, and it works, but it works by lying. Typing '007 tells Excel "the rest of this is text" and the apostrophe is not part of the value. There is no equivalent in a CSV file. A CSV field is just characters between separators, and the file format has no column types at all: there is no way for the file to say that column three is an identifier that happens to be made of digits. Some converters will emit ="007" in a cell, which is a formula that evaluates to the text 007 and therefore displays correctly. It also means the column now contains formulas, which is fine right up until someone opens it in anything that is not Excel, and it is a well-known route for spreadsheet formula injection if the source data is untrusted.

So the honest framing is a choice, not a fix. You are picking between a file that behaves well when Excel has a go at it and a file that is faithful to what the data actually said. Quoting every field is not enough on its own — Excel will still convert a quoted numeric-looking field to a number on import. The only dependable option for a code like 007 is to not let Excel guess: import through Data, From Text, and set the column type to Text on the preview screen. If the file has to be double-clickable, then the column has to be shaped so it cannot be read as a number.

Separators, quotes and the newline problem

The second family of failures is pure syntax, and it is the one where the converter itself is genuinely responsible. A CSV field containing a comma has to be wrapped in double quotes, or the parser sees two fields where there was one. A field containing a double quote has to have that quote doubled — He said "hi" is written as "He said ""hi""" — and a parser that does not implement that rule will produce garbage on the very first record that contains a quotation mark. An address like 12 Rue de l'Église, Apt 3 hits the first rule; a product description with an inch mark in it hits the second.

The one that catches people out is the newline. RFC 4180, the document that describes what CSV actually is, permits a line break inside a quoted field. A field spanning two lines is legal. A parser written by hand — split on commas, split on newlines — will treat that one record as two, and every row after it shifts by one column for the rest of the file. I have spent more time than I would like debugging exactly this, and the symptom is always a column of names that starts halfway down the sheet. If your JSON has a notes or description field, assume it contains a newline and assume your parser does not handle it.

The byte order mark, and why an accent goes wrong

Encoding does not usually hurt anyone until a name with a diacritic shows up. Excel's older CSV import path assumes the system codepage when it cannot tell what the file is, and on a Windows machine that is often Windows-1252. A UTF-8 encoded José is the bytes 4A 6F 73 C3 A9. Read those five bytes as Windows-1252 and the last two become é, so the cell says José. That string is called mojibake and there is no way to un-see it once it is in a report.

The fix is the UTF-8 byte order mark: three bytes, EF BB BF, written before the first character of the file. Excel sees them, concludes the file is UTF-8, and decodes accordingly. It is a hack with real costs. Command-line tools that read the file will hand the three bytes to your application as part of the first field, so a header that should read id arrives as id — the same word with an invisible character in front of it, which breaks every string comparison against it. JSON does not permit a leading BOM in most parsers, and CSV is the only format where adding one is safe. That is why every converter has a BOM toggle and why the correct answer depends entirely on who is going to open the file.

What the damage looks like together

Here is the reference I keep. Left to right: what goes wrong, what causes it, and what actually stops it.

Every row above shares one theme: Excel or a parser is making a reasonable guess about a type that CSV never recorded. The format has no schema, so the guessing is not a bug in the importer. It is the format working as designed.

Flat is a lie: JSON is a tree, CSV is a table

Everything above is recoverable. This is the part that is not, because it is not damage at all — it is a mismatch of shape, and one of the two sides has to give. CSV has two dimensions: rows and columns. JSON has as many as you want. Nested objects, arrays of objects, arrays of scalars, keys that only some records have. The moment a record contains anything below the first level, the table cannot hold it without a decision being made, and that decision is the whole job.

Take a real order feed. This is the shape I get on about half the projects I touch:

[
  {
    "orderId": "A-1043",
    "customer": {
      "name": "José Ramírez",
      "account": "007"
    },
    "total": 149.5,
    "items": [
      { "sku": "BOLT-M8", "qty": 100 },
      { "sku": "NUT-M8",  "qty": 100 }
    ]
  }
]

The traps are already waiting inside that object. customer is an object with two keys. items is an array whose length varies per order. And account is the leading-zero problem from earlier, sitting inside the nesting.

Flatten it with dotted paths and the columns name themselves by where the value came from:

orderId,customer.name,customer.account,total,items.0.sku,items.0.qty,items.1.sku,items.1.qty
A-1043,José Ramírez,007,149.5,BOLT-M8,100,NUT-M8,100

That file is faithful, parseable and completely hostile to read. The number of columns depends on the longest array in the whole dataset, so one order with forty items gives you eighty item columns and eighty empty cells on every other row. It is also fragile: add a third item and the header changes. But if the destination is a script rather than a person, this is often exactly the right output, because the shape is predictable in code even when it looks awful in a spreadsheet.

The alternative for arrays is to serialise them: keep one row per order, and put the array in a single cell as a JSON string. You get a stable column count and a file a human can scan, at the cost of a cell containing [{"sku":"BOLT-M8","qty":100},...] that has to be double-quoted and comma-stuffed to survive the trip. It is a perfectly good answer when the array is a detail nobody queries, and a bad one when it is the point of the dataset.

The third option is to stop pretending one file is one table. Two CSVs — an orders file and an items file joined on orderId — is the normal relational answer, and it is what I recommend when both halves get analysed. It costs the reader a join, which is cheap in a database and mildly annoying in a spreadsheet, but it keeps every value in a real column with a real type and it does not fall apart when an order has nineteen items.

The last row is not a joke. The single most common mistake with JSON to CSV is doing it at all: flattening a structure that the receiving tool would have read directly, and losing the nesting in exchange for nothing.

When the array is not an array of objects

Every rule above assumed a list of objects. Large amounts of real JSON is not that. A geolocation endpoint hands you [[51.5074, -0.1278], [40.7128, -74.0060]]. A tag list is ["red", "green", "blue"]. A key-value export is a single object, not an array at all. Each of those needs a different decision, and a converter that assumes objects will produce one column called 0 and a lot of empty cells.

Arrays of arrays are positional by definition, so the header has to be positional too. If you cannot name the columns from the data, name them from what you know: the first field is a latitude and the second is a longitude, so write lat,lon as the header rather than 0,1. An array of scalars is one column, and if it is a list of tags you have a choice between joining them into one cell with a delimiter or exploding them into one row per tag — the second is almost always better if anyone will ever count them. A bare object is a one-row table, which is legal but rarely what was wanted; if you were expecting an array, the bug is upstream and you should fix it there rather than converting the wrong thing carefully.

There is a diagnostic step worth doing before any conversion: look at the actual structure, not the structure you remember. Whether the top level is an array or an object, whether the keys are consistent across records, and where the nesting really is — all of that changes which of these cases you are in, and guessing at it and then debugging the CSV is the slow way round.

The rule that survives all of this

Before you convert, classify every column of the source as either a measurement or a name. Numbers that get added, averaged or plotted are measurements. Everything else is a name, including things that look like numbers: account codes, phone numbers, postcodes, card identifiers, version strings, and anything with a leading zero. Measurements want to arrive in Excel as numbers. Names want to arrive as text, and the only reliable way to make that happen is to set the type on import, because the file cannot carry the instruction.

The check to run on any CSV you are about to send someone is short. Open it in a text editor, not a spreadsheet, and read the raw text rather than the rendered table. Does the header have a strange character before the first name, which means the BOM will leak into somebody's code. Are there fields with commas or quotes in them that are not quoted. Does any row have a different number of separators from the row above it, which means a newline is loose inside a field. Those checks take about a minute by eye and catch nearly everything on the list above.

The deeper lesson is that CSV is a transport format with no memory. It can carry values and it cannot carry meaning, so every type in your data is re-derived by whoever opens the file, using whatever rules their tool prefers. JSON keeps the distinction between "007" and 7; CSV cannot, because the quotes around a CSV field are syntax, not a type declaration. If you need the distinction to survive the trip and a human has to read the result, name the file so the recipient knows to import it rather than open it, and say which columns are text. That sentence, written in an email, is worth more than any converter setting.