Reputation: 245
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. :-)
Upvotes: 0
Views: 781
Reputation: 141
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()
Upvotes: 0
Reputation: 141
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
Upvotes: 1