Converting Excel into JSON using Python

Question

I have an excel file and I want to convert it into a JSON file. So the excel is something like this:

-------------------------
| Col A | Col C | Col F |
--------+-------+--------
|   1   |   A   |   EE  |
|   2   |   B   |   FF  |
|   4   |   C   |   FF  |
|   5   |   D   |   HH  |
|   6   |   D   |   HH  |
|   7   |   A   |   EE  |
|   8   |   E   |   EE  |
--------------------------

I would like the JSON to follow this format:

{
"EE": {
    "A": {
      "Col A key": "1",
      "Col A key": "7"
    },
    "E": {
      "Col A key": "8"
    },
  },

"FF": {
    "B": {
      "Col A key": "2"
    },
    "C": {
      "Col A key": "4"
    }
  },

"HH": {
    "D": {
      "Col A key": "5",
      "Col A key": "6"
    }
  }

}

Can anyone help me implement this using python? I've tried various ways but without success. This is what I have done so far:


import openpyxl, pprint, json
print('Opening workbook...')
wb = openpyxl.load_workbook('excel_form.xlsx')
sheet = wb.get_sheet_by_name('Sheet')


excel_data = {}
print('Reading rows...')
for row in range(2, sheet.max_row + 1):
    Col F  = sheet['F' + str(row)].value
    Col C = sheet['C' + str(row)].value
    Col A = sheet['A' + str(row)].value

    excel_data.setdefault(Col F, {})
    excel_data[Col F].setdefault(Col C, {'Col A': Col A})


# Open a new text file and write the contents of excel_data to it.
print('Writing results...')
with open('DATA.json', 'w') as resultFile:
    json.dump(Matrix, resultFile)
print('Done.')

Thanks in advance

How about this previous answer: stackoverflow.com/questions/53940031/… — Plato77
– Plato77, Commented Feb 3, 2020 at 17:28
Imo you should consider using the simplejson package instead of trying to reinvent the wheel. — Massimo Di Saggio
– Massimo Di Saggio, Commented Feb 3, 2020 at 17:35

Born Ready · Accepted Answer · 2022-02-25 17:33:11Z

20

Went through a couple of solutions, this is the one that worked best for me. Hope this can save someone else some time.

import pandas
import json

# Read excel document
excel_data_df = pandas.read_excel('data.xlsx', sheet_name='sheet1')

# Convert excel to string 
# (define orientation of document in this case from up to down)
thisisjson = excel_data_df.to_json(orient='records')

# Print out the result
print('Excel Sheet to JSON:\n', thisisjson)

# Make the string into a list to be able to input in to a JSON-file
thisisjson_dict = json.loads(thisisjson)

# Define file to write to and 'w' for write option -> json.dump() 
# defining the list to write from and file to write to
with open('data.json', 'w') as json_file:
    json.dump(thisisjson_dict, json_file)

edited Feb 25, 2022 at 17:33

Born Ready

3,6963 gold badges30 silver badges31 bronze badges

answered Apr 8, 2021 at 9:28

Vik

2132 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

s.bramblet Over a year ago

NameError: name 'json' is not defined

Michael Behrens Over a year ago

Worked for me after I changed 'sheet1' to 'Sheet1', as in my case, the default sheet name had a capital S. I also had to install two packages with pip3: pandas and openpyxl.

Cătălina Sîrbu · Accepted Answer · 2020-03-31 09:20:25Z

10

I prefer using xlrd to convert the Excel rows into a JSON format.

import xlrd
from collections import OrderedDict
import json

Open the workbook and select the first worksheet

wb = xlrd.open_workbook("Excel-sheet location here")
sh = wb.sheet_by_index(0)

Create a list to hold dictionaries

data_list = []

Iterate through each row in worksheet and fetch values into dict

for rownum in range(1, sh.nrows):
     data = OrderedDict()

row_values = sh.row_values(rownum)
data['<Column Name1>'] = row_values[0]
data['<Column Name2>'] = row_values[1]
data_list.append(data)

Write to file:

 with open("RulesJson.json", "w", encoding="utf-8") as writeJsonfile:
      json.dump(data_list, writeJsonfile, indent=4,default=str)

edited Mar 31, 2020 at 9:20

Cătălina Sîrbu

1,28316 silver badges36 bronze badges

answered Mar 31, 2020 at 6:55

Prasad Nadiger

3933 silver badges14 bronze badges

Comments

Cătălina Sîrbu · Accepted Answer · 2020-03-31 19:30:01Z

6

There are 2 approaches for achieving the result:

Using excel2json. It's quite a simple tool but might be helpful for you.
- First, install the package excel2json-3 using pip.
- Then, running this code block should output a JSON file for each sheet in the file:

import excel2json

excel2json.convert_from_file('excel_form.xlsx')

Using pandas. If you are looking for a more comprehensive solution, you might as well find pandas useful. It is a library made for data manipulation and has many more features.
- First install pandas via pip.
- Then, running this code should result with printing a JSON string describing the excel sheet called Sheet.

import pandas

excel_data_fragment = pandas.read_excel('excel_form.xlsx', sheet_name='Sheet')

json_str = excel_data_fragment.to_json()

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

Link to the source

edited Mar 31, 2020 at 19:30

Cătălina Sîrbu

1,28316 silver badges36 bronze badges

answered Feb 3, 2020 at 17:39

RVIDAN

14010 bronze badges

3 Comments

muthu Over a year ago

It thows AttributeError: module 'excel2json' has no attribute 'convert_from_file' error

aydow Over a year ago

pandas is a bit of overkill if you just want to read in the data. if you want to transform the data in some way that leverages pandas, then that would be more ideal.

bfontaine Over a year ago

Note that excel2json has been unmaintained since 2018.

Collectives™ on Stack Overflow

Converting Excel into JSON using Python

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related