How do I import multiple excel files with similar sheet names using wild card?

Question

I'm importing this sheet called "My Bench Sheet 1" using the following code.

bench_file_1 = pd.read_excel("Bench1.xlsx", sheet_name = "My Bench Sheet 1")

I have multiple workbooks with sheets starting with "My Bench Sheet". I want to use a wildcard so that I do not have to type the sheet name every time I import it, since it basically is the same except for the figure at the end.

I have tried using this wildcard, but it does not work.

bench_file_1 = pd.read_excel("Bench1.xlsx", sheet_name = "My Bench Sheet*")

Answer 1

Another (but safest) way would be to read the whole excel file first, gather the sheet names. As seen in this explanation: https://stackoverflow.com/a/17977609

excel = pd.ExcelFile("your_excel.xlsx")
excel.sheet_names
# ["Sheet1", "Sheet2"]
dfs = [pd.read_excel("your_excel.xlsx", sheet_name=n) for n in excel.sheet_names]