Skip to main content

How to Convert JSON to CSV in Python

In data engineering and web development, JSON (JavaScript Object Notation) is the standard format for API responses, while CSV (Comma Separated Values) remains the preferred format for spreadsheet analysis and legacy database imports. Converting between these two is a fundamental skill.

This guide demonstrates how to parse a JSON file containing web access logs and transform it into a strictly formatted CSV file using Python's built-in libraries.

Understanding the Data Structure​

Before writing code, we must understand the structure of the input JSON. For this guide, we assume a file named web_access.json which contains a list of dictionary objects.

Example JSON Structure:

[
    {
        "IP": "72.55.30.187",
        "Status": "202",
        "Time": "[2025-02-23 16:25:10]",
        "HttpReferer": "http://www.google.cn/search?q=hive",
        ...
    },
    ...
]

We need to flatten this list of dictionaries into rows, where keys (like IP, Status) become the header columns.

Step 1: Loading JSON Data​

We use Python's built-in json library to deserialize the file content into a Python list of dictionaries.

import json

# ✅ Correct: Open the file and load data into a variable
with open("web_access.json", "r") as json_file:
    data = json.load(json_file)

# 'data' is now a standard Python list
print(f"Loaded {len(data)} records.")

Step 2: Configuring the CSV Writer​

To write the CSV, we use the csv library. Specifically, csv.DictWriter is ideal here because our source data is already in dictionary format.

We must explicitly define fieldnames to ensure the columns appear in a specific order, rather than the arbitrary order of a dictionary.

Critical Settings​

  • newline="": Required when opening CSV files in Python 3 to prevent blank lines between rows on Windows.
  • quoting=csv.QUOTE_ALL: Ensures every field (strings and numbers) is wrapped in quotes (e.g., "200" instead of 200).
import csv

fieldnames = ["IP", "Status", "Time", "HttpReferer", "HttpUserAgent", 
              "Request", "HttpXForwardedFor", "BodyBytesSent", 
              "RemoteUser", "RequestLength"]

with open("result.csv", "w", newline="") as csvfile:
    # Initialize the writer
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames, quoting=csv.QUOTE_ALL)

    # Write the header row (IP, Status, etc.)
    writer.writeheader()

    # Write the data
    # Method A: Loop through rows
    for row in data:
        writer.writerow(row)
        
    # Method B (Alternative): Write all at once
    # writer.writerows(data)
note

If your JSON data contains keys that are not in your fieldnames list, DictWriter will raise a ValueError by default. You can use the extrasaction='ignore' parameter in DictWriter to skip extra data safely.

Complete Code Solution​

Here is the complete script convert.py. It combines the loading and writing steps into a robust process.

import csv
import json

def convert_json_to_csv(input_file, output_file):
    # 1. Read JSON data
    try:
        with open(input_file, "r") as json_file:
            data = json.load(json_file)
    except FileNotFoundError:
        print(f"Error: The file {input_file} was not found.")
        return

    # 2. Define Column Order
    fieldnames = [
        "IP",
        "Status",
        "Time",
        "HttpReferer",
        "HttpUserAgent",
        "Request",
        "HttpXForwardedFor",
        "BodyBytesSent",
        "RemoteUser",
        "RequestLength",
    ]

    # 3. Write CSV data
    with open(output_file, "w", newline="") as csvfile:
        writer = csv.DictWriter(csvfile, fieldnames=fieldnames, quoting=csv.QUOTE_ALL)

        # Write Header
        writer.writeheader()

        # Write Rows
        # writerows is more efficient than a for loop for large datasets
        writer.writerows(data)
        
    print(f"Successfully converted {input_file} to {output_file}")

if __name__ == "__main__":
    convert_json_to_csv("web_access.json", "result.csv")

Execution​

Run the script from your terminal:

python convert.py

Verification​

Check the output content:

head result.csv

Output:

"IP","Status","Time","HttpReferer","HttpUserAgent","Request",...
"72.55.30.187","202","[2016-02-23 16:25:10]","http://www.google.cn/search?q=hive",...
"55.222.156.202","200","[2016-02-23 16:25:10]","-",...

Conclusion​

Converting JSON to CSV is a two-step process: deserialization (json.load) and serialization (csv.DictWriter).

  1. Use json.load() to turn the file into Python objects.
  2. Use csv.DictWriter to map dictionary keys to CSV columns.
  3. Define fieldnames explicitly to control column order.
  4. Set newline="" to ensure cross-platform compatibility.

Table of Contents