Search code examples
pythonpython-requestsexport-to-excel

Cannot save Excel response from API

I am doing a POST request to an API that returns an Excel file.

When I try the process without Python - in Postman - it works just fine : I see the garbled output, but if I click on Save response and Save to a file, it saves the file as an xlsx file that I can open just fine:

enter image description here

When I try to do the same in Python, I can also print the (garbled) response, but I do not manage to save the file as something that I can open.

First part of code (runs without issue):

import requests
for i in range (1,3):
   url = "myurl"
   payload={}
   headers = {}
   response = requests.request("POST", url, headers=headers, data=payload)

And now for the crucial part of the code.

If I do A:

    with open('C:\\Users\\mypath\\exportdata.xlsx', "w") as o:
           o.write(response.text)
        print(response.text)

...then I get this error when I run the code:

  File "C:\Users\Username\AppData\Local\Programs\Python\Python310\lib\encodings\cp1252.py", line 19, in encode
    return codecs.charmap_encode(input,self.errors,encoding_table)[0]
UnicodeEncodeError: 'charmap' codec can't encode characters in position 10-11: character maps to <undefined>

If I do B:

   with open('C:\\Users\\mypath\\exportdata.xlsx', "w", encoding="utf-8") as o:
      o.write(response.text)
   print(response.text)

...then the code runs without error, but I get an extension/format error in excel when I open the file.

How do I save the excel file with python so that I can open and view it correctly after?

This is not a standard text/csv to excel conversion issue, you can see from the garbled output that all the XML hallmarks of an excel file are there.

Solution

  • Excel isn't Text. Excel is binary. Try response.content:

    with open(filename, "wb") as o:
  o.write(response.content)