Convert JSON to CSV/Excel: A Practical Guide

The Running Example: Three API Orders

Let's work with something concrete. Say your orders API returns this (trimmed to three orders, each with a nested customer object and an array of items):

Each order has an `id`, a `total`, a `customer` object containing `name` and `email`, and an `items` array of product SKUs. In JSON this is natural — objects nest inside objects, arrays hold the line items. In CSV, every row must have the same flat set of columns. So the first decision is: how do those nested structures become columns?

The standard answer is flattening with dot notation. The nested `customer.name` becomes a column literally called `customer.name`, and `customer.email` becomes `customer.email`. Simple, readable, and it round-trips back to nested JSON cleanly if your converter supports it. For the `items` array, there are two common approaches: join the values into one cell (`SKU-101; SKU-104`), or explode the row so each item gets its own row with the order details repeated. Which one you pick depends on who's consuming the file — a human reading a report usually wants the joined version; a pivot table wants the exploded version.

Flattening: How Nested Objects Become Columns

Flattening sounds mechanical until you hit real-world JSON with four levels of nesting and arrays inside arrays. Here's how to think about it.

One level of nesting is trivial: `customer.name` and `customer.email` become columns. Two levels (`shipping.address.city`) still works — the column names get long but stay readable. Past three levels, flat column names start to look absurd (`order.shipping.address.geo.lat`), and you should ask whether every level is actually needed in the spreadsheet. Often it isn't. The finance team wants totals and customer names, not geocoordinates. Dropping unneeded branches before converting is the single best way to keep the output sane.

Arrays are the real decision point, and there are exactly three strategies worth knowing. Join puts all values in one cell with a delimiter — best when the consumer just needs to see the list. Explode creates one row per array element, duplicating the parent fields — best for analysis, because each row is one atomic fact. Index creates numbered columns (`item_1`, `item_2`, `item_3`) — occasionally useful, but brittle, because the column count depends on whichever record had the longest array. For our orders example, I'd join the SKUs for a finance report and explode them for an inventory analysis. Same data, different question, different shape.

One more flattening gotcha: inconsistent keys across records. If order #1 has `customer.phone` but order #2 doesn't, a good converter creates the column anyway and leaves that cell empty. A bad one crashes or silently drops the field. When you're converting API data with optional fields — which is nearly all API data — make sure your converter handles ragged records gracefully. The JSON to CSV & CSV to JSON Converter builds the full column set from every record before writing a single row, so no field gets lost.

Where CSV Breaks: Commas, Quotes, and Newlines in Your Data

CSV's dirty secret is that its delimiter appears constantly inside real data. A customer named "Smith, Jr." has a comma in the name field. A product description contains quotes. An address has a line break. The CSV spec (RFC 4180) handles all of this with exactly one rule: wrap the field in double quotes, and double any quotes inside it.

So `Smith, Jr.` becomes `"Smith, Jr."` — quoted, comma harmless. A description like `24" monitor` becomes `"24"" monitor"` — wrapped in quotes, inner quote doubled. It looks odd but every correct CSV parser reads it back perfectly. The trouble starts with tools that don't follow the rule: hand-rolled export scripts that skip quoting, or spreadsheet programs that guess at delimiters. If your converted file opens wrong, the quoting is the first thing to check — open the raw file in a text editor and look at whether fields containing commas are wrapped in quotes.

Newlines inside fields are the sneakiest breakage. An address like `12 High Street` newline `London` is legal inside a quoted CSV field, but plenty of naive parsers treat every newline as a new row, splitting one record across two lines and shifting every column after it. If your row counts don't match between the JSON and the CSV, embedded newlines are suspect number one.

The practical takeaway: always validate the conversion by spot-checking. Count the rows — 5,000 JSON records should give you 5,000 CSV data rows (plus the header). Open three random rows and confirm the fields line up. Five minutes of checking beats an afternoon of explaining to finance why the totals column contains email addresses.

Importing Into Excel Without the Date Disaster

Here's the part that ruins the most afternoons: Excel doesn't just display your CSV, it interprets it, and its interpretations are frequently wrong.

The classic disaster is leading zeros. A product code `00134` opens in Excel as `134`. A phone number `+44 7700 900123` becomes a mangled number or a formula error. Excel sees digits and converts the cell to a number, destroying the original text. The fix: don't double-click the CSV file to open it. Use Data → From Text/CSV (or Data → Get Data) and set the column type to Text during import. That one habit prevents an entire category of data loss.

Dates are the second disaster. Excel will "helpfully" convert anything date-shaped into its own date format — `03/04/2026` might become March 4th or April 3rd depending on your locale settings, and gene researchers famously discovered that Excel had been silently corrupting gene names like `SEPT2` into dates for years. For CSV imports, setting date columns to Text on import and converting deliberately afterward is far safer than letting Excel guess.

The third gotcha is character encoding. If your JSON contains non-ASCII characters — accented names, emoji, currency symbols — and the CSV opens with mojibake like `é` instead of `é`, that's an encoding mismatch. JSON is virtually always UTF-8; Excel on Windows often assumes the local legacy encoding. Saving or converting with a UTF-8 BOM (byte order mark) at the start of the file usually makes Excel detect UTF-8 correctly.

None of this is the converter's fault, which is worth remembering when something looks wrong: the CSV file itself is often perfect, and Excel's import assumptions are doing the damage. Import deliberately, set your column types, and the data survives intact.

Going the Other Way: CSV Back to JSON

Conversions aren't always one-directional. Maybe you received a spreadsheet from a client, cleaned it up, and now need to feed it back into an API as JSON. Going CSV → JSON has its own traps.

The biggest one is type loss. CSV has no types — everything is text. When converting back, `"true"` might become the boolean `true` or stay the string `"true"` depending on the converter's guessing rules, and `"00134"` might become the number `134`. A good converter lets you control this: keep everything as strings by default and cast deliberately, rather than guessing. Guessing is how `yes` becomes `true` and your data subtly changes meaning.

Column headers become keys, so clean them first. Spaces, special characters, and duplicate headers in the CSV become awkward or broken keys in the JSON. Rename `Customer Name` to `customer_name` before converting and you'll thank yourself later. And if you flattened with dot notation on the way out (`customer.name`), a smart converter can un-flatten on the way back in, restoring the nested structure — which is exactly what the JSON to CSV & CSV to JSON Converter does.