JSON to CSV Python Converter | Transform and Export Data with Code

Prerequisites and Setup

Before we begin, make sure you have:

• Python 3.6 or higher installed on your system
• Basic understanding of Python syntax and data structures
• A text editor or IDE for writing Python code
• Basic familiarity with JSON and CSV data formats

The good news is that Python includes all the libraries we need for this tutorial in its standard library, so no additional installations are required.

Understanding When to Convert Between Formats

There are several common scenarios where format conversion becomes necessary:

JSON to CSV Conversion

• API Data Analysis: When you retrieve data from REST APIs (typically in JSON format) and need to analyze it in Excel or Google Sheets
• Data Migration: Moving data from NoSQL databases to relational databases or data warehouses
• Reporting: Creating reports and dashboards from API responses
• PowerShell Integration: Working with PowerShell scripts that handle CSV files more efficiently than JSON

CSV to JSON Conversion

• API Integration: Sending data to web APIs that expect JSON payloads
• Web Development: Converting spreadsheet data for use in web applications
• Configuration Files: Creating configuration files from tabular data
• Database Imports: Preparing data for NoSQL databases like MongoDB

How to Convert JSON to CSV

Converting JSON to CSV requires understanding the structure of your JSON data. This tutorial works best with JSON arrays containing objects with consistent key-value pairs. Let’s start with a practical example.

Sample Data Setup

First, let’s create a sample JSON file that represents customer data. Save this as customers.json:

[
  {
    "customer_id": 1,
    "name": "Alice Johnson",
    "email": "[email protected]",
    "city": "San Francisco",
    "state": "CA",
    "purchase_amount": 1250.50
  },
  {
    "customer_id": 2,
    "name": "Bob Smith",
    "email": "[email protected]",
    "city": "Austin",
    "state": "TX",
    "purchase_amount": 825.75
  },
  {
    "customer_id": 3,
    "name": "Carol Davis",
    "email": "[email protected]",
    "city": "Miami",
    "state": "FL",
    "purchase_amount": 2100.00
  },
  {
    "customer_id": 4,
    "name": "David Wilson",
    "email": "[email protected]",
    "city": "Seattle",
    "state": "WA",
    "purchase_amount": 675.25
  }
]

Basic JSON to CSV Conversion

Here’s a complete Python script that converts our JSON data to CSV format:

import json
import csv

def json_to_csv(json_file_path, csv_file_path):
    """
    Convert a JSON file to CSV format.

    Args:
        json_file_path (str): Path to the input JSON file
        csv_file_path (str): Path to the output CSV file
    """
    try:
        # Read the JSON file
        with open(json_file_path, 'r', encoding='utf-8') as json_file:
            data = json.load(json_file)

        # Handle case where JSON is not a list
        if not isinstance(data, list):
            print("Error: JSON data must be a list of objects")
            return False

        # Handle empty data
        if not data:
            print("Warning: JSON file is empty")
            return False

        # Extract headers from the first object
        headers = list(data[0].keys())

        # Write to CSV file
        with open(csv_file_path, 'w', newline='', encoding='utf-8') as csv_file:
            writer = csv.DictWriter(csv_file, fieldnames=headers)

            # Write header row
            writer.writeheader()

            # Write data rows
            for row in data:
                writer.writerow(row)

        print(f"Successfully converted {json_file_path} to {csv_file_path}")
        return True

    except FileNotFoundError:
        print(f"Error: File {json_file_path} not found")
        return False
    except json.JSONDecodeError as e:
        print(f"Error: Invalid JSON format - {e}")
        return False
    except Exception as e:
        print(f"Error: {e}")
        return False

# Usage example
if __name__ == "__main__":
    json_to_csv('customers.json', 'customers.csv')

Running this script will create a CSV file with the following structure:

customer_id,name,email,city,state,purchase_amount
1,Alice Johnson,[email protected],San Francisco,CA,1250.5
2,Bob Smith,[email protected],Austin,TX,825.75
3,Carol Davis,[email protected],Miami,FL,2100.0
4,David Wilson,[email protected],Seattle,WA,675.25

How to Convert CSV to JSON

Converting CSV to JSON is typically more straightforward than the reverse process because CSV data has a predictable structure. Let’s create a robust function that handles various edge cases.

import json
import csv

