Writing API Results to CSV in Python

Question

I am looking for some assistance with writing API results to a .CSV file using Python. At this point, I'm successfully writing to .CSV, but I cannot seem to nail down the code behind the .CSV format I'm looking for, which is the standard one field = one column format.

Any help is appreciated! Details are below. Thanks!

My code:

import requests
import json
import csv

urlcomp = 'http://url_ommitted/api/reports/completion?status=COMPLETED&from=2016-06-01&to=2016-08-06'
headers = {'authorization': "Basic API Key Ommitted", 'accept': "application/json", 'accept': "text/csv"}

## API Call to retrieve report
rcomp = requests.get(urlcomp, headers=headers)

## API Results
data = rcomp.text

## Write API Results to CSV
with open('C:\_Python\\testCompletionReport.csv', "wb") as csvFile:
    writer = csv.writer(csvFile, delimiter=',')
    for line in data:
        writer.writerow(line)

The code above creates a .CSV with the correct output, but it's writing each character from the API results into a new cell in Column A of the output file. Screenshot below:

I've also attempted the code below, which writes the entire API result set into a single cell in the .CSV output file.

Code:

data = rcomp.text

with open('C:\_Python\\CompletionReportOutput.csv', 'wb') as csvFile:
    writer = csv.writer(csvFile, delimiter = ',')
    writer.writerow([data])

Output:

Below is a screenshot of some sample API result data returned from my call:

Example of what I'm looking for in the final .CSV output file:

EDIT - Sample API Response:

"PACKAGE CREATED","PACKAGE ID","PACKAGE NAME","PACKAGE STATUS","PACKAGE TRASHED","PACKAGE UPDATED","SENDER ID","SENDER NAME","SENDER COMPANY","SENDER CREATED","SENDER EMAIL","SENDER FIRSTNAME","SENDER LANGUAGE","SENDER LASTNAME","SENDER PHONE","SENDER TITLE","SENDER UPDATED","SENDER ACTIVATED","SENDER LOCKED","SENDER STATUS","SENDER TYPE" "Thu Aug 04 14:52:57 CDT 2016","ulw5MTQo8WjBfoCTKqz9LNCFpV4=","TestOne to TestTwo - Flatten PDF Removed","COMPLETED","false","Thu Aug 04 14:53:30 CDT 2016","tKpohv2kZ2oU","","","2016-08-03 14:12:06.904","[email protected]","John","en","Smith","","","2016-08-03 14:12:06.942118","null","null","INVITED","REGULAR" "Thu Aug 04 09:39:22 CDT 2016","IJV3U_yjPlxS-TVQgMrNgVUUSss=","TestOne to TestTwo - Email Test","COMPLETED","false","Thu Aug 04 10:11:29 CDT 2016","tKpohv2kZ2oU","","","2016-08-03 14:12:06.904","[email protected]","John","en","Smith","","","2016-08-03 14:12:06.942118","null","null","INVITED","REGULAR"

SECOND EDIT - Output from Lee's suggestion:

Recoil

I think for the german version of excel (not sure) the separator is ; - possibly different for other languages. – Lumenhour

Please add the actual API response directly into the question instead of a screenshot; nobody can test anything to help you. Does the API not send json in response which you're converting? At a glance, I don't see how you could reasonably separate out the newlines in the screenshot response and be sure that you have the correct data going into the correct columns, they're different lengths - surely the raw structure is more sensible? – Marjoriemarjory

@Marjoriemarjory I've added the csv/text API response. I'm assuming that is what you're looking for? Or do you need to see the response in JSON format? Thanks – Recoil

I'm trying to copy/paste into txt but I can't replicate what I think is just \n split being added in data = rcomp.text. I think data = recomp.json() will make this much easier to split out :) – Marjoriemarjory

Answer 1

So, I eventually stumbled onto a solution. Not sure if this is the "correct" way of handling this, but the code below wrote the API results directly into a .CSV with the correct column formatting.

# Get JSON Data
rcomp = requests.get(urlcomp, headers=headers)

# Write to .CSV
f = open('C:\_Python\Two\\newfile.csv', "w")
f.write(rcomp.text)
f.close()

Recoil

Answer 2

csvFile = open('C:\_Python\\CompletionReportOutput.csv', 'w')

writer = csv.writer(csvFile, delimiter = ' ')

for row in data.split('\n'):
    writer.writerow(row)

Gensmer

I doubt that this alone would solve the issue. You'd then need to split the row surely? Without the API response, I don't think you can answer this – Marjoriemarjory

This definitely moved it in the right direction. The fields are writing to the correct columns, but there are some spacing and " character issues. Output added to original question. Thanks! – Recoil

Can you try and use writer.writerow(row.replace('"','')) – Eubank

Answer 3

import requests
import base64
import json

base_url = "http://dummy.restapiexample.com/api/v1/employees";

def get_report(base_url):
    print("Getting report results...")
    header_gs = {'Accept': 'application/json'}
    r = requests.get(base_url)
    if r.ok:
        print("Report results received...")
        print("HTTP %i - %s" % (r.status_code, r.reason))
        return r.text
    else:
        print("HTTP %i - %s" % (r.status_code, r.reason))

def export_to_json(base_url):
    print("Exporting report results to JSON file...")
    r = get_report(base_url)
    text_file = open("report_results.json", "w", encoding="utf8")
    text_file.write(r)
    text_file.close()

def export_to_csv(base_url):
    print("Exporting report results to JSON file...")

    csv_file = open('report_results.csv', "w", encoding="utf8")
    csv_file.write("Id, Employee_Name"+"\n") #manually modify this CSV file header
    csv_file.close()

    #there are 3 attributes in my example; add/remove levels according to the number of attributes in your case
    r = get_report(base_url)
    report_parsed = json.loads(r)
    print(report_parsed)
    a1_list = report_parsed['data']
    print(a1_list)
    for a1 in a1_list:
        a1_id = a1['id']
        a2_employee_name=a1['employee_name']
        csv_file = open('report_results.csv', "a", encoding="utf8")
        csv_file.write("'"+a1_id + "','" + a2_employee_name +"\n")
        csv_file.close()


    print("Export finished")

def main():
    choice = None
    while choice != "0":
        print \
            ("""
        ---MENU---
        
        0 - Exit
        1 - Export report results to JSON file
        2 - Export report results to CSV file
        """)

        choice = input("Your choice: ") # What To Do ???
        print()

        if choice == "0":
            print("Good bye!")
        elif choice == "1":
            export_to_json(base_url)
        elif choice == "2":
            export_to_csv(base_url)
        else:
            print(" ### Wrong option ### ")

### Main program
main()

Cachepot

I think some explanation for executed changes is needed. – Electrotechnology