CODEWITHSUNDEEP
pythonjsonexcelparsingdictionary
Raj
Raj

Reputation: 351

Parse JSON to Excel - Python

I'm trying to query Azure AD for some data which returns JSON. I want to take some part of this data and fill up an excel sheet. I've checked many examples which dumps all the data from JSON to an excel sheet (using xlwt), but how do I do this for only some part of JSON data?

Here's the script that I'm using:

import requests 

def get_application_list():
    application_list_response = requests.get("https://graph.microsoft.com/beta/applications", verify=False,
                                             headers={"Authorization": "Bearer" + access_token})

    application_list_response_json = application_list_response.json()

    for item in application_list_response_json['value']:
        print("Application Name:", item['displayName'])
        print("Application ID:", item['id'])

get_application_list()

I would like to get the Application name and the application id in the excel sheet. Sample output:

enter image description here

P.S: I'm very new to Python. Any suggestion to optimize this code would also be helpful.

Thanks!

Upvotes: 0

Views: 4292

Answers (1)

Philip Tzou
Philip Tzou

Reputation: 6438

Just save it as a CSV file and then you can open it in M$ Excel.

import csv
import requests 

def save_application_list(target_file):
    application_list_response = requests.get("https://graph.microsoft.com/beta/applications", verify=False,
                                             headers={"Authorization": "Bearer" + access_token})

    application_list_response_json = application_list_response.json()

    with open(target_file, 'w') as fp:
        writer = csv.DictWriter(fp, ['displayName', 'id'])
        writer.writeheader()
        writer.writerows(application_list_response_json['value'])

save_application_list('/path/to/your/saved/file.csv')

Update:

To change the field names to ['Application Name', 'Application ID'], just change the writer.writeheader() to writerow() like this:

def save_application_list(target_file):

    ...
    with open(target_file, 'w') as fp:
        writer = csv.DictWriter(fp, ['displayName', 'id'])
        writer.writerow({
            'displayName': 'Application Name',
            'id': 'Application ID'
        })
        writer.writerows(application_list_response_json['value'])
    ...

Update 2:

Since you have other fields in the JSON file which were no need to keep, the code can be something like this:

def save_application_list(target_file):

    ...
    with open(target_file, 'w') as fp:
        writer = csv.DictWriter(fp, ['Application Name', 'Application ID'])
        writer.writeheader()
        writer.writerows({
            'Application Name': item['displayName'],
            'Application ID': item['id']
        } for item in application_list_response_json['value'])

    ...

Upvotes: 1

Related Questions