3

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*")
Shubhankar Kamat
  • 53
  • 3

2 Answers2

3

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]
chumbaloo
  • 543
  • 4
  • 13
Chris
  • 22,987
  • 3
  • 18
  • 40
  • An advantage of this is that it is performant - the data is read in only once into memory, allowing for further processing without repeated reading in of the file(s). – sammywemmy Jun 09 '20 at 05:45
2

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.

chumbaloo
  • 543
  • 4
  • 13
  • But what if there are alphabets instead of numbers. I want to import the sheet starting with "My Bench Sheet" irrespective of what comes after *Sheet*. – Shubhankar Kamat Jun 09 '20 at 05:04
  • 1
    You need a way to be able to get a list of names you want to insert into the read_excel method. https://stackoverflow.com/a/17977609 provides a way for you to be able to get a list of all the sheet names. And then you could use the python re (https://docs.python.org/3/library/re.html) module to match any that fit your pattern described. – chumbaloo Jun 09 '20 at 05:08