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)
Advertisement
Answer
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)