[FIXED] Why os.listdir() finds the excel but pd.read_excel() returns error?

Issue

here is the simple version of my code:

for filename in os.listdir('excels/'):
    print(filename)
    df = pd.read_excel(filename)
    df.head()

Output is:

RandomExcelData.xlsx
---------------------------------------------------------------------------
FileNotFoundError: [Errno 2] No such file or directory: 'RandomExcelData.xlsx'

What is really happening here? why pandas does not recognize the file name that is clearly there?

I tested this and it works properly:

df = pd.read_excel('excels/RandomExcelData.xlsx')
df.head()

this returns output as intended...

Solution

You are getting this result because filename is just the filename, not the full path. Try this line instead:

df = pd.read_excel('excels/' + filename)

Or, change the script's working directory to 'excels':

for filename in os.listdir('excels/'):
    print(filename)
    os.chdir('excels')
    df = pd.read_excel(filename)
    df.head()

Answered By - MattDMo