Python Pandas read_excel returns empty Dataframe

user_8598720 0
Viewed 10527

Reading a simple xls returning empty dataframe, can't figure it out for the life of me:

path = ('c:/Users/Desktop/Stuff/Ready')
files = os.listdir(path)
print(files)

files_xlsx = [f for f in files if f[-3:] == 'xlsx']

readyorders = pd.DataFrame()
for filename in files_xlsx:
    with open(os.path.join(path, filename)) as f:
        data = pd.read_excel(f)
        readyorders = readyorders.append(data)

print(readyorders)

The excel is just two simple columns...is it just too early in the day?

python
4 Answers

I had a similar issue and it turns out that there are TWO types of XLSX: "Excel Workbook" (at the top of the list in the image below) and "Strict Open XML Spreadsheet" (with the checkmark). The latter returns an empty spreadsheet in pandas, so use the Excel Workbook (.xlsx) and you won't have problems.

enter image description here

user_55397150

I had the same issue and I later i discovered that it's because I have many sheets in the excel file and I didn't specify the sheet name.

user_32612920

Sometimes there is also a "hidden sheet" which results of bad exports.. You should use the sheet_name parameter for your sheet then or you could also use sheet_name=None. Then you get a dict with the empty df of the hidden sheet and the other data

user_174107140
Related