Convert Excel File to JSON using Python

To convert Excel file to a JSON file using Python:

import pandas as pd

# Load Excel to DataFrame
path_excel = 'path_to_excel_file.xlsx'
df = pd.read_excel(path_excel, engine='openpyxl')

# Convert DataFrame to JSON
json_data = df.to_json(orient='records', indent=4)
print(json_data)

# Write JSON data to file
path_json = 'final_result.json'
with open(path_json, 'w') as json_file:
    json_file.write(json_data)

In case needed, install the openpyxl package using pip install openpyxl.

Example of converting Excel to JSON

Let’s suppose that the following data is stored in an Excel file called store_data:

product_namepricestore_numberstore_cityopening_hoursopening_days
computer120077London8-18Mon-Fri
printer20077London8-18Mon-Fri

Here is the Python script to convert the Excel to JSON:

import pandas as pd

# Load Excel to DataFrame
path_excel = 'store_data.xlsx'
df = pd.read_excel(path_excel, engine='openpyxl')

# Convert DataFrame to JSON
json_data = df.to_json(orient='records', indent=4)
print(json_data)

# Write JSON data to a file
path_json = 'final_result.json'
with open(path_json, 'w') as json_file:
    json_file.write(json_data)

The result:

[
    {
        "product_name":"computer",
        "price":1200,
        "store_number":77,
        "store_city":"London",
        "opening_hours":"8-18",
        "opening_days":"Mon-Fri"
    },
    {
        "product_name":"printer",
        "price":200,
        "store_number":77,
        "store_city":"London",
        "opening_hours":"8-18",
        "opening_days":"Mon-Fri"
    }
]

Here is a guide to convert JSON to Excel.

Home / Python / Convert Excel File to JSON using Python

Leave a Comment