Python convert Excel to JSON

·

1 min read

Converting an Excel file to JSON in Python can be done using the pandas library, which provides methods for reading Excel files and exporting data to JSON format. Here's a basic guide on how to do it:

Install pandas and openpyxl

pip install pandas openpyxl

Sample code

import pandas as pd

excel_file_path = 'path/to/your/excelfile.xlsx'
# Use sheet_name=0 for the first sheet
df = pd.read_excel(excel_file_path, sheet_name='YourSheetName')

# Convert the DataFrame to JSON
json_str = df.to_json(orient='records', lines=True)

# Save the JSON to a file
with open('output.json', 'w') as json_file:
    json_file.write(json_str)

print(json_str)

In this code:

  • orient='records' outputs the JSON string in a record format, which is a list of dictionaries. There are other options like 'split', 'index', 'columns', and 'values' that you can use depending on how you want the JSON to be structured.

  • lines=True will output the JSON string with each record on a separate line, set it to False if you want a JSON array.

Adjust the excel_file_path and sheet_name according to your Excel file's details.