Unable to read excel file , list index out of range error, cant find Sheets

Question

I am trying to read excel (.xlsx) file and convert it to dataframe. I used pandas.ExelFile , pandas.read_excel, openpyxl load_workbook and even io file reading methods but i am unable to read Sheet of this file. Every time i get list index out of range error or no sheet names is case of openpyxl. Also tried xlrd method.

temp_df = pd.read_excel("v2s.xlsx",  sheet_name = 0)

or

temp_df = pd.read_excel("v2s.xlsx",  sheet_name = "Sheet1")

or from openpyxl import load_workbook workbook = load_workbook(filename="v2s.xlsx",read_only = True, data_only = True) workbook.sheetnames

Link to excel file

try `xl = pd.ExcelFile('v2s.xlsx')` then `print(xl.sheet_names)` check to see if that sheet exists, the error looks like it doesn't exist. — Umar.H, Feb 09 '20 at 20:06
xl = pd.ExcelFile("v2s.xlsx") print(xl.sheet_names) this returns [] an empty array — UDevD, Feb 10 '20 at 06:07
@Datanovice the excel file is not empty, there is a sheet with data on it. — APhillips, Feb 10 '20 at 15:13

score 5 · Answer 1 · answered Feb 10 '20 at 00:40

According to this ticket, the file is saved in a "slightly defective" format.

The user posted that he used Save As to change the type of document back to a normal Excel spreadsheet file.

Your file is this type:

You need to save it as:

Then running your code

from openpyxl import load_workbook
workbook = load_workbook(filename="v2s_0.xlsx",read_only = True, data_only = True)
print(workbook.sheetnames)

Outputs:

['Sheet1']

Unable to read excel file , list index out of range error, cant find Sheets

1 Answers1

Linked