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)

👁 sheet

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).

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 openpyxllibrary 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).

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.

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"}]