CODEWITHSUNDEEP
jsonpython-3.xcsv
Gary Glasspool
Gary Glasspool

Reputation: 87

How do I write json content to csv file?

I am using an api call to request data, which returns JSON. I need to save this response to a CSV file.

I am able to pull down the JSON response, but am having problems with writing to CSV.

# import statements
import json         # package to work with json
import requests     # package to make http requests
import csv

# set global variables
group_id = 0000
api_token = 'yyyy'
api_url = 'https://api.samsara.com/v1'

endpoint_url = api_url + '/fleet/drivers'

# query params for the request
my_params = {"access_token": api_token}

# body data to send with the request
my_data = {"groupId": group_id}

# send POST request to endpoint
resp = requests.post(url = endpoint_url, params = my_params, json = my_data)

# pull out the json
array = resp.json()
text = json.dumps(array)

csvFile = open('/tmp/output.csv','w')
csvwriter = csv.writer(csvFile)
count = 0
for line in text:
    if count == 0:
        header = line.keys()
        csvwriter.writerow(header)
        count += 1
    csvwriter.writerow(line.values())
csvFile.close()

When I run the code above, I get the following error:

Traceback (most recent call last): File "get_driver_test4.py", line 34, in header = line.keys() AttributeError: 'str' object has no attribute 'keys'

Upvotes: 0

Views: 3170

Answers (2)

marsnebulasoup
marsnebulasoup

Reputation: 2660

Ok what about this:

# import statements
import json         # package to work with json
import requests     # package to make http requests
import csv
resp = """
{
    "drivers":[
        {
            "id":134763,
            "groupId":1234,
            "vehicleId":212014918234742,
            "currentVehicleId":212014918234742,
            "username":"ablah",
            "name":"Art Blah"
        },
        {
            "id":134764,
            "groupId":1234,
            "vehicleId":212014918234709,
            "currentVehicleId":212014918234709,
            "username":"btodd",
            "name":"Barry Todd"
        }
    ]
}
"""
text = json.loads(resp)
csvFile = open('output.csv','w')
csvwriter = csv.writer(csvFile, delimiter=',')
line = text["drivers"]
csvwriter.writerow(["id","groupId","vehicleId","currentVehicleId","username","name"])
for l in line:
    csvwriter.writerow([l["id"],l["groupId"],l["vehicleId"],l["currentVehicleId"],l["username"],l["name"]])
    
    
csvFile.close()

You can adjust the code to fit your needs.

Output.csv

id,groupId,vehicleId,currentVehicleId,username,name

134763,1234,212014918234742,212014918234742,ablah,Art Blah

134764,1234,212014918234709,212014918234709,btodd,Barry Todd

By the way...

In the sample json you gave, the groupId 0000 throws an error in python, as it's not a valid number. Consider making 0000 a string "0000"

More references...

https://docs.python.org/3/library/csv.html

Upvotes: 0

zedfoxus
zedfoxus

Reputation: 37069

Break down your problem into different functions like so:

import json
import requests
import csv

def get_data():
    group_id = 0000
    api_token = 'yyyy'
    api_url = 'https://api.samsara.com/v1'
    endpoint_url = api_url + '/fleet/drivers'

    my_params = {"access_token": api_token}
    my_data = {"groupId": group_id}
    resp = requests.post(url = endpoint_url, params = my_params, json = my_data)
    array = resp.json()
    text = json.dumps(array)

    return text


def write_file(filename, text):
    dataset = json.loads(text)
    drivers = dataset['drivers']

    csvFile = open(filename,'w')
    csvwriter = csv.writer(csvFile)

    # write header
    if len(drivers) > 0:
        keys = drivers[0].keys()
        csvwriter.writerow(keys)

    # write data
    for line in drivers:
       csvwriter.writerow(line.values())

    csvFile.close()

text = get_data()
write_file('output.csv', text)

That way, you can test your write_file function separate from your get_data function.

One thing to be careful about is GroupID of 0000. If you have group id typed like that, you may get a JSON parsing error. If you really want 4 character numbers, find a way to quote them like "0000". If you are just interested in number, replace 0000 with 0.

To test your theory, at the bottom of the python file just use something like this:

text = '{"drivers": [{"id": 134763, "groupId": "0000", "vehicleId": 212014918234742, "currentVehicleId": 212014918234742, "username": "ablah", "name": "Art Blah"}, {"id": 134764, "groupId": "0000", "vehicleId": 212014918234709, "currentVehicleId": 212014918234709, "username": "btodd", "name": "Barry Todd"}]}'
write_file('output.csv', text)

Resulting file

id,groupId,vehicleId,currentVehicleId,username,name
134763,0000,212014918234742,212014918234742,ablah,Art Blah
134764,0000,212014918234709,212014918234709,btodd,Barry Todd

Upvotes: 2

Related Questions