Question 110154 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Convert excel file (.xlsx) to json

I have a xlsx file

Country name Country code
IN India
SL Sri Lanka

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"
       }
Tags:

2

Answers


  1. 0

    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'.
    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search