JSON to CSV: Convert JSON Data to Spreadsheet Format

March 31, 2026 · 12 min read

Understanding JSON and CSV Formats

Before diving into conversion techniques, let's establish a solid understanding of both formats. JSON (JavaScript Object Notation) and CSV (Comma-Separated Values) serve different purposes in the data ecosystem, and knowing their strengths helps you work with them effectively.

JSON is a lightweight data interchange format that's both human-readable and machine-parsable. It's become the de facto standard for APIs and web services because it handles complex data structures naturally. You'll find JSON everywhere—from configuration files to API responses to NoSQL databases.

Here's what a typical JSON structure looks like:

{
  "users": [
    {
      "id": 1,
      "name": "Sarah Chen",
      "email": "[email protected]",
      "role": "developer",
      "active": true
    },
    {
      "id": 2,
      "name": "Marcus Johnson",
      "email": "[email protected]",
      "role": "designer",
      "active": false
    }
  ]
}

CSV, on the other hand, represents data in a tabular format—think spreadsheet rows and columns. Each line is a record, and commas separate the fields within that record. CSV files are universally compatible with spreadsheet applications, databases, and data analysis tools.

The same data in CSV format:

id,name,email,role,active
1,Sarah Chen,[email protected],developer,true
2,Marcus Johnson,[email protected],designer,false

The key differences between these formats become apparent when you work with them:

Why Convert JSON to CSV?

Converting JSON to CSV isn't just a technical exercise—it solves real business problems. Understanding when and why to convert helps you make better decisions about your data workflow.

Data Analysis and Visualization

Analysts and data scientists often prefer CSV because it integrates seamlessly with their tools. Excel, Google Sheets, Tableau, and R all handle CSV files natively. When you receive JSON data from an API but need to create pivot tables, charts, or statistical analyses, CSV becomes your bridge to these powerful tools.

For example, if you're pulling sales data from an e-commerce API, the JSON response might contain hundreds of transactions. Converting to CSV lets you quickly import into Excel, create summary tables, and generate visualizations for stakeholder presentations.

Database Import Operations

Many relational databases offer optimized bulk import features for CSV files. PostgreSQL's COPY command, MySQL's LOAD DATA INFILE, and similar features in other databases can process millions of CSV rows in seconds. JSON imports, while possible, often require more complex parsing and are slower.

When migrating data between systems or performing initial database seeding, CSV conversion can dramatically reduce import times.

Simplicity and Compatibility

CSV files are universally understood. You can email a CSV file to anyone, and they can open it—no special software required. This makes CSV ideal for:

• Sharing data with non-technical stakeholders
• Creating data backups that are easy to restore
• Generating reports for regulatory compliance
• Archiving data in a format that will remain accessible for decades

Performance and File Size

For large datasets with simple structures, CSV files are significantly smaller than their JSON equivalents. The reduction in formatting characters (brackets, quotes, colons) can result in 30-50% smaller file sizes. This matters when you're transferring data over networks or storing large archives.

Legacy System Integration

Many enterprise systems built before the JSON era still expect CSV input. Converting JSON to CSV lets you integrate modern APIs with legacy systems without extensive system rewrites. This is particularly common in industries like finance, healthcare, and manufacturing where older systems remain in production.

Simple Conversion Methods

Let's explore the most straightforward ways to convert JSON to CSV, starting with methods that require no programming knowledge.

Using Spreadsheet Applications

Both Excel and Google Sheets can import JSON data directly, though with some limitations. This method works best for simple, flat JSON structures.

In Microsoft Excel:

1. Open Excel and create a new workbook
2. Go to Data → Get Data → From File → From JSON
3. Select your JSON file
4. Use the Power Query Editor to transform the data
5. Click "Close & Load" to import
6. Save as CSV using File → Save As → CSV (Comma delimited)

In Google Sheets:

1. Create a new spreadsheet
2. Use the IMPORTDATA() function if your JSON is hosted online
3. Or paste JSON into a cell and use Apps Script for parsing
4. Download as CSV using File → Download → Comma-separated values

Command Line Tools

