How to Convert JSON to Excel Using Python

In the data age of today, JSON (JavaScript Object Notation) is probably the most popular format used to transfer data from servers to applications. It’s light, readable, and supported by many different development environments. Yet, for presenting, examining, or disseminating information to non-technical audiences, JSON is not always the easiest format.

That’s where Excel is useful.

Excel remains the default utility for most analysts, managers, and business users when it comes to reviewing data and reporting. Converting JSON to Excel empowers developers and data engineers to bridge the gap between unstructured data and easy-to-analyze reports.

This guide will walk you through an easy and effective way to convert JSON data into an Excel file using Python.

We’ll be using the powerful pandas library to work with the data and openpyxl to work with Excel export. Whether you’re working with API responses, log files, or custom JSON structures — this approach is a tidy and customizable solution for your data conversion requirements.

Step 1: Install Required Libraries

We’ll use:

• pandas for data manipulation
• openpyxl to handle Excel file formats (.xlsx)

Run the following commands to install them:

pip install pandas openpyxl

Step 2: Create a JSON File

Let’s create a simple JSON file called data.json. You can place this in your project directory.

    "name": "TechHub",
    "website": "techhub.io",
    "location": "San Francisco",
    "active": true
}

This is a basic JSON object, and we’ll now load it into Python.

Step 3: Read JSON in Python

We’ll use Python’s built-in json module to load this file and then pandas to convert it into a DataFrame.

# main.py

import json
import pandas as pd

# Load the JSON file
with open('data.json', 'r') as file:
    data = json.load(file)

# Convert it to a pandas DataFrame
df = pd.DataFrame([data])  # Wrap in a list to ensure it's treated as a row
print(df)

Out Put

Step 4: Export Data to Excel

Next, we’ll save the DataFrame as an Excel file using the to_excel() function.

# Export to Excel file
df.to_excel('result.xlsx', index=False)

This will generate a result.xlsx file in the current directory with all the data neatly formatted into columns.

Name	Website	Location	Active
TechHub	techhub.io	San Francisco	true

Example with a JSON Array

Now, let’s update our JSON file to contain an array of records:

[
    {"name": "Adam Luis", "age": 23, "department": "S/w"},
    {"name": "Abigail Lee", "age": 20, "department": "S/w"},
    {"name": "Mia Reed", "age": 25, "department": "HR"}
]

Save it as employees.json. Here’s how to read and export this structure:

import json
import pandas as pd

# Load the JSON array
with open('employees.json', 'r') as file:
    data = json.load(file)

# Convert to DataFrame directly
df = pd.DataFrame(data)

# Export to Excel
df.to_excel('employees.xlsx', index=False)

Out Put

Name	Age	Department
Adam Luis	23	S/w
Abigail Lee	20	S/w
Mia Reed	25	HR

Handling Nested JSON (Bonus)

What if you have nested JSON like this?

{
    "name": "Laptop",
    "specs": {
        "brand": "Lenovo",
        "ram": "16GB",
        "storage": "512GB SSD"
    },
    "price": 1200
}

You can flatten it manually before converting:

import json
import pandas as pd

# Load nested JSON
with open('product.json', 'r') as file:
    data = json.load(file)

# Flatten manually
flattened = {
    "name": data["name"],
    "brand": data["specs"]["brand"],
    "ram": data["specs"]["ram"],
    "storage": data["specs"]["storage"],
    "price": data["price"]
}

df = pd.DataFrame([flattened])
df.to_excel('product.xlsx', index=False)

Out Put

Name	Brand	Ram	Storage	Price
Laptop	Dell	16GB	512GB SSD	1200

Conclusion

By using pandas with openpyxl, converting JSON to Excel becomes incredibly straightforward, whether you’re dealing with a single object, a list of entries, or even nested structures.

• This approach is especially useful when:
• You want to automate reporting from API data
• You need to preprocess or clean the JSON before exporting
• You’re building data pipelines for Excel consumers