Convert Excel To Json With Python
Last Updated :
27 Feb, 2024
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:
- Using to_json() Function
- Using openpyxl Module
- 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)
.
Python3
import pandas as pd
import json
data = pd.read_excel( "student.xlsx" , sheet_name = "suraj1" )
json_data = data.to_json()
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)
.
Python3
from openpyxl import load_workbook
from json import dumps
wb = load_workbook( "student.xlsx" )
sheet = wb[ "suraj1" ]
rows = sheet.max_row
columns = sheet.max_column
lst = []
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)
json_data = dumps(lst)
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.
Python3
from excel2json import convert_from_file
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”}]
Share your thoughts in the comments
Please Login to comment...