I am a new coder. I am having trouble with pandas.read_excel(). I had a lot of excel files. Pandas read most of it. However, Pandas couldn’t read some of the files and they return the exactly same corupted output on every file that pandas couldn’t read.

My Code:

import pandas as pd


files = """AllExcels\NH1 CE A+C 20W AI 20th Oct 2023.xls
AllExcels\33WPD B003 B005 A+C BOM update.xls
AllExcels\33WPD B003 B005 Single C BOM(2).xls
AllExcels\35W B011 A+C BOM.xls
AllExcels\35W B011 Single C BOM.xls
AllExcels\CE 20W  A+C Rev.xls
AllExcels\CE 20W  A+C.xls
AllExcels\CE NH 1 QC 18W April 07, 2023 (1).xls
AllExcels\CE NH 2 QC 18W April 07, 2023 (1).xls
AllExcels\CE NH 2 QC 18W May 08，2023.xls
AllExcels\CE NH1 A+C 20W March 29, 2023.xls
AllExcels\NH 1 CE QC 18W BOM 20th Oct 2023.xls
AllExcels\NH1 CE A+C 20W AI 20th Oct 2023.xls"""
filesList = files.splitlines()
allSheets = {}
num1 = 0
num2 = 0
num3 = 0
for file in filesList:
    file_read = pd.read_excel(file, sheet_name=None)
    num2 = 0
    for sheet_name, sheet in file_read.items():
        sheet_name = "page" + str(num3)
        allSheets[sheet_name] = sheet
        print(sheet)
        num2 += 1
        num3 += 1
    num1 += 1

Output of Every Excel File in This Example(Each of them are identical, I didn’t want to write all of the output. It was pretty long.):

   Unnamed: 0
0         1.0
1         NaN
2         NaN
3         NaN
4         0.0
5         1.0

Example of One of the Excel Files(They all have different values)