Pandas read_excel doesn't read certain xlsx files, returns empty dataframe

Question

Hi I wanted to do some work with my binance.com trade exports but it seems as if pandas can't read the orignal nor copies of the original report from binance.

I checked with Nic Scorazzos answer (Python Pandas read_excel returns empty Dataframe) to see if the file is indeed an excel workbook and not a strict open xml spreadsheet but it seems to me that my file is a workbook.

I checked with the file explorer and the default on 'save as' and it both shows 'Microsoft Excel Workbook (.xlsx)'

Resaving the file as a workbook helps but I want to import them to my python code directly without manually working on them.

I'm on OSX with pandas 1.2 and necessary plugins. Pandas reads other .xlxs files normally, it's just with the binance exports

For those who want to know my code

import pandas as pd
import numpy as np

df = pd.read_excel('binanceExport.xlsx')
df

returns

Date(UTC)
---------

which is the first cell in the spreadsheet

whereas

print(df)

returns

Empty DataFrame
Columns: [Date(UTC)]
Index: []

Screenshot of the spreadsheet

EDIT 1: I tried saving it as an Open Strict XML Spreadsheet (the other .xlsx) and python returns an IndexError: list index out of range

strange, I really believed the original to be an OpenStrictXMLSpreadsheet somehow but as it seems it's not...

Try changing "engine" parameter of read_excel. In particular, try using openpyxl engine, because often it's better at wowrking with newer Excel formats. — NotAName
– NotAName, Commented Jan 20, 2021 at 0:02
hi @pavel, it seems to be a bug on openpyxl's side github.com/pandas-dev/pandas/issues/39250 — Thuan
– Thuan, Commented Jan 20, 2021 at 8:13

user11545193 · Accepted Answer · 2021-01-20 00:00:12Z

0

Try to put more arguments

df= read_excel('name.xlsx', sheet_name='')

Are the code and the file in the same folder? Try type all path

I thin that the problem is the type of the data. Try to specify to

answered Jan 20, 2021 at 0:00

user11545193

Sign up to request clarification or add additional context in comments.

1 Comment

Thuan Over a year ago

I resaved the file as an excel workbook and it solves the problem. But after some digging it seems as it's a Bug on openpyxl's side (see: github.com/pandas-dev/pandas/issues/39250)

Collectives™ on Stack Overflow

Pandas read_excel doesn't read certain xlsx files, returns empty dataframe

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related