JSON to CSV Conversion Guide

When Do You Need JSON to CSV?

JSON is the ideal format for data exchange between programs, but for data analysis, reporting, and business users, CSV (comma-separated values) or Excel format is more intuitive. Common JSON-to-CSV scenarios: extracting data from an API and importing to Excel for analysis; converting database query results (JSON format) to shareable spreadsheets; exporting product or user data for non-technical staff; data migration tasks converting JSON-format data into CSV-format target systems.

Prerequisites for JSON to CSV Conversion

CSV is a flat table structure (rows × columns), while JSON is a tree structure (arbitrary nesting). For JSON to successfully convert to CSV: the outermost JSON must be an array; each element in the array must be an object with the same structure; object field names become CSV column headers; object field values become cell data in corresponding rows.

If JSON contains nested objects or arrays, you must decide how to "flatten" these nested structures. Not all JSON data is suitable for direct CSV conversion — deeply nested JSON may need manual field extraction first.

Simple JSON Array to CSV Example

// JSON 输入 / JSON Input
[
  {"id": 1, "name": "Alice", "age": 30, "city": "Beijing"},
  {"id": 2, "name": "Bob", "age": 25, "city": "Shanghai"},
  {"id": 3, "name": "Carol", "age": 28, "city": "Guangzhou"}
]

// CSV 输出 / CSV Output
id,name,age,city
1,Alice,30,Beijing
2,Bob,25,Shanghai
3,Carol,28,Guangzhou

Flattening Strategies for Nested JSON

For nested objects, typically use dot-notation paths as column names:

// 嵌套 JSON / Nested JSON
{"name": "Alice", "address": {"city": "Beijing", "zip": "100000"}}

// 展平后 CSV 列名 / Flattened CSV column names
name, address.city, address.zip

For nested arrays, handling is more complex. Common strategies: serialize the array as a JSON string in a single cell (preserves all data but sacrifices readability); expand the array into multiple rows (one row per array item, parent row data repeated); take only the first or a few array items (discards some data). Choose the most appropriate strategy based on specific business requirements.

JSON to CSV in Python

import json, csv

# 读取 JSON
with open('data.json', 'r', encoding='utf-8') as f:
    data = json.load(f)

# 写入 CSV
if data:
    with open('output.csv', 'w', newline='', encoding='utf-8-sig') as f:
        writer = csv.DictWriter(f, fieldnames=data[0].keys())
        writer.writeheader()
        writer.writerows(data)

Note: use utf-8-sig encoding (with BOM) to ensure Excel correctly recognizes Chinese characters and avoids garbled text. For English-only data, regular utf-8 is sufficient.

Conversion in JavaScript/Node.js

const { Parser } = require('json2csv');

const fields = ['id', 'name', 'age', 'city'];
const opts = { fields };

try {
  const parser = new Parser(opts);
  const csv = parser.parse(jsonData);
  console.log(csv);
} catch (err) {
  console.error(err);
}

CSV Format Considerations

Details to note when generating CSV: Field value contains a comma: the value must be enclosed in double quotes, e.g., "New York, USA"; Field value contains double quotes: double quotes must be escaped as two double quotes, e.g., "Say ""hello"""; Field value contains a newline: the entire value must be enclosed in double quotes; Separator choice: some regions (like German-speaking areas) use semicolons instead of commas as separators — Excel in these regions may not correctly recognize comma-separated CSV.

For internationalized data, consider using tab separators (TSV format) instead of commas to avoid most separator conflict issues.

Online JSON to CSV Tool Recommendations

Selection tips for online JSON-to-CSV tools: prioritize tools that process data locally in the browser (avoid uploading sensitive data to third-party servers); confirm the tool supports custom field selection (not all JSON fields need to be exported); verify the tool handles Chinese and special characters correctly (some tools have poor non-ASCII support); check whether it supports flattening of nested structures.

For large files (JSON with tens of thousands of rows or more), online tools may be slow or run out of memory. Use command-line tools (like jq + custom scripts) or Python scripts instead.