0

I use the POST method via the requests library to download an EXCEL file.

payload = json.dumps({
  "data_ids": [
    "124902201",
    "124835548"
  ]
})

headers = {
  'Accept': 'application/vnd.ms-excel', 
  'Content-Type': 'application/json',
  }


response=requests.post(url, headers=headers, data=payload)

file_excel=pandas.read_excel(response.content)

When I tried the query on Postman, it returns me a response in Bytes form. So the idea I had was to read this file and convert it to Excel format and then load it into a dataframe. Is it possible.

Python_M
  • 1
  • 2

2 Answers2

0

Install openpyxl

pip install openpyxl

Pandas can read the response.content directly

import pandas as pd
import io

file_extension = "xlsx"

(...)

if file_extension == 'xlsx':
    excel_data = pd.read_excel(resp.content, engine='openpyxl')
elif file_extension == 'xls':
    excel_data = pd.read_excel(resp.content)

EDIT: Ok, now I've tested that this does the trick.

Geoffrey Garrett
  • 195
  • 1
  • 9
  • Thanks for your answer. But still the same problem. ********************************************************************************** ValueError: Colors must be aRGB hex values – Python_M Mar 02 '22 at 09:54
  • Then maybe it's an old excel file, with a possible solution here https://stackoverflow.com/a/66647319/7721925 – Geoffrey Garrett Mar 02 '22 at 10:03
  • From what I understand, the response retrieved by pyhon is in BYTES format, so the read_excel cannot locate an excel file. – Python_M Mar 02 '22 at 11:02
  • I tested the code above with and without `io.BytesIO`, both worked. If I remember correctly, the `read_excel` recently added the feature of receiving binary direction. – Geoffrey Garrett Mar 02 '22 at 16:40
0

Thanks, Geoffrey Garrett your suggestion worked for reading both versions of excel files from disk. But I had to modify read_excel() for 'xls', by adding engine='xlrd' and I had to install current version of xlrd. Using current version of Pandas also. Hope this helps others.

daveevans2022
  • 1
  • 1
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/32555922) – ljmc Aug 30 '22 at 10:57