Convert Excel To Json With Python

In the realm of data manipulation and analysis, the ability to convert data between different formats is a valuable skill. Excel and JSON are two widely used formats, and Python, with its powerful libraries, provides a seamless way to convert Excel files into JSON. In this article, we will see how we can convert Excel to JSON in Python.

Excel File: Link

student.xlsx (sheet_name = suraj1)

Convert Excel to JSON with Python

Below are some of the ways by which we can convert excel to JSON in Python:

1. Using to_json() Function
2. Using openpyxl Module
3. Using excel2json Module

Using to_json() Function

In this example, Python's pandas library is utilized to read an Excel file named "student.xlsx" from the sheet named "suraj1." The data is then converted to JSON format using to_json() method, and the resulting JSON data is printed to the console using print(json_data).

import pandas as pd
import json

# Convert Excel To Json With Python
data = pd.read_excel("student.xlsx", sheet_name="suraj1")
json_data = data.to_json()

# Print the JSON data
print(json_data)

Output:

[{"Country name": "Mauritania", "2023": 2023, "36th": "37th"}, {"Country name": "North Korea", "2023": 2023, "36th": "38th"}, {"Country name": "Angola", "2023": 2023, "36th": "39th"}, {"Country name": "Iran", "2023": 2023, "36th": "40th"}, {"Country name": "Bangladesh", "2023": 2023, "36th": "41st"}, {"Country name": "Equatorial Guinea", "2023": 2023, "36th": "42nd"}, {"Country name": "Malawi", "2023": 2023, "36th": "43rd"}, {"Country name": "Rwanda", "2023": 2023, "36th": "44th"}, {"Country name": "Comoros", "2023": 2023, "36th": "45th"}, {"Country name": "Djibouti", "2023": 2023, "36th": "46th"}, {"Country name": "Togo", "2023": 2023, "36th": "47th"}, {"Country name": "Zambia", "2023": 2023, "36th": "48th"}, {"Country name": "Mauritania", "2023": 2023, "36th": "36th"}, {"Country name": "North Korea", "2023": 2023, "36th": "37th"}, {"Country name": "Angola", "2023": 2023, "36th": "38th"}, {"Country name": "Iran", "2023": 2023, "36th": "39th"}]

Using openpyxl Module

In this example, the openpyxl library is employed to load an Excel workbook named "student.xlsx," focusing on the sheet named "suraj1." The script iterates through the rows and columns, extracts data, and organizes it into a list of dictionaries. Finally, the extracted data is converted to JSON format using json.dumps() and printed to the console with print(json_data).

from openpyxl import load_workbook
from json import dumps

# Load Excel workbook
wb = load_workbook("student.xlsx")

# Choose a specific sheet
sheet = wb["suraj1"]

# Find the number of rows and columns in the sheet
rows = sheet.max_row
columns = sheet.max_column

# List to store all rows as dictionaries
lst = []

# Iterate over rows and columns to extract data
for i in range(1, rows):
    row = {}
    for j in range(1, columns):
        column_name = sheet.cell(row=1, column=j)
        row_data = sheet.cell(row=i+1, column=j)

        row.update(
            {
                column_name.value: row_data.value
            }
        )
    lst.append(row)

# Convert extracted data into JSON format
json_data = dumps(lst)

# Print the JSON data
print(json_data)

Output:

[{"Country name": "Mauritania", "2023": 2023, "36th": "37th"}, {"Country name": "North Korea", "2023": 2023, "36th": "38th"}, {"Country name": "Angola", "2023": 2023, "36th": "39th"}, {"Country name": "Iran", "2023": 2023, "36th": "40th"}, {"Country name": "Bangladesh", "2023": 2023, "36th": "41st"}, {"Country name": "Equatorial Guinea", "2023": 2023, "36th": "42nd"}, {"Country name": "Malawi", "2023": 2023, "36th": "43rd"}, {"Country name": "Rwanda", "2023": 2023, "36th": "44th"}, {"Country name": "Comoros", "2023": 2023, "36th": "45th"}, {"Country name": "Djibouti", "2023": 2023, "36th": "46th"}, {"Country name": "Togo", "2023": 2023, "36th": "47th"}, {"Country name": "Zambia", "2023": 2023, "36th": "48th"}, {"Country name": "Mauritania", "2023": 2023, "36th": "36th"}, {"Country name": "North Korea", "2023": 2023, "36th": "37th"}, {"Country name": "Angola", "2023": 2023, "36th": "38th"}, {"Country name": "Iran", "2023": 2023, "36th": "39th"}]

Using excel2json Library

This is a Python library that converts an Excel file to JSON format. This is a simple way to convert an Excel file to JSON.

pip install excel2json

In this example, the excel2json library is used to simplify the process of converting an Excel file ("student.xls") to JSON. The convert_from_file function is invoked to perform the conversion, providing a convenient way to automate the task without manually handling the intricacies of Excel-to-JSON conversion.

from excel2json import convert_from_file

# Convert Excel file to JSON
convert_from_file("student.xls")

Output:

[{"Country name": "Mauritania", "2023": 2023, "36th": "37th"}, {"Country name": "North Korea", "2023": 2023, "36th": "38th"}, {"Country name": "Angola", "2023": 2023, "36th": "39th"}, {"Country name": "Iran", "2023": 2023, "36th": "40th"}, {"Country name": "Bangladesh", "2023": 2023, "36th": "41st"}, {"Country name": "Equatorial Guinea", "2023": 2023, "36th": "42nd"}, {"Country name": "Malawi", "2023": 2023, "36th": "43rd"}, {"Country name": "Rwanda", "2023": 2023, "36th": "44th"}, {"Country name": "Comoros", "2023": 2023, "36th": "45th"}, {"Country name": "Djibouti", "2023": 2023, "36th": "46th"}, {"Country name": "Togo", "2023": 2023, "36th": "47th"}, {"Country name": "Zambia", "2023": 2023, "36th": "48th"}, {"Country name": "Mauritania", "2023": 2023, "36th": "36th"}, {"Country name": "North Korea", "2023": 2023, "36th": "37th"}, {"Country name": "Angola", "2023": 2023, "36th": "38th"}, {"Country name": "Iran", "2023": 2023, "36th": "39th"}]