1

I am trying to read multiple excel files in a loop using read_excel :

Different excel files contain sheet names which contain the word "staff" eg Staff_2013 , Staff_list etc

Is there a way to read all these files dynamically using some wild card concept ?

Something like the code below :

df = pd.read_excel(folder,col_names=True,sheet_name='Staff*')
Debayan
  • 572
  • 6
  • 16

1 Answers1

2

You can list the sheets and select the ones you want to read one by one.

For instance:

xls_file = pd.ExcelFile('my_excel_file.xls')
staff_fnames = [sheet for sheet in xls.sheet_names if sheet.startswith('Staff')]
for staff_fname in staff_fnames:
    df = pd.read_excel('my_excel_file.xls'), sheet_name=staff_fname)

Or, if you don't mind loading all the sheets, you can also use sheet_name=None to load all sheets in a dict and filter afterwards:

dfs_dict = pd.read_excel('my_excel_file.xls', sheet_name=None)
dfs_dict = {s: df for s, df in dfs_dict.items() if s.startswith('Staff')}
rjg
  • 569
  • 6
  • 18
  • Perfect, i know this is could be problematic. however it does solve the issue for now – Debayan Oct 16 '20 at 05:10
  • 1
    `read_excel` can read multiple sheets: https://stackoverflow.com/a/46081870. Your explanation, and claim that you have to read the sheets one-by-one, is incorrect. – BigBen Oct 04 '22 at 16:49