read the excel file in directory using pandas python

Question

I would like to read the excel files in directory if there is a file with specific name And then do some data related operations. But firstly I have a problem to read the files with pandas

import os
import pandas as pd

for filename in os.listdir(my_path):
    if filename.startswith('PB orders Dec'):
        dec=pd.read_excel(filename,sheet_name='Raw data')

error: FileNotFoundError: [Errno 2] No such file or directory: 'PB orders December.xlsb'

But when I run this code:

import os
import pandas as pd
    
    for filename in os.listdir(my_path):
        if filename.startswith('PB orders Dec'):
        print(filename)

result is the existing file name in directory : PB orders December.xlsb

How can I read a specific file in directory based on the name?

ljmc · Accepted Answer · 2021-12-17 08:53:56Z

1

The directory is missing when you read_excel, you only point to the file as you showed with the print.

You need to rebuild the full path with for instance, os.path.join:

import os
import pandas as pd

for filename in os.listdir(my_path):
    if filename.startswith('PB orders Dec'):
        dec = pd.read_excel(os.path.join(my_path, filename), sheet_name='Raw data')

answered Dec 17, 2021 at 8:53

ljmc

5,3732 gold badges11 silver badges30 bronze badges

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

Comments

9769953 · Accepted Answer · 2021-12-17 08:54:06Z

1

Add the directory: path = os.path.join(my_path, filename), then pd.read_excel(path, ...):

import os
import pandas as pd

for filename in os.listdir(my_path):
    if filename.startswith('PB orders Dec'):
        path = os.path.join(my_path, filename)
        dec = pd.read_excel(path, sheet_name='Raw data')

answered Dec 17, 2021 at 8:54

9769953

12.5k3 gold badges31 silver badges46 bronze badges

2 Comments

Greencolor Over a year ago

after installing fsspec, i get new error OSError: File contains no valid workbook part

Greencolor Over a year ago

it is the extension problem. with xlsx it works but with xlsb no

Collectives™ on Stack Overflow

read the excel file in directory using pandas python

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related