// Tutorial //

Python Excel to JSON Conversion

Published on August 3, 2022
Default avatar
By Pankaj
Developer and author at DigitalOcean.
Python Excel to JSON Conversion

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

There are many ways to convert an excel file to JSON data. In this tutorial, we will look into two python modules to convert excel files to JSON.

  1. excel2json-3
  2. Pandas

Converting Excel File to JSON Files using excel2json-3 Module

It’s a very simple module to convert excel files to JSON files. The contents from the excel sheet are converted to JSON string and saved in a file. The name of the files is derived from the excel sheet names. So, if there are two sheets with the name “Numbers” and “Cars”, the JSON files will be named as Numbers.json and Cars.json respectively. The module supports both .xls and .xlsx file formats for conversion. We can read the excel file from the file system as well as the URL. We can install this module using the PIP command.

$ pip install excel2json-3

For our example, I have created an excel file named “records.xlsx” having three sheets. Python Excel File To Json Example Here is the script to convert this excel file to JSON files.

import excel2json

excel2json.convert_from_file('records.xlsx')

The script creates three JSON files. Employees.json

[
    {
        "EmpID": 1.0,
        "EmpName": "Pankaj",
        "EmpRole": "CEO"
    },
    {
        "EmpID": 2.0,
        "EmpName": "David Lee",
        "EmpRole": "Editor"
    },
    {
        "EmpID": 3.0,
        "EmpName": "Lisa Ray",
        "EmpRole": "Author"
    }
]

Cars.json

[
    {
        "Car Name": "Honda City",
        "Car Model": "City",
        "Car Maker": "Honda",
        "Car Price": "20,000 USD"
    },
    {
        "Car Name": "Bugatti Chiron",
        "Car Model": "Chiron",
        "Car Maker": "Bugatti",
        "Car Price": "3 Million USD"
    },
    {
        "Car Name": "Ferrari 458",
        "Car Model": 458.0,
        "Car Maker": "Ferrari",
        "Car Price": "2,30,000 USD"
    }
]

Numbers.json

[
    {
        "1.0": 3.0,
        "2.0": 4.0
    },
    {
        "1.0": "N1",
        "2.0": "N2"
    },
    {
        "1.0": 5.0,
        "2.0": 6.0
    },
    {
        "1.0": 7.0,
        "2.0": 8.0
    }
]

If you have to read the excel file from a URL, use convert_from_url() function.

Limitations of excel2json-3 module

  • The plugin has very limited features.
  • There are no options to skip any sheet, rows, and columns. This makes it hard to use with bigger excel files.
  • The JSON is saved into files. Most of the times, we want to convert to JSON and use it in our program rather than saving it as a file.
  • The integers are getting converted to the floating point numbers.

Converting Excel Sheet to JSON String using Pandas Module

Pandas module provides functions to read excel sheets into DataFrame object. There are many options to specify headers, read specific columns, skip rows, etc. You can read more about it at Pandas read_excel() – Reading Excel File in Python. We can use the to_json() function to convert the DataFrame object to JSON string. Let’s look at a simple example to read the “Employees” sheet and convert it to JSON string.

import pandas

excel_data_df = pandas.read_excel('records.xlsx', sheet_name='Employees')

json_str = excel_data_df.to_json()

print('Excel Sheet to JSON:\n', json_str)

Output:

Excel Sheet to JSON:
 {"EmpID":{"0":1,"1":2,"2":3},"EmpName":{"0":"Pankaj","1":"David Lee","2":"Lisa Ray"},"EmpRole":{"0":"CEO","1":"Editor","2":"Author"}}

So, the JSON data is created with the orientation of columns. If you want to create the JSON string with row-wise orientation, pass the “orient” parameter value as “records”.

json_str = excel_data_df.to_json(orient='records')

Output:

Excel Sheet to JSON:
 [{"EmpID":1,"EmpName":"Pankaj","EmpRole":"CEO"},{"EmpID":2,"EmpName":"David Lee","EmpRole":"Editor"},{"EmpID":3,"EmpName":"Lisa Ray","EmpRole":"Author"}]

Conclusion

If you have a simple and well-structured excel file and you want to convert it to JSON files, use the excel2json-3 module. But, if you want more control on the way the excel data is read and converted to JSON string, use the pandas’ module.

References


Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.

Sign up
About the authors
Default avatar
Pankaj

author

Developer and author at DigitalOcean.

Still looking for an answer?

Was this helpful?

Any advice on handling accented character in the Excel sheet while using excel2json-3? Right now the output is showing unicode characters instead of the accented characters. e.g \u00ed instead of “í”

- Mike G

    I keep getting “raise ValueError(f"Worksheet named ‘{name}’ not found”) I’ve triple checked the spelling for the sheet_name. Not sure what’s going on.

    - RichterRights

      This is why python is such a darling 😁 Most of the things can be done without big codes

      - Rupsha G

        It works for me. Thank you!

        - Trieu Vu

          How do I get a dictionary of all named cells and their values?

          - Domanskiy Alexandr