I have a xlsx file

I want to convert this to a json in the format

json = {
       {"Name":"India",
        "Code":"IN"},
       {"Name":"Sri Lanka",
        "Code":"SL"}
       }

I tried load the excel file using the pandas and convert them to json but i am getting

json = {
       "India":"IN",
        "Sri Lanka":"SL"
       }

CodePudding user response：

You could use pandas to accomplish this. First you can read the xlsx file into a DataFrame using pandas.read_excel

import pandas as pd
df = pd.read_excel('your_sheet.xlsx')

then you can write that DataFrame back out as json using pandas.DataFrame.to_json

df.to_json('your_data.json', orient='records')

or if you want it as a json string instead of writing to file

json_data = df.to_json(orient='records')

Note that to achieve the output format you described you want to pass orient='records'

The format of the JSON string:

• ‘split’ : dict like {‘index’ -> [index], ‘columns’ -> [columns], >- ‘data’ -> [values]}
• ‘records’ : list like [{column -> value}, … , {column -> value}]
• ‘index’ : dict like {index -> {column -> value}}
• ‘columns’ : dict like {column -> {index -> value}}
• ‘values’ : just the values array
• ‘table’ : dict like {‘schema’: {schema}, ‘data’: {data}} Describing the data, where data component is like orient='records'.

CodePudding user response：

try: df.to_json(orient="records")