Step-by-Step Guide to Converting JSON to Excel in Python

JSON to Excel Converter

A JSON to Excel converter transforms structured data from JSON format into Excel files, which are widely used for data analysis and reporting. Converting JSON to Excel allows users to view, filter, and manipulate the data in a tabular format using spreadsheet applications like Microsoft Excel or Google Sheets.

This conversion is particularly useful when you need to:

• Present data in a more readable format.
• Share structured data with non-developers.
• Perform advanced data analysis using Excel's built-in features.

Syntax (Using Python with Pandas)

To convert JSON to Excel in Python, we typically use the pandas library. The core methods involved are:

1.	pandas.read_json(): Reads JSON data into a Pandas DataFrame.
2.	DataFrame.to_excel(): Exports the DataFrame to an Excel file.

Example 1: Convert JSON String to Excel File

Python Code:

# Import pandas and StringIO
import pandas as pd
from io import StringIO  # StringIO allows handling strings like files

# Define a JSON string
json_data = '''
[
    {"name": "Macdara", "age": 30, "department": "HR"},
    {"name": "Sara", "age": 25, "department": "IT"},
    {"name": "Charlie", "age": 35, "department": "Finance"}
]
'''

# Wrap the JSON string with StringIO
json_io = StringIO(json_data)  # StringIO converts the string to a file-like object

# Convert the JSON string to a Pandas DataFrame
df = pd.read_json(json_io)  # Read JSON from the StringIO object

# Export the DataFrame to an Excel file
df.to_excel("output.xlsx", index=False)  # Save without the index column

# Print a success message
print("JSON data has been successfully converted to Excel.")

Output:

JSON data has been successfully converted to Excel.

Explanation of the Code:

1. Importing Required Libraries

import pandas as pd
from io import StringIO  # StringIO allows handling strings like files

• pandas is imported as pd. It’s a powerful data manipulation library in Python.
• StringIO is imported from the io module. It allows treating a string as a file-like object, which is useful when working with functions expecting file input, like pandas.read_json().

2. Defining a JSON String

json_data = '''
[
    {"name": "Macdara", "age": 30, "department": "HR"},
    {"name": "Sara", "age": 25, "department": "IT"},
    {"name": "Charlie", "age": 35, "department": "Finance"}
]
'''

• json_data is a multiline string representing an array of JSON objects.
• Each object contains three fields: name, age, and department.



3. Wrapping the JSON String with StringIO

json_io = StringIO(json_data)  # StringIO converts the string to a file-like object

• StringIO(json_data) creates an in-memory stream (file-like object) from the json_data string.
• This is necessary because pandas.read_json() expects either a file path or a file-like object. Wrapping the string in StringIO makes it compatible.



4. Converting the JSON String to a Pandas DataFrame

df = pd.read_json(json_io)  # Read JSON from the StringIO object

• pd.read_json(json_io) reads the JSON data from the StringIO object and converts it into a Pandas DataFrame.
• A DataFrame is a tabular data structure with rows and columns, similar to a spreadsheet or SQL table.
• The resulting DataFrame will have the following structure:

	name	age	department
0	Macdara	30	HR
1	Sara	25	IT
2	Charlie	35	Finance




5. Exporting the DataFrame to an Excel File

df.to_excel("output.xlsx", index=False)  # Save without the index column

• df.to_excel() exports the DataFrame to an Excel file named output.xlsx.
• The index=False argument ensures that the index column (default numerical row labels) is not included in the Excel file.
• The output Excel file will look like this:

name	age	department
Macdara	30	HR
Sara	25	IT
Charlie	35	Finance

Example 2: Convert JSON File to Excel File

Python Code:

# Import pandas library
import pandas as pd

# Load JSON data from a file
df = pd.read_json("data.json")  # Read JSON file into a DataFrame

# Normalize the nested JSON data
df_normalized = pd.json_normalize(df["glossary"])  # Flatten the "glossary" key

# Export the normalized DataFrame to an Excel file
df_normalized.to_excel("converted_output.xlsx", index=False)  # Save without the index column

# Print a success message
print("JSON file has been successfully converted to a normalized Excel.")

Output:

JSON file has been successfully converted to Excel.

Explanation:

1. pd.read_json():

• Reads the entire JSON file into a Pandas DataFrame. Since your JSON file contains nested data under the "glossary" key, the result will initially have a single column with the nested dictionary.

2. pd.json_normalize():

• This function flattens the nested structure. By specifying df["glossary"], it extracts and normalizes the contents under the "glossary" key, creating individual columns for each nested field.

3. to_excel():

• Saves the normalized DataFrame to an Excel file without the index.

Notes:

1. Installing Required Libraries:

• Make sure pandas and openpyxl (required for Excel output) are installed:

pip install pandas openpyxl

2. Customizing the Excel Output:

• You can customize the Excel file by adding formatting options:

df.to_excel("styled_output.xlsx", index=False, sheet_name="Employee Data")

3. Handling Nested JSON:

• If your JSON contains nested objects, you may need to normalize it before converting:

from pandas import json_normalize
df = json_normalize(nested_json_data)

Practical Guides to JSON Snippets and Examples.