Json to Excel python script

Question

I have a API which returns JSON response every single time whenever called, Response example:-

{
"data": [
         {"id":1,"name":"A","class":"AA" },
         {"id":2,"name":"B","class":"BB" },
         {"id":3,"name":"C","class":"CC" },
        ]
}

I want to create a Excel file from the received response with selected fields in this case just id and name. Can anyone please tell me how I can do this? Thanks.

If you Google "python excel", you get dozens of options. You can also just use the Python built-in csv library. — Grismar
– Grismar, Commented Feb 4, 2020 at 6:13
Thank you for your response @Grismar i have tried many ways but couldn't able to convert it that's why i have asked this question here. — Newton8989
– Newton8989, Commented Feb 4, 2020 at 6:18
Really? You have to try really hard to miss this: docs.python.org/3/library/csv.html and this isn't very hard to find either docs.python.org/3/library/json.html — Grismar
– Grismar, Commented Feb 4, 2020 at 6:20
@Grismar i have created successfully created CSV from json but unable to create Excel directly from it. — Newton8989
– Newton8989, Commented Feb 4, 2020 at 6:24
Have a read openpyxl.readthedocs.io/en/stable (or xlsxwriter, or xlwt, or any of many libraries - which is the best is not a question to ask on StackOverflow) — Grismar
– Grismar, Commented Feb 4, 2020 at 6:39

Spot135 · Accepted Answer · 2020-02-04 06:38:04Z

1

You want to convert the JSON data into an dict in python and then convert the data in to csv format (which can be read by excel).

import json
import csv

api = "{"data": [
          {"id":1,"name":"A","class":"AA" },
          {"id":2,"name":"B","class":"BB" },
          {"id":3,"name":"C","class":"CC" },
         ]
       }"

data = json.load(api)

f = open('data.csv')
csv_file = csv.writer(f)
for item in data['data']:
    f.writerow(item['id'] + ', ' . item['name']) 
f.close()

To answer your question, you could try pandas.

import json
import pandas as pd

api = "{"data": [
          {"id":1,"name":"A","class":"AA" },
          {"id":2,"name":"B","class":"BB" },
          {"id":3,"name":"C","class":"CC" },
         ]
       }"

data = json.load(api)
excel_data = pd.DataFrame.from_dict(data['data'])

# Then convert to excel xls or csv files
excel_data.to_csv('file.csv')
excel_data.to_excel('file.xls')

edited Feb 4, 2020 at 6:38

answered Feb 4, 2020 at 6:19

Spot135

1575 bronze badges

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

1 Comment

Newton8989 Over a year ago

Hi @Spot135 Thanks for your response I have already done this is there any other way threw which i can directly convert json to Excel because the data-set is to large and json to csv,and then csv to excel is consuming too much time.

Collectives™ on Stack Overflow

Json to Excel python script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related