DEV Community

Snappy Tools
Snappy Tools

Posted on • Originally published at snappytools.app

From API to Spreadsheet: How to Convert JSON to CSV Without Writing a Script

#javascript #webdev #beginners #productivity

You just hit a REST API. The response looks like this:

[
  { "name": "Alice", "role": "Engineer", "team": "Backend" },
  { "name": "Bob",   "role": "Designer", "team": "Product" },
  { "name": "Carol", "role": "Engineer", "team": "Frontend" }
]
Enter fullscreen mode Exit fullscreen mode

Your stakeholder wants this in a spreadsheet. Right now.

You have two options: write a quick Python script, or use a browser tool that does it in two seconds. This post covers both — and explains what actually happens during the conversion so you understand the edge cases.

What JSON to CSV conversion actually does

A JSON array of objects maps cleanly to a CSV file:

  • Each object becomes a row
  • Each key becomes a column header
  • Each value becomes a cell

For the example above, the output is:

name,role,team
Alice,Engineer,Backend
Bob,Designer,Product
Carol,Engineer,Frontend
Enter fullscreen mode Exit fullscreen mode

Simple when the data is flat. It gets interesting when objects contain nested structures.

The nested object problem

Most real API responses are not flat. Consider:

[
  {
    "name": "Alice",
    "address": { "city": "London", "country": "UK" },
    "tags": ["backend", "python"]
  }
]
Enter fullscreen mode Exit fullscreen mode

You have two choices for how to handle address and tags:

Flatten (dot-notation headers):

name,address.city,address.country,tags
Alice,London,UK,"[""backend"",""python""]"
Enter fullscreen mode Exit fullscreen mode

The nested address object becomes two columns: address.city and address.country. Each nested key gets a dotted path as its header. This makes the data fully tabular and works cleanly in Excel or Google Sheets.

Stringify (keep as JSON):

name,address,tags
Alice,"{""city"":""London"",""country"":""UK""}","[""backend"",""python""]"
Enter fullscreen mode Exit fullscreen mode

The nested object stays as a JSON string in one cell. Less readable in a spreadsheet, but you can re-parse it later if needed.

Which to use? Flatten if you need to filter or sort by nested fields in the spreadsheet. Stringify if you just want to move the data somewhere and might re-process it later.

Doing it in Python (one function)

If you're already in a script:

import csv
import json

def flatten(obj, prefix=""):
    """Recursively flatten a nested dict with dot-notation keys."""
    items = {}
    for key, val in obj.items():
        full_key = f"{prefix}.{key}" if prefix else key
        if isinstance(val, dict):
            items.update(flatten(val, full_key))
        else:
            items[full_key] = val
    return items

def json_to_csv(data, output_path):
    flat = [flatten(row) for row in data]
    # Collect all unique headers across all rows
    headers = list(dict.fromkeys(k for row in flat for k in row.keys()))

    with open(output_path, "w", newline="", encoding="utf-8") as f:
        writer = csv.DictWriter(f, fieldnames=headers, extrasaction="ignore")
        writer.writeheader()
        for row in flat:
            writer.writerow(row)

# Usage
with open("data.json") as f:
    data = json.load(f)

json_to_csv(data, "output.csv")
Enter fullscreen mode Exit fullscreen mode

This handles:

  • Flat objects
  • Nested dicts (flattened with dot notation)
  • Missing keys (written as empty cells, not errors)

RFC 4180 — the CSV standard that trips people up

CSV sounds simple until a value contains a comma or a newline. RFC 4180 defines the rules:

  1. Values containing commas, quotes, or newlines must be wrapped in double quotes
  2. A literal double quote is escaped as two double quotes ("")
  3. Each row ends with CRLF (\r\n) for maximum compatibility

Python's csv module handles this automatically. If you're writing a CSV by hand with string concatenation — don't. You'll hit edge cases.

The browser alternative

If you just need to convert a JSON blob right now — no terminal, no Python environment — paste it into SnappyTools JSON to CSV Converter. It runs entirely in your browser (no upload, no server), handles flatten vs stringify modes, and downloads a properly escaped RFC 4180 CSV file.

Useful when:

  • You're on a shared machine without a dev environment
  • You need to quickly check what a JSON structure looks like as a table before writing a script
  • You need to share the result with a non-developer immediately

Edge cases to handle in any conversion

Whether you're writing your own code or using a tool, watch for these:

Sparse data — not all objects have the same keys. The converter collects all unique keys across the entire dataset and uses them as headers. Objects missing a key get an empty cell.

Null values — null in JSON becomes an empty string in CSV (there's no CSV equivalent of null).

Boolean values — true and false become the literal strings "true" and "false".

Arrays of primitives — ["a", "b", "c"] doesn't map cleanly to a single column. Most tools stringify it. If you need it split across columns, you'll need a custom transform.

Large files — browser-based tools handle files up to several MB fine. For 50MB+ JSON, use Python or a command-line tool like jq.

Quick reference

Situation Recommendation
Flat JSON array, quick share Browser tool
Nested objects, need tabular output Flatten mode
Need to re-parse later Stringify mode
Automated pipeline Python csv + json modules
50MB+ file jq + shell redirection

JSON to CSV is one of those conversions that looks trivial and hides real complexity in the edge cases. Knowing when to flatten, when to stringify, and how CSV escaping works will save you from corrupted spreadsheets and confused stakeholders.

Top comments (0)