CODEWITHSUNDEEP
python-3.xpandasopenpyxl
murkcity
murkcity

Reputation: 49

Convert API binary Data in Python to Paste into XLSX

I need help converting my api contents into an xlsx

print(download.headers)
print(download.content)

{'Cache-Control': 'no-cache', 'Pragma': 'no-cache', 'Content-Length': '990913', 'Content-Type': 'text/csv', 'Expires': '-1', 'Content-Disposition': 'attachment; filename=Report.csv', 'Access-Control-Allow-Origin': '*', 'Date': 'Fri, 22 Jun 2018 19:17:23 GMT'}

the result is a binary string csv deliminated by commas: ,"3/29/2018 12:00:00 AM","3/29/2018 12:00:00 AM","961015","-2500","-2500","100","Contract: 13","0.01","-1","4","-2500","13565197"\

Pasting it into xlsx will just paste the entire content into a single cell. How can I format this and put into xlsx where each attribute is in a single cell.

enter image description here

the top row is how the data is currently being entered - want it to look like the second row where each attribute has its own column, but I want Python to be able to make the change and paste the data into excel already broken into individual columns.

Thanks

Upvotes: 0

Views: 445

Answers (2)

pnuts
pnuts

Reputation: 59485

Try Text to Columns with , as delimiter.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96773

In B1 enter:

=SUBSTITUTE(A1,CHAR(34),"")

this removes all the double-quotes. Then in A2 enter:

=TRIM(MID(SUBSTITUTE($B1,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

and copy across:

enter image description here

Upvotes: 1

Related Questions