PureTools

JSON to CSV: Convert Data for Spreadsheets

PureTools Team· 7 min read
JSON to CSV: Convert Data for Spreadsheets

JSON to CSV: The Developer's Data Export Guide

You've got an API that returns JSON. Your manager wants it in a spreadsheet. Your data analyst needs it in CSV for their Python script. This is one of the most common data conversion tasks in development.

The Structure Mismatch

JSON is hierarchical — it supports nesting, arrays, and mixed types. CSV is flat — rows and columns, nothing else. The conversion isn't always 1:1.

Simple case (flat JSON array):

// JSON
[
  { "name": "Alice", "age": 30, "city": "São Paulo" },
  { "name": "Bob", "age": 25, "city": "Lisboa" }
]

// CSV
name,age,city
Alice,30,São Paulo
Bob,25,Lisboa

This is straightforward — each object becomes a row, each key becomes a column header.

The Hard Part: Nested Objects

{
  "name": "Alice",
  "address": {
    "street": "Rua Augusta",
    "city": "São Paulo"
  },
  "skills": ["JavaScript", "Python"]
}

How do you flatten this? Common approaches:

  • Dot notation: address.street, address.city
  • JSON in cell: Keep nested data as JSON string in the CSV cell
  • Array join: skills becomes "JavaScript, Python"

Handling Edge Cases

CSV has gotchas that trip up naive converters:

  • Commas in values: "São Paulo, SP" — must be quoted
  • Quotes in values: He said "hello" — must escape with double quotes ""
  • Newlines in values: Must be wrapped in quotes
  • Empty values: ,, — two consecutive commas
  • Unicode: UTF-8 BOM (\uFEFF) helps Excel detect encoding

In JavaScript

function jsonToCsv(data) {
  const headers = [...new Set(data.flatMap(Object.keys))];
  const escape = (v) => {
    const s = String(v ?? '');
    return s.includes(',') || s.includes('"') || s.includes('\n')
      ? '"' + s.replace(/"/g, '""') + '"'
      : s;
  };
  const rows = data.map(row =>
    headers.map(h => escape(row[h])).join(',')
  );
  return [headers.join(','), ...rows].join('\n');
}

Key decisions in this code: headers are extracted from all objects (handles inconsistent schemas), values are escaped per RFC 4180, and missing values become empty strings.

In Python (pandas)

import pandas as pd
import json

with open('data.json') as f:
    data = json.load(f)

df = pd.json_normalize(data)  # flattens nested objects
df.to_csv('output.csv', index=False)

json_normalize is the magic here — it automatically flattens nested structures using dot notation for column names.

When NOT to Use CSV

CSV loses information. If your data has:

  • Deeply nested structures → use JSON or Parquet
  • Mixed types in the same column → CSV treats everything as strings
  • Large datasets (>1M rows) → use Parquet or Arrow for performance

Convert now: JSON to CSV converter — handles escaping, quoting, and flat JSON arrays instantly.

← Back to blog