For developers comfortable with the terminal, command-line tools offer quick conversion without writing code. The jq utility is particularly powerful for JSON manipulation.

Install jq on your system:

# macOS
brew install jq

# Ubuntu/Debian
sudo apt-get install jq

# Windows (using Chocolatey)
choco install jq

Convert a simple JSON array to CSV:

jq -r '.[] | [.id, .name, .email] | @csv' input.json > output.csv

This command extracts specific fields from each JSON object and formats them as CSV rows. The -r flag outputs raw strings without JSON quotes, and @csv handles proper CSV escaping.

Using Online Conversion Tools

Online converters provide the fastest path from JSON to CSV without installing anything. They're perfect for one-off conversions or when you're working on a machine where you can't install software.

Features to Look For

When choosing an online JSON to CSV converter, prioritize these features:

• Privacy: Ensure the tool processes data client-side (in your browser) rather than uploading to servers
• Size limits: Check maximum file size—some tools cap at 1MB while others handle 100MB+
• Nested data handling: Can it flatten nested objects and arrays?
• Customization: Options for delimiter choice, header inclusion, and field selection
• Preview: Ability to preview results before downloading

Typical Workflow

Most online converters follow this pattern:

1. Paste JSON text or upload a JSON file
2. The tool validates and parses the JSON structure
3. Configure conversion options (delimiters, headers, flattening)
4. Preview the CSV output
5. Download the converted file

Programming-Based Solutions

When you need to convert JSON to CSV regularly or as part of an automated workflow, programming solutions offer flexibility and power. Let's explore implementations in popular languages.

Python Implementation

Python's built-in libraries make JSON to CSV conversion straightforward. Here's a robust solution that handles common scenarios:

import json
import csv

def json_to_csv(json_file, csv_file):
    # Read JSON data
    with open(json_file, 'r') as f:
        data = json.load(f)
    
    # Handle both single object and array of objects
    if isinstance(data, dict):
        data = [data]
    
    # Extract headers from first object
    headers = data[0].keys()
    
    # Write to CSV
    with open(csv_file, 'w', newline='', encoding='utf-8') as f:
        writer = csv.DictWriter(f, fieldnames=headers)
        writer.writeheader()
        writer.writerows(data)

# Usage
json_to_csv('input.json', 'output.csv')

For more complex scenarios with nested data, the pandas library provides powerful tools:

import pandas as pd

# Read JSON and normalize nested structures
df = pd.read_json('input.json')

# Flatten nested columns if needed
df = pd.json_normalize(data)

# Export to CSV
df.to_csv('output.csv', index=False)

JavaScript/Node.js Implementation

For JavaScript developers, Node.js offers several approaches. Here's a solution using the popular json2csv library:

const { parse } = require('json2csv');
const fs = require('fs');

// Read JSON file
const jsonData = JSON.parse(fs.readFileSync('input.json', 'utf8'));

// Convert to CSV
const csv = parse(jsonData);

// Write CSV file
fs.writeFileSync('output.csv', csv);

For custom control without external libraries:

const fs = require('fs');

function jsonToCsv(jsonData) {
    const array = Array.isArray(jsonData) ? jsonData : [jsonData];
    const headers = Object.keys(array[0]);
    
    const csvRows = [
        headers.join(','),
        ...array.map(row => 
            headers.map(header => 
                JSON.stringify(row[header] ?? '')
            ).join(',')
        )
    ];
    
    return csvRows.join('\n');
}

const data = JSON.parse(fs.readFileSync('input.json', 'utf8'));
const csv = jsonToCsv(data);
fs.writeFileSync('output.csv', csv);

Other Language Solutions

Handling Complex JSON Structures

Real-world JSON data rarely comes in simple, flat structures. APIs often return nested objects, arrays within arrays, and mixed data types. Converting these complex structures to CSV requires strategic decisions about how to flatten and represent the data.

The Flattening Challenge

Consider this nested JSON structure representing a customer order:

{
  "orderId": "ORD-12345",
  "customer": {
    "name": "Alex Rivera",
    "email": "[email protected]",
    "address": {
      "street": "123 Main St",
      "city": "Portland",
      "state": "OR",
      "zip": "97201"
    }
  },
  "items": [
    {"product": "Laptop", "quantity": 1, "price": 999.99},
    {"product": "Mouse", "quantity": 2, "price": 24.99}
  ],
  "total": 1049.97
}

CSV requires a flat structure, so you have several options:

Option 1: Flatten with Dot Notation

Create column names that reflect the nested structure:

orderId,customer.name,customer.email,customer.address.street,customer.address.city,customer.address.state,customer.address.zip,total
ORD-12345,Alex Rivera,[email protected],123 Main St,Portland,OR,97201,1049.97

This approach preserves the structure but doesn't handle the items array well. You'd need to either:

• Create separate rows for each item (duplicating order information)
• Serialize the array as a JSON string in a single column
• Create multiple columns (item1.product, item2.product, etc.)

Option 2: Normalize into Multiple Tables

Create separate CSV files for different entity types:

orders.csv:

orderId,customerName,customerEmail,street,city,state,zip,total
ORD-12345,Alex Rivera,[email protected],123 Main St,Portland,OR,97201,1049.97

order_items.csv:

orderId,product,quantity,price
ORD-12345,Laptop,1,999.99
ORD-12345,Mouse,2,24.99

This relational approach maintains data integrity and is ideal for database imports.

Option 3: Denormalize with Repeated Rows

Repeat parent data for each child item:

orderId,customerName,customerEmail,product,quantity,price,total
ORD-12345,Alex Rivera,[email protected],Laptop,1,999.99,1049.97
ORD-12345,Alex Rivera,[email protected],Mouse,2,24.99,1049.97

This creates redundancy but keeps everything in one file, which is useful for certain analysis scenarios.

Working with Nested and Array Data

Let's dive deeper into practical techniques for handling the most challenging JSON structures.

Handling Arrays of Primitives

When JSON contains arrays of simple values (strings, numbers), you have several options:

{
  "userId": 101,
  "name": "Jordan Lee",
  "tags": ["developer", "python", "data-science"],
  "scores": [95, 87, 92]
}

You can:

• Join with delimiter: Convert arrays to pipe-separated strings: developer|python|data-science
• Create separate columns: tag1, tag2, tag3 (works only if array length is consistent)
• Keep as JSON: Store the array as a JSON string in a single column

Handling Deeply Nested Objects

For objects nested several levels deep, recursive flattening is your friend. Here's a Python function that handles arbitrary nesting:

def flatten_json(nested_json, separator='_'):
    """Flatten nested JSON into a single-level dictionary."""
    out = {}
    
    def flatten(x, name=''):
        if type(x) is dict:
            for key in x:
                flatten(x[key], name + key + separator)
        elif type(x) is list:
            for i, item in enumerate(x):
                flatten(item, name + str(i) + separator)
        else:
            out[name[:-1]] = x
    
    flatten(nested_json)
    return out

Handling Mixed Data Types

JSON supports multiple data types, but CSV treats everything as text. Handle type conversion carefully:

• Booleans: Convert to "true"/"false" strings or 1/0 integers
• Null values: Decide between empty strings, "null" text, or a placeholder like "N/A"
• Dates: Standardize to ISO 8601 format (YYYY-MM-DD) for consistency
• Numbers: Watch for locale issues with decimal separators (. vs ,)

Real-World Example: API Response

Let's convert a realistic API response from a weather service:

{
  "location": {
    "city": "Seattle",
    "coordinates": {"lat": 47.6062, "lon": -122.3321}
  },
  "current": {
    "temp": 58,
    "conditions": "Cloudy",
    "humidity": 75
  },
  "forecast": [
    {"day": "Monday", "high": 62, "low": 52},
    {"day": "Tuesday", "high": 65, "low": 54}
  ]
}

Flattened CSV output:

city,lat,lon,current_temp,current_conditions,current_humidity,forecast
Seattle,47.6062,-122.3321,58,Cloudy,75,"[{""day"":""Monday"",""high"":62,""low"":52},{""day"":""Tuesday"",""high"":65,""low"":54}]"

Or as separate forecast rows:

city,lat,lon,current_temp,current_conditions,current_humidity,forecast_day,forecast_high,forecast_low
Seattle,47.6062,-122.3321,58,Cloudy,75,Monday,62,52
Seattle,47.6062,-122.3321,58,Cloudy,75,Tuesday,65,54

Best Practices and Common Pitfalls

Converting JSON to CSV successfully requires attention to detail. Here are the practices that separate clean conversions from problematic ones.

Character Encoding

Always use UTF-8 encoding for both input and output files. This ensures international characters, emojis, and special symbols convert correctly. Many CSV readers default to ASCII or Windows-1252, which can corrupt data.

In Python, explicitly specify encoding:

with open('output.csv', 'w', encoding='utf-8-sig', newline='') as f:
    # utf-8-sig adds BOM for Excel compatibility
    writer = csv.writer(f)

Escaping Special Characters

CSV uses commas as delimiters, so values containing commas must be quoted. Similarly, quotes within values need escaping. Most CSV libraries handle this automatically, but be aware when writing custom parsers:

• Values with commas: wrap in double quotes
• Values with quotes: escape with double quotes ("")
• Values with newlines: wrap in quotes and preserve the newline

Consistent Column Order

When converting multiple JSON files or processing streaming data, maintain consistent column order across all rows. Inconsistent ordering breaks imports and confuses analysis tools.

Define a schema upfront:

COLUMN_ORDER = ['id', 'name', 'email', 'created_date', 'status']

# Use this order for all conversions
writer = csv.DictWriter(f, fieldnames=COLUMN_ORDER)

Handling Missing Fields

JSON objects in an array might have different fields. Decide how to handle missing values:

[
  {"id": 1, "name": "Alice", "email": "[email protected]"},
  {"id": 2, "name": "Bob"}  // missing email
]

Options:

• Leave empty (most common)
• Use a default value like "N/A" or "unknown"
• Use null or None (though CSV doesn't have a true null concept)

Performance Considerations

For large JSON files (100MB+), consider these optimizations:

• Stream processing: Don't load entire file into memory; process line by line
• Batch writing: Write CSV rows in batches rather than one at a time
• Compression: Use gzip for both input and output to reduce I/O time
• Parallel processing: Split large files and process chunks in parallel

Common Pitfalls to Avoid

• Assuming flat structure: Always inspect JSON before conversion; nested data requires special handling
• Ignoring data types: CSV loses type information; document what each column represents
• Forgetting headers: Always include a header row for clarity
• Using wrong delimiter: Some regions use semicolons or tabs; know your target system
• Not validating JSON: Invalid JSON will cause conversion failures; validate first with JSON Validator

Advanced Conversion Techniques

Once you've mastered basic conversion, these advanced techniques help you handle edge cases and optimize workflows.

Selective Field Extraction

Often you don't need every field from JSON. Extract only what matters:

# Python with pandas
df = pd.read_json('input.json')
df_filtered = df[['id', 'name', 'email', 'created_date']]
df_filtered.to_csv('output.csv', index=False)

This reduces file size and focuses on relevant data.

Data Transformation During Conversion

Apply transformations as you convert:

• Format dates consistently
• Calculate derived fields
• Normalize text (lowercase, trim whitespace)
• Convert units (e.g., timestamps to readable dates)

import pandas as pd
from datetime import datetime

df = pd.read_json('input.json')

# Transform timestamp to readable date
df['created_date'] = pd.to_datetime(df['timestamp'], unit='s')

# Calculate age from birthdate
df['age'] = (datetime.now() - pd.to_datetime(df['birthdate'])).dt.days // 365

df.to_csv('output.csv', index=False)

Handling Large Files with Streaming

For JSON files too large for memory, use streaming parsers:

import ijson
import csv

with open('large_input.json', 'rb') as json_file:
    with open('output.csv', 'w', newline='') as csv_file:
        writer = None
        
        # Stream parse JSON array
        for record in ijson.items(json_file, 'item'):
            if writer is None:
                writer = csv.DictWriter(csv_file, fieldnames=record.keys())
                writer.writeheader()
            writer.writerow(record)

Custom Delimiter and Formatting

Sometimes you need TSV (tab-separated) or pipe-delimited files: