How to save Python Json output as a CSV file using modules

Question

I'm trying to get the data output I have, saved as an xlsm or csv file, but I don't grasp how I do that. The code include one of my attempts

import requests
import xlsxwriter

BASE_URL = 'https://restapi.e-conomic.com/'
HEADERS = {
  'X-AgreementGrantToken': 'demo',
  'X-AppSecretToken': 'demo',
  'Content-type': 'application/json'
}
def get_invoice():
  url = "{0}/{1}".format(BASE_URL, 'invoices/booked')
  resp = requests.get(url, headers=HEADERS)
  print(resp)
  print(resp.json())
  workbook = xlsxwriter.Workbook('demo1.xlsx')
  worksheet = workbook.add_worksheet()
  worksheet.write(1, 1, resp)
  workbook.close()

if __name__ == "__main__":
  get_invoice()

Can anyone tell me, what I'm doing wrong?

* EDIT *

Hello again guys and girls,

I've gotten a little further than yesterday, by following this answer to a question

import requests
import json
import csv

BASE_URL = 'https://restapi.e-conomic.com/'
HEADERS = {
  'X-AgreementGrantToken': 'demo',
  'X-AppSecretToken': 'demo',
  'Content-type': 'application/json'
}


def get_invoice():
  url = "{0}/{1}".format(BASE_URL, 'invoices/booked')
  resp = requests.get(url, headers=HEADERS)
  whale = (resp.json)
  print(resp)
  print(whale())
  output_fil = 'blab.csv'
  horse = len(whale) - 1

  data_til_fil = open(output_fil, 'w', newline='')
  csv_writer = csv.writer(data_til_fil, delimiter=";")
  csv_writer.writerow(["bookedInvoiceNumber","date","netAmount","vatAmount","grossAmount","dueDate"])

  for i in range(0, horse):
    meetup = whale[i]
    bookedInvoiceNumber = meetup['bookedInvoiceNumber']
    date = meetup['date']
    netAmount = meetup['netAmount']
    vatAmount = meetup['vatAmount']
    grossAmount = meetup['grossAmount']
    dueDate = meetup['dueDate']
    csv_writer.writerow([bookedInvoiceNumber,date,netAmount,vatAmount,grossAmount,dueDate])
    data_til_fil.close()

if __name__ == "__main__":
  get_invoice()

I have however still trouble with getting it to work, as it doesn't like my

horse = len(whale) - 1

line. Python responds with

TypeError: object of type 'method' has no len()

Is there anyone here who are patient enough to help me with this? I can say, a lot of people who uses e-conomic, would appreciate it, now and in the future. :-)

When you print resp, do you see what you expect? your steps look similar to the xlsxwriter tutorial — wwii
– wwii, Commented Feb 11, 2018 at 21:50
Yes, I get all the data I want, or the 200 first responds of it, but it's apparently really hard to save that in an Excel file — Blab
– Blab, Commented Feb 11, 2018 at 21:53
Can you include a minimal example of the result of print(resp)? — wwii
– wwii, Commented Feb 11, 2018 at 21:55
Try it here jsfiddle.net/economicapi/rwd2ort4 - just with restapi.e-conomic.com/invoices/booked — Blab
– Blab, Commented Feb 11, 2018 at 22:04

Glacier · Accepted Answer · 2018-02-11 22:22:30Z

1

When you use worksheet.write, it only write to 1 specific cell, you wouldn't want to write every thing in a single cell, would you? Ref to https://stackoverflow.com/a/35623260/7492424

Just replace

worksheet.write(1, 1, resp)

with

json_to_excel(worksheet, resp.json())

def json_to_excel(ws, data, row=0, col=0):
    if isinstance(data, list):
        row -= 1
        for value in data:
            row = json_to_excel(ws, value, row+1, col)
    elif isinstance(data, dict):
        max_row = row
        start_row = row
        for key, value in data.iteritems():
            row = start_row
            ws.write(row, col, key)
            row = json_to_excel(ws, value, row+1, col)
            max_row = max(max_row, row)
            col += 1
        row = max_row
    else:
        ws.write(row, col, data)

    return row

answered Feb 11, 2018 at 22:22

Glacier

1415 bronze badges

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

2 Comments

Blab Over a year ago

Thanks for your help! But it returns File "C:/Users/User/Desktop/aaaab.py", line 21, in json_to_excel for key, value in data.iteritems(): AttributeError: 'dict' object has no attribute 'iteritems'

Glacier Over a year ago

Sorry, you must be using Python3, please change to data.items()

Glacier · Accepted Answer · 2018-02-13 20:53:02Z

There are a couple of issues with your new code.

resp.json is a method, so you need to call it to get the values (resp.json()). And the result is a map, I suppose you need to 'collection' to extract the content.

In [17]: resp.json().keys() Out[17]: dict_keys(['collection', 'metaData', 'pagination', 'self'])
You closed the file too early, please move the close out of the for loop.

Please try below code and see whether that's what you wanted.

import requests
import json
import csv

BASE_URL = 'https://restapi.e-conomic.com/'
HEADERS = {
  'X-AgreementGrantToken': 'demo',
  'X-AppSecretToken': 'demo',
  'Content-type': 'application/json'
}


def get_invoice():
    url = "{0}/{1}".format(BASE_URL, 'invoices/booked')
    resp = requests.get(url, headers=HEADERS)
    # whale = (resp.json)
    whales = resp.json()['collection']
    #print(resp)
    #print(whale())
    output_fil = 'blab.csv'
    horse = len(whales)

    data_til_fil = open(output_fil, 'w', newline='')
    csv_writer = csv.writer(data_til_fil, delimiter=";")
    csv_writer.writerow(["bookedInvoiceNumber","date","netAmount","vatAmount","grossAmount","dueDate"])

    #for i in range(0, horse):
    for meetup in whales:
        #meetup = whale[i]
        bookedInvoiceNumber = meetup['bookedInvoiceNumber']
        date = meetup['date']
        netAmount = meetup['netAmount']
        vatAmount = meetup['vatAmount']
        grossAmount = meetup['grossAmount']
        dueDate = meetup['dueDate']
        csv_writer.writerow([bookedInvoiceNumber,date,netAmount,vatAmount,grossAmount,dueDate])
    # you closed this file too early, pleaes make sure move this out of the for loop
    data_til_fil.close()

if __name__ == "__main__":
  get_invoice()

Thank you very much for your help, really appreciate it! :-)

Collectives™ on Stack Overflow

How to save Python Json output as a CSV file using modules

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related