Convert Excel to JSON using Python

In this tutorial, we are going to see how to convert an Excel file to JSON format using Python. pandas is a Python library used to accomplish this task. The pandas library is used for data analytics.

First of all, we need to install the Pandas library:

! pip install pandas

Now, import the libraries to use in the code.

import pandas
import json

Note: json is a built-in module in Python so no need to install it again.

Using the read_excel() function from the pandas library read the excel file. Pass the file location path as an argument. sheet_name is an attribute used to select the excel sheet from the workbook. [The excel file must have the extension .xlsx or .xls ]

Suppose we have an IRIS dataset.

Excel = pandas.read_excel('Jupyter\IRIS.xlsx', sheet_name='Worksheet')

to_json( ) function

to_json() is a function from the pandas library used to convert the object to JSON string.
orient is a parameter of the function which specifies the JSON string format. There are various JSON string formats eg. values, records, index, table, split, etc. ‘index’ is the default JSON string format.

Json = Excel.to_json(orient='records')

Finally, print the converted JSON string.

print(Json)

Output:

[{"SL":5.1,"SW":3.5,"PL":1.4,"PW":0.1,"SPECIES":"Iris-versicolor"},
{"SL":4.9,"SW":3.0,"PL":1.4,"PW":0.2,"SPECIES" :"Iris-verginica"},
{"SL":4.7,"SW":3.2,"PL":1.3,"PW":0.1,"SPECIES":"Iris-setosa"},
{"SL":4.6,"SW":3.1,"PL":1.5,"PW":0.2,"SPECIES":"Iris-versicolor"},
{"SL":5.0,"SW":3.4,"PL":1.4,"PW":0.2,"SPECIES":"Iris-versicolor"},
{"SL":5.4,"SW":3.9,"PL":1.7,"PW":0.4,"SPECIES":"Iris-setosa"},
{"SL":4.6,"SW":3.4,"PL":1.4,"PW":0.4,"SPECIES":"Iris-verginica"},
{"SL":5.0,"SW":3.4,"PL":1.5,"PW":0.2,"SPECIES":"Iris-setosa"},
{"SL":4.4,"SW":3.2,"PL":1.4,"PW":0.3,"SPECIES":"Iris-setosa"}]

Now save the created JSON file.
Open the ‘JSON.json’ file in write mode to add content to it.
Note: open function creates the new file at a given location and opens it in specified read or write mode.

out_file = open("F:\Aakanksha\Jupyter\JSON.json", "w")

dump( ) function

dump() function converts the Python object into a desired JSON object. You must pass the JSON string and output file you have opened before.
indent is a function parameter that improves the readability of JSON files.

json.dump(Json, out_file, indent = 6)

Now close the opened file.

out_file.close()

Created JSON file:

Also see, How to convert XML to JSON in Python