How to Convert JSON to CSV: Every Method Explained (2026)
You have JSON data — from an API, a database export, or a log file — and you need to open it in Excel, load it into Google Sheets, or hand it off to someone who does not write code. The answer is converting it to CSV. This is one of the most searched developer tasks on Google, and it is not as straightforward as it sounds when your JSON is nested, contains arrays, or has inconsistent keys across records. This guide covers every method: online tools, Python, JavaScript, jq, and Excel Power Query, with real working examples for each case.
Why JSON to CSV Is Trickier Than It Looks
A flat JSON array converts to CSV cleanly — each object becomes a row, each key becomes a column header. The problem starts the moment your JSON has any of these characteristics: nested objects (an address field that contains city, state, zip as sub-properties), arrays of values inside a field (a tags field containing multiple strings), inconsistent keys across objects (some records have a phone field and others do not), or multiple levels of nesting (users containing orders containing items).
There is no single universally correct way to flatten these structures, which is why different tools produce different results for the same input. Understanding what each method does with your specific data structure before committing saves significant debugging time.
Method 1: Convert Online — Fastest for One-Off Conversions
For single conversions or when you just need to inspect the data quickly, an online converter is the fastest option. ToolPry's JSON Formatter validates and formats your JSON first — confirming the structure is correct before converting. Paste your JSON, convert to CSV, and download the result. Everything runs in your browser; your data never leaves your machine. This matters for any JSON containing credentials, PII, or proprietary business data.
Online converters typically flatten nested objects by joining key names with a dot or underscore — address.city or address_city — and stringify arrays as comma-separated values within a single cell. If this output structure is not what you need, use a code-based approach where you control the flattening logic.
Method 2: Python — The Most Flexible Approach
Python gives you complete control over how JSON is flattened into tabular structure. For simple flat JSON, the built-in json and csv modules are all you need. For nested JSON, pandas and json_normalize handle most real-world cases with a single function call.
Simple flat JSON array to CSV
import json
import csv
with open('data.json') as f:
data = json.load(f)
# data must be a list of dicts:
# [{"name": "Alice", "age": 30}, {"name": "Bob", "age": 25}]
with open('output.csv', 'w', newline='', encoding='utf-8') as f:
if not data:
print("Empty data — nothing to write")
else:
writer = csv.DictWriter(f, fieldnames=data[0].keys())
writer.writeheader()
writer.writerows(data)
print(f"Converted {len(data)} rows")
Nested JSON using pandas json_normalize
import json
import pandas as pd
with open('data.json') as f:
data = json.load(f)
# json_normalize flattens nested objects automatically
# {"user": {"name": "Alice", "address": {"city": "NYC"}}}
# becomes columns: user.name, user.address.city
df = pd.json_normalize(data)
df.to_csv('output.csv', index=False)
print(df.head())
print(f"Shape: {df.shape}")
Deeply nested JSON with arrays — expanding into rows
import json
import pandas as pd
with open('orders.json') as f:
data = json.load(f)
# JSON structure:
# {"order_id": 1, "items": [{"sku": "A", "qty": 2}, {"sku": "B", "qty": 1}]}
# Use record_path to expand the nested array into individual rows
df = pd.json_normalize(
data,
record_path='items', # expand this array — each item becomes a row
meta=['order_id', 'date'], # keep these parent fields in every row
meta_prefix='order_' # prefix parent fields to avoid name collisions
)
df.to_csv('orders_flat.csv', index=False)
print(df)
From an API response directly
import requests
import pandas as pd
response = requests.get('https://api.example.com/users')
data = response.json()
# APIs often wrap data in a key — detect it
if isinstance(data, dict):
for key in ['data', 'results', 'items', 'records']:
if key in data and isinstance(data[key], list):
data = data[key]
break
df = pd.json_normalize(data)
df.to_csv('api_output.csv', index=False)
print(f"Exported {len(df)} rows, {len(df.columns)} columns")
Method 3: JavaScript / Node.js
For converting JSON to CSV in Node.js — useful in automation scripts, build pipelines, or when processing JSON files as part of a larger workflow — the right approach depends on whether your data is flat or nested.
Flat JSON — no dependencies required
const fs = require('fs');
const data = JSON.parse(fs.readFileSync('data.json', 'utf8'));
// Collect all unique headers across all objects
// This handles records with inconsistent keys
const headers = [...new Set(data.flatMap(obj => Object.keys(obj)))];
const rows = data.map(obj =>
headers.map(h => {
const val = obj[h] ?? '';
const str = String(val);
// Quote values containing commas, quotes, or newlines
if (str.includes(',') || str.includes('"') || str.includes('\n')) {
return '"' + str.replace(/"/g, '""') + '"';
}
return str;
}).join(',')
);
const csv = [headers.join(','), ...rows].join('\n');
fs.writeFileSync('output.csv', csv, 'utf8');
console.log(`Written ${data.length} rows, ${headers.length} columns`);
Nested JSON using the flat package
// npm install flat
const fs = require('fs');
const { flatten } = require('flat');
const data = JSON.parse(fs.readFileSync('nested.json', 'utf8'));
// Flatten each object: {user: {name: 'Alice'}} becomes {'user_name': 'Alice'}
const flatData = data.map(obj => flatten(obj, { delimiter: '_' }));
const headers = [...new Set(flatData.flatMap(obj => Object.keys(obj)))];
const rows = flatData.map(obj =>
headers.map(h => {
const val = obj[h] ?? '';
const str = String(val);
return str.includes(',') ? '"' + str + '"' : str;
}).join(',')
);
const csv = [headers.join(','), ...rows].join('\n');
fs.writeFileSync('output.csv', csv, 'utf8');
Method 4: jq on the Command Line
If you have jq installed (brew install jq on Mac or apt install jq on Ubuntu), it converts flat JSON arrays to CSV directly in the terminal without needing a programming language. Ideal for quick conversions in shell scripts or CI pipelines.
# Convert JSON array to CSV with auto-detected headers
# Input: [{"name":"Alice","age":30},{"name":"Bob","age":25}]
cat data.json | jq -r '
(.[0] | keys_unsorted) as $keys |
$keys,
(.[] | [.[$keys[]]] | map(tostring))
| @csv
' > output.csv
# Specify exact columns (safer — controls column order)
cat data.json | jq -r '
["name","email","age"],
(.[] | [.name, .email, (.age | tostring)])
| @csv
' > output.csv
# Extract from a nested API response key
cat api_response.json | jq -r '.data |
(.[0] | keys_unsorted) as $k |
$k, (.[] | [.[$k[]]] | map(tostring)) | @csv
' > output.csv
Method 5: Excel Power Query — No Code Required
If the end destination is Excel and you want to avoid writing any code, Power Query handles JSON to tabular data natively. In Excel: go to Data → Get Data → From File → From JSON, select your file. Power Query shows a preview and lets you expand nested columns by clicking the expand icon next to each column header. You can also load JSON from a URL via Data → Get Data → From Other Sources → From Web.
The trade-off: Power Query requires manually expanding nested columns one level at a time. For deeply nested JSON with many levels, Python is significantly faster. For a one-time analysis of a moderately structured API response, Power Query's point-and-click interface is faster than writing code.
Handling Common Problems
Inconsistent keys across records
When some objects have fields that others lack, Python's csv.DictWriter with fieldnames set to the union of all keys handles this correctly. Missing values become empty strings. In JavaScript, the new Set(data.flatMap(Object.keys)) pattern shown above also handles this automatically.
Arrays embedded in field values
A field like tags: ["python", "javascript"] cannot directly become a CSV cell without losing the list structure. Three options: join as a string with a delimiter ("python|javascript"), JSON-stringify the array into a string ('["python","javascript"]'), or expand each array element into its own row. In pandas: df.explode('tags').to_csv(...) expands each tag into a separate row, duplicating the other fields.
Unicode and encoding issues
Always specify encoding='utf-8' in Python when writing CSV files. For Excel on Windows, add encoding='utf-8-sig' — the BOM (Byte Order Mark) helps Excel detect UTF-8 correctly and prevents garbled characters for non-ASCII text like accented letters, Chinese characters, or emoji. The BOM is invisible to most other applications and causes no issues.
Numbers stored as strings
JSON numbers become strings in CSV. If you need proper numeric types in Excel or a database, add a conversion step: df['age'] = pd.to_numeric(df['age'], errors='coerce') in pandas, or type-cast individual fields before writing. pandas json_normalize usually infers types correctly from the JSON values.
Complete Script: API to CSV in One File
#!/usr/bin/env python3
"""Fetch JSON from an API and save as CSV. Usage: python3 api_to_csv.py"""
import requests
import pandas as pd
import sys
API_URL = 'https://api.example.com/users'
OUTPUT = 'users.csv'
def main():
print(f"Fetching: {API_URL}")
r = requests.get(API_URL, timeout=30)
r.raise_for_status()
data = r.json()
# Unwrap common API envelope patterns
if isinstance(data, dict):
for key in ['data', 'results', 'items', 'records', 'users']:
if key in data and isinstance(data[key], list):
data = data[key]
print(f"Unwrapped key: '{key}'")
break
if not isinstance(data, list):
print(f"ERROR: Expected list, got {type(data)}")
sys.exit(1)
df = pd.json_normalize(data)
df.to_csv(OUTPUT, index=False, encoding='utf-8-sig')
print(f"Saved {len(df)} rows x {len(df.columns)} cols to {OUTPUT}")
print(f"Columns: {list(df.columns)[:10]}{'...' if len(df.columns) > 10 else ''}")
if __name__ == '__main__':
main()
Frequently Asked Questions
Can I convert CSV back to JSON?
Yes — pandas handles the reverse direction cleanly: pd.read_csv('file.csv').to_json('output.json', orient='records', indent=2). The orient='records' parameter produces a JSON array of objects, with column names as keys — the format most APIs and JavaScript applications expect. ToolPry's JSON Formatter also supports converting CSV to JSON directly in the browser.
What does orient='records' mean in pandas?
orient='records' produces an array of objects — one object per row, column names as keys. Other useful options: 'index' (dictionary keyed by row number), 'columns' (dictionary keyed by column names with nested row data), 'values' (2D array of raw values, no keys). Use 'records' as the default unless you have a specific API format requirement.
How do I handle null values during conversion?
In Python, pandas represents missing values as NaN. Use df.fillna('') before exporting to replace all NaN with empty strings, making the CSV cleaner for non-technical recipients. Use df.fillna('null') to make the absence of a value explicit in the output. In JavaScript, the ?? '' null-coalescing operator in the code examples above handles both null and undefined values automatically.
My JSON has thousands of records — what is the fastest method?
For very large files (100MB+), pandas with chunked reading is the most practical approach. Use pd.read_json('large.json', lines=True, chunksize=10000) for NDJSON (newline-delimited JSON), or use the Python standard library ijson package for streaming parsing of large JSON arrays. Avoid loading multi-gigabyte JSON files entirely into memory — stream them instead.
Real-World Use Case: Processing a Shopify Product Export
A common scenario: you export product data from Shopify's admin in JSON format and need to import it into a spreadsheet, a different e-commerce platform, or a database. Shopify product JSON is moderately nested — each product has a variants array and an images array that need special handling during conversion.
import json
import pandas as pd
with open('shopify_products.json') as f:
raw = json.load(f)
# Shopify wraps products in a top-level key
products = raw.get('products', raw) if isinstance(raw, dict) else raw
# Expand variants — each variant becomes its own row
# keeping product-level fields (title, vendor, status) in every row
df = pd.json_normalize(
products,
record_path='variants',
meta=['id', 'title', 'vendor', 'product_type', 'status', 'tags'],
meta_prefix='product_',
errors='ignore' # skip products missing expected fields
)
# Clean up: rename variant id to avoid collision with product id
df.rename(columns={'id': 'variant_id'}, inplace=True)
# Select useful columns for the spreadsheet
columns = ['product_id', 'product_title', 'variant_id', 'title',
'sku', 'price', 'compare_at_price', 'inventory_quantity',
'product_vendor', 'product_type', 'product_status']
output = df[[c for c in columns if c in df.columns]]
output.to_csv('shopify_products_flat.csv', index=False, encoding='utf-8-sig')
print(f"Exported {len(output)} variants across {output['product_id'].nunique()} products")
Automating JSON to CSV in CI/CD Pipelines
For teams that regularly process JSON exports — API snapshots, database dumps, analytics outputs — automating the conversion in CI/CD eliminates the manual step. A GitHub Actions workflow that runs the conversion script on every push or on a schedule produces a fresh CSV automatically.
# .github/workflows/json-to-csv.yml
name: Convert JSON exports to CSV
on:
push:
paths: ['data/*.json']
schedule:
- cron: '0 6 * * 1' # Every Monday at 6am UTC
jobs:
convert:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- uses: actions/setup-python@v4
with: {python-version: '3.12'}
- run: pip install pandas
- run: python3 scripts/json_to_csv.py
- uses: actions/upload-artifact@v4
with:
name: csv-exports
path: output/*.csv
Frequently Asked Questions
Can I convert CSV back to JSON?
Yes. pandas handles the reverse direction in one line: pd.read_csv('file.csv').to_json('output.json', orient='records', indent=2). The orient='records' parameter produces a JSON array of objects with column names as keys — the format most web APIs and JavaScript applications expect. ToolPry's JSON Formatter also converts CSV to JSON directly in the browser with no code required.
How do I handle null and NaN values in the conversion?
In Python, pandas represents missing JSON values as NaN in the DataFrame. Use df.fillna('') before exporting to replace all NaN with empty strings, making the CSV cleaner for non-technical recipients. Use df.fillna('null') if you want the absence of a value to be explicit in the output. In JavaScript, the ?? '' null-coalescing operator handles both null and undefined values automatically in the code examples above.
What is the fastest way to convert very large JSON files?
For files over 500MB, avoid loading the entire file into memory. Use ijson in Python for streaming JSON parsing: pip install ijson, then import ijson; objects = ijson.items(file, 'item') for a top-level array. Write rows to CSV incrementally using csv.DictWriter with each object as it is parsed rather than collecting all objects first. For NDJSON (one JSON object per line), use pd.read_json('file.ndjson', lines=True, chunksize=10000) to process in chunks.
Why does my CSV have extra quotes around everything?
Python's csv module quotes fields that contain commas, quotes, or newlines — this is correct CSV behaviour. If every field is quoted regardless, you may have accidentally set quoting=csv.QUOTE_ALL. The default csv.QUOTE_MINIMAL only quotes when necessary. If the output looks wrong, check your csv.DictWriter or csv.writer instantiation for a quoting parameter, and ensure you are using newline='' when opening the output file on Windows to prevent double newlines.