def csv_to_json(csv_file_path, json_file_path, indent=2):
    """
    Convert a CSV file to JSON format.

    Args:
        csv_file_path (str): Path to the input CSV file
        json_file_path (str): Path to the output JSON file
        indent (int): Number of spaces for JSON indentation (default: 2)
    """
    try:
        data = []

        # Read CSV file
        with open(csv_file_path, 'r', encoding='utf-8') as csv_file:
            csv_reader = csv.DictReader(csv_file)

            # Convert each row to a dictionary
            for row in csv_reader:
                # Convert numeric strings to appropriate data types
                converted_row = {}
                for key, value in row.items():
                    # Try to convert to int or float if possible
                    if value.isdigit():
                        converted_row[key] = int(value)
                    else:
                        try:
                            converted_row[key] = float(value)
                        except ValueError:
                            # Keep as string if conversion fails
                            converted_row[key] = value

                data.append(converted_row)

        # Handle empty CSV
        if not data:
            print("Warning: CSV file is empty or has no data rows")
            return False

        # Write JSON file
        with open(json_file_path, 'w', encoding='utf-8') as json_file:
            json.dump(data, json_file, indent=indent, ensure_ascii=False)

        print(f"Successfully converted {csv_file_path} to {json_file_path}")
        print(f"Converted {len(data)} records")
        return True

    except FileNotFoundError:
        print(f"Error: File {csv_file_path} not found")
        return False
    except csv.Error as e:
        print(f"Error: CSV parsing error - {e}")
        return False
    except Exception as e:
        print(f"Error: {e}")
        return False

# Usage example
if __name__ == "__main__":
    csv_to_json('customers.csv', 'customers_converted.json')

This script will produce a well-formatted JSON file with proper data types:

[
  {
    "customer_id": 1,
    "name": "Alice Johnson",
    "email": "[email protected]",
    "city": "San Francisco",
    "state": "CA",
    "purchase_amount": 1250.5
  },
  {
    "customer_id": 2,
    "name": "Bob Smith",
    "email": "[email protected]",
    "city": "Austin",
    "state": "TX",
    "purchase_amount": 825.75
  }
]

Advanced Features and Edge Cases

Handling Nested JSON Objects

Converting nested JSON structures requires flattening the data. Here’s an enhanced version that handles nested objects:

def flatten_json(nested_json, separator='_'):
    """
    Flatten a nested JSON object.

    Args:
        nested_json (dict): The nested JSON object
        separator (str): Character to separate nested keys

    Returns:
        dict: Flattened dictionary
    """
    def _flatten(obj, parent_key=''):
        items = []
        if isinstance(obj, dict):
            for key, value in obj.items():
                new_key = f"{parent_key}{separator}{key}" if parent_key else key
                items.extend(_flatten(value, new_key).items())
        elif isinstance(obj, list):
            for i, value in enumerate(obj):
                new_key = f"{parent_key}{separator}{i}" if parent_key else str(i)
                items.extend(_flatten(value, new_key).items())
        else:
            return {parent_key: obj}
        return dict(items)

    return _flatten(nested_json)

def json_to_csv_advanced(json_file_path, csv_file_path):
    """
    Convert JSON with nested objects to CSV.
    """
    try:
        with open(json_file_path, 'r', encoding='utf-8') as json_file:
            data = json.load(json_file)

        if not isinstance(data, list):
            data = [data]  # Convert single object to list

        # Flatten all objects
        flattened_data = [flatten_json(obj) for obj in data]

        # Get all unique keys
        all_keys = set()
        for obj in flattened_data:
            all_keys.update(obj.keys())

        # Write to CSV
        with open(csv_file_path, 'w', newline='', encoding='utf-8') as csv_file:
            writer = csv.DictWriter(csv_file, fieldnames=sorted(all_keys))
            writer.writeheader()

            for obj in flattened_data:
                writer.writerow(obj)

        print(f"Successfully converted nested JSON to CSV: {csv_file_path}")
        return True

    except Exception as e:
        print(f"Error: {e}")
        return False

Complete Utility Class

Here’s a comprehensive utility class that combines all the functionality:

import json
import csv
import os
from typing import List, Dict, Any, Union

class DataConverter:
    """A comprehensive utility class for converting between JSON and CSV formats."""

    @staticmethod
    def json_to_csv(json_file: str, csv_file: str, flatten_nested: bool = False) -> bool:
        """
        Convert JSON file to CSV format.

        Args:
            json_file (str): Path to input JSON file
            csv_file (str): Path to output CSV file
            flatten_nested (bool): Whether to flatten nested objects

        Returns:
            bool: Success status
        """
        try:
            with open(json_file, 'r', encoding='utf-8') as f:
                data = json.load(f)

            if not isinstance(data, list):
                data = [data]

            if flatten_nested:
                data = [DataConverter._flatten_dict(item) for item in data]

            if not data:
                raise ValueError("No data to convert")

            # Get fieldnames from first object
            fieldnames = list(data[0].keys())

            with open(csv_file, 'w', newline='', encoding='utf-8') as f:
                writer = csv.DictWriter(f, fieldnames=fieldnames)
                writer.writeheader()
                writer.writerows(data)

            print(f"✅ Successfully converted {json_file} to {csv_file}")
            return True

        except Exception as e:
            print(f"❌ Error converting JSON to CSV: {e}")
            return False

    @staticmethod
    def csv_to_json(csv_file: str, json_file: str, indent: int = 2) -> bool:
        """
        Convert CSV file to JSON format.

        Args:
            csv_file (str): Path to input CSV file
            json_file (str): Path to output JSON file
            indent (int): JSON indentation spaces

        Returns:
            bool: Success status
        """
        try:
            data = []

            with open(csv_file, 'r', encoding='utf-8') as f:
                reader = csv.DictReader(f)
                for row in reader:
                    # Convert numeric strings to numbers
                    converted_row = DataConverter._convert_types(row)
                    data.append(converted_row)

            if not data:
                raise ValueError("No data to convert")

            with open(json_file, 'w', encoding='utf-8') as f:
                json.dump(data, f, indent=indent, ensure_ascii=False)

            print(f"✅ Successfully converted {csv_file} to {json_file}")
            return True

        except Exception as e:
            print(f"❌ Error converting CSV to JSON: {e}")
            return False

    @staticmethod
    def _flatten_dict(nested_dict: Dict[str, Any], separator: str = '_') -> Dict[str, Any]:
        """Flatten a nested dictionary."""
        def _flatten(obj: Any, parent_key: str = '') -> Dict[str, Any]:
            items = []
            if isinstance(obj, dict):
                for k, v in obj.items():
                    new_key = f"{parent_key}{separator}{k}" if parent_key else k
                    items.extend(_flatten(v, new_key).items())
            elif isinstance(obj, list):
                for i, v in enumerate(obj):
                    new_key = f"{parent_key}{separator}{i}" if parent_key else str(i)
                    items.extend(_flatten(v, new_key).items())
            else:
                return {parent_key: obj}
            return dict(items)

        return _flatten(nested_dict)

    @staticmethod
    def _convert_types(row: Dict[str, str]) -> Dict[str, Union[str, int, float]]:
        """Convert string values to appropriate data types."""
        converted = {}
        for key, value in row.items():
            if value.isdigit():
                converted[key] = int(value)
            else:
                try:
                    converted[key] = float(value)
                except ValueError:
                    converted[key] = value
        return converted

# Usage examples
if __name__ == "__main__":
    converter = DataConverter()

    # Convert JSON to CSV
    converter.json_to_csv('customers.json', 'output.csv')

    # Convert CSV to JSON
    converter.csv_to_json('output.csv', 'output.json')

    # Convert with nested object flattening
    converter.json_to_csv('nested_data.json', 'flattened.csv', flatten_nested=True)

Troubleshooting Common Issues

Encoding Issues

Always specify UTF-8 encoding when working with files that contain special characters:

# Always use UTF-8 encoding
with open('file.json', 'r', encoding='utf-8') as f:
    data = json.load(f)

Memory Management for Large Files

For very large files, process data in chunks to avoid memory issues:

def process_large_json(json_file, csv_file, chunk_size=1000):
    """Process large JSON files in chunks."""
    with open(json_file, 'r') as f:
        data = json.load(f)

    with open(csv_file, 'w', newline='') as f:
        writer = None
        for i in range(0, len(data), chunk_size):
            chunk = data[i:i + chunk_size]
            if writer is None:
                writer = csv.DictWriter(f, fieldnames=chunk[0].keys())
                writer.writeheader()
            writer.writerows(chunk)

Best Practices and Performance Tips

• Validate Data Structure: Always check if your JSON is in the expected array format before processing
• Handle Missing Values: Use Python’s get()
• Use Type Hints: Add type annotations to make your code more maintainable
• Implement Logging: Use Python's logging module instead of print statements in production code
• Consider pandas: For complex data transformations, consider using pandas DataFrames
• Test with Sample Data: Always test your conversion scripts with small sample files first

# Install pandas: pip install pandas
import pandas as pd

# JSON to CSV with pandas
df = pd.read_json('customers.json')
df.to_csv('customers.csv', index=False)

# CSV to JSON with pandas
df = pd.read_csv('customers.csv')
df.to_json('customers.json', orient='records', indent=2)