Tutorial

Python Excel to JSON Conversion

Published on August 3, 2022
author

Pankaj

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

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products


About the authors
Default avatar
Pankaj

author

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
JournalDev
DigitalOcean Employee
DigitalOcean Employee badge
February 11, 2022

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

    JournalDev
    DigitalOcean Employee
    DigitalOcean Employee badge
    February 4, 2022

    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

      JournalDev
      DigitalOcean Employee
      DigitalOcean Employee badge
      September 13, 2021

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

      - Rupsha G

        JournalDev
        DigitalOcean Employee
        DigitalOcean Employee badge
        September 26, 2020

        It works for me. Thank you!

        - Trieu Vu

          JournalDev
          DigitalOcean Employee
          DigitalOcean Employee badge
          April 22, 2020

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

          - Domanskiy Alexandr

            Try DigitalOcean for free

            Click below to sign up and get $200 of credit to try our products over 60 days!

            Sign up

            Join the Tech Talk
            Success! Thank you! Please check your email for further details.

            Please complete your information!

            Featured on Community

            Get our biweekly newsletter

            Sign up for Infrastructure as a Newsletter.

            Hollie's Hub for Good

            Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

            Become a contributor

            Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

            Welcome to the developer cloud

            DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

            Learn more
            DigitalOcean Cloud Control Panel