0

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. :-)

Blab
  • 235
  • 2
  • 9
  • When you print `resp`, do you see what you expect? your *steps* look similar to [the xlsxwriter tutorial](http://xlsxwriter.readthedocs.io/tutorial01.html) – wwii Feb 11 '18 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 Feb 11 '18 at 21:53
  • Can you include a minimal example of the result of `print(resp)`? – wwii Feb 11 '18 at 21:55
  • Try it here http://jsfiddle.net/economicapi/rwd2ort4/ - just with https://restapi.e-conomic.com/invoices/booked – Blab Feb 11 '18 at 22:04

2 Answers2

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
Glacier
  • 141
  • 5
  • 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' – Blab Feb 11 '18 at 22:36
  • Sorry, you must be using Python3, please change to data.items() – Glacier Feb 13 '18 at 20:29
0

There are a couple of issues with your new code.

  1. 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'])

  2. 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()
Glacier
  • 141
  • 5