How to convert Excel file to json using pandas?

Question

I would like to parse Excel file which has a couple of sheets and save a data in JSON file.

I don't want to parse first and second sheet, and also the last one. I want to parse those in between and that number of sheets is not always equal and names of those sheets are not always the same.

import pandas
import json

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

Is there a way not to put this parameter sheet_name?

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

Ryan · Accepted Answer · 2022-04-11 19:06:16Z

2

I would just create a sheet level dict and loop through each of the sheets. Something like this:

import pandas
import json

sheets = ['sheet1','sheet2','sheet3']
output = dict()
# Read excel document
for sheet in sheets:
    excel_data_df = pandas.read_excel('data.xlsx', sheet_name=sheet)

# 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)
    output[sheet] = thisisjson_dict
# 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(output, json_file)

answered Apr 11, 2022 at 19:06

Ryan

3093 silver badges12 bronze badges

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

2 Comments

user18292038 Over a year ago

Is there a way to collect names of all of the sheets and put it in a list?

Ryan Over a year ago

if you don't enter a sheetname pandas will parse all of the sheets in the file and return a list of dataframes. You could just iterate over that list instead.

Collectives™ on Stack Overflow

How to convert Excel file to json using pandas?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related