JSON to CSV: When APIs Spit Out JSON and Your Boss Wants Excel

I've lost count of how many times I've exported API data only to hear "can you put this in a spreadsheet?" from across the room. The first few times, I actually wrote Python scripts. Then I realized I was spending 15 minutes on something that should take 15 seconds.

JSON and CSV are both text formats, but they model data differently. JSON is hierarchical — objects inside objects, arrays of arrays. CSV is a flat table — rows and columns. Converting between them means making decisions about that nesting. This guide covers what I've learned from doing it wrong.

The flattening problem

Most JSON-to-CSV pain comes from nested data. Take a typical API response:

{
  "users": [
    {
      "id": 1,
      "name": "Alice Chen",
      "email": "[email protected]",
      "address": {
        "city": "San Francisco",
        "state": "CA"
      },
      "tags": ["admin", "beta"]
    }
  ]
}

CSV doesn't have a natural way to represent address.city or tags[0]. Every converter handles this differently — some flatten with dots (address.city), some with brackets (address[city]), and some just give up and print [object Object] into a cell.

I learned this the hard way with Stripe's API. Their charge objects are 12 levels deep in places. If you dump the raw JSON into a naive converter, you get columns like metadata.items[3].custom_fields.0.value — completely unusable in Excel.

Picking which fields matter

Before converting, decide what actually needs to go into the spreadsheet. The person asking for it probably doesn't need all 47 fields from your API response. They need 6 specific columns.

I keep a small checklist when flattening JSON for CSV:

• Top-level scalars — strings, numbers, booleans. These map 1:1 to columns. No drama.
• Nested objects one level deep — pick the 2-3 fields that matter and flatten them. user.address.city becomes a column called "City".
• Arrays — decide: join them with commas into one cell, or explode them into multiple rows? Joining works for tags; exploding works for line items.
• Deep nesting (3+ levels) — skip it unless someone explicitly asked for it.

Encoding gotchas that will ruin your Friday

CSV is older than me and it shows. There's no single CSV standard — the closest thing is RFC 4180, which is more of a post-hoc description than a spec. Here's what bites people:

Commas inside values. If a JSON string contains a comma, your CSV row just gained an extra column. The fix is wrapping values in double quotes — but then you need to escape any double quotes already in the value by doubling them. A string like She said "hello, world" becomes "She said ""hello, world""" in CSV. I've debugged this exact thing twice in production.

Unicode and BOM. Excel on Windows will mangle UTF-8 CSV unless you prepend a BOM (byte order mark — ). Without it, non-ASCII characters render as gibberish. Excel on Mac handles UTF-8 fine without BOM, because of course it does. Google Sheets handles both.

Newlines in fields. JSON strings can contain \n. In CSV, a newline means a new row — unless it's inside a quoted field. Most parsers handle this correctly now, but I've seen older ERP systems choke on it.

Using a browser converter instead of writing scripts

For one-off conversions, I use Formly's built-in converter. Drag a JSON file, pick CSV, and it downloads. No script to maintain, no dependencies to update. The converter handles the encoding details I mentioned above — quotes, commas, UTF-8 — so I don't have to remember them each time.

For recurring data pipelines, a script makes more sense. But for "hey can you pull the Q2 sales data from the API and send it to accounting" — that's a one-off. Use a converter. Move on.

What about CSV back to JSON?

The reverse direction is simpler — CSV is already flat, so there's no nesting decision to make. But you do need to decide: should each row become a flat object, or do you want to rebuild nesting from columns like address.city? For most use cases, flat objects are fine. Your API probably expects an array of objects, and a flat CSV row maps directly to that.

The one trap: type inference. CSV doesn't carry type information — everything is a string. When converting to JSON, a good converter will guess that 42 is a number and true is a boolean, not a string. But if your CSV has zip codes with leading zeros (like 02134), you want those to stay as strings. I've had a Boston user's address break because her zip code got converted to the number 2134.

Quick reference: JSON → CSV by data shape

I've found that the simplest approach works best 90% of the time: pull the fields you need, flatten one level deep, join arrays with commas. If someone needs more detail, they can ask for it — but they usually don't.

If you're working with JSON from a specific API (Stripe, GitHub, Salesforce), check their docs — many have built-in CSV export endpoints that already handle the flattening decisions. No point reinventing it.

And if you've got a JSON file sitting on your desktop right now that you need in Excel, drag it into Formly. It's free, nothing leaves your browser, and you'll be done before your coffee gets cold.

Written by Sam Taylor — Full-stack developer. I build tools that run in your browser so your data never leaves your machine. More about me →