2
r = requests.get(projectsExportURL, auth=(username, password), verify=False,stream=True)
r.raw.decode_content = True

#add snapshot date column
df = pd.read_excel(r.raw,sheet_name='Tasks',Headers=0)

This worked just fine until 0.25.0 and xlrd 1.2.0

I recently had to re-do my entire environment and opted to update. The above code is now resulting in the following error:

  File "d:\python\python37\lib\site-packages\pandas\io\excel\_base.py", line 356, in __init__
    filepath_or_buffer.seek(0)

UnsupportedOperation: seek

if I remove xlrd from the equation pandas throws an error about an optional library missing (like if it is optional, why are you complaining).

So the incoming data is xlsx file format and I have to add a snapshot date to the file and then I send it to a MySQL database.

How can I fix my code to read the excel file with the changes to pandas, I can't seem to find anything in the docs that are specifically jumping out at me about this.

Shenanigator
  • 1,036
  • 12
  • 45

2 Answers2

1

Here is my current replacement code that seems to be working:

wb = load_workbook(filename=BytesIO(r.raw.read()))
ws = wb['Tasks']
data = ws.values 
columns = next(data)[0:]

df = pd.DataFrame(data, columns=columns)
Shenanigator
  • 1,036
  • 12
  • 45
0

This is how I solved this problem to download an xlsx excel file to pandas 1.0 DataFrame. This works on pandas >= 1.0

    xl = requests.get(EXCEL_URL)
    df = pd.read_excel(BytesIO(xl.content), sheet_name="Worksheet Name")

if sheet_name not given 1st sheet will be loaded.

bnik
  • 49
  • 3
  • That's great for pandas 1.0+, but in .25 it doesn't work that way. – Shenanigator Mar 26 '20 at 21:23
  • You are right. That was the reason I had to come up with this solution at the first place because we upgraded our production to pandas 1.0+ then some scripts reading excel stopped working. Thanks for the comment I'll update my answer. – bnik Mar 28 '20 at 02:46