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 # 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)
.
Python3
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.
Python3
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”}]
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)