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

user_11722233 0
Viewed 1060

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*")
pandas python-3.x python
2 Answers

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]
user_70937410

Could you possibly use f strings.

For example:

for i in range(10):
    bench_file_1 = pd.read_excel("Bench1.xlsx", sheet_name = f'My Bench Sheet {i}')

The above example just provides a range from 0-9 inclusive. If you already have that list of numbers, you could iterate through that list instead of generating the numbers from a range.

user_62944830
Related