Reading Excel file and use a wildcard in file name and sheet name

[randolphoralph]

I have a simple script that reads an excel file and captures certain columns and saves to a new Excel spreadsheet. I run this script each day and before I run it I have to update the date in the file name and sheet name to match the current report. I am trying to determine if it is possible to use a wildcard in the file name and sheet name. I have tried using the * for a wildcard like this....data = pd.read_excel(r"H:\Daily Reports\Employee_*_Report.xlsx" , sheet_name='Metrics * Employee'), but it does not seem to work. I am relatively new to using Python and I am sure it is probably something simple that I am missing.

Here is the python script I am using.

import pandas as pd

data = pd.read_excel(r"H:\Daily Reports\Employee_12202021_Report.xlsx" , sheet_name='Metrics Dec 20 Employee')

#Select only certain columns
df = pd.DataFrame(data, columns= ['Lookup','Report_Date', 'Employee_ID', 'Type', 'Location', 'Sales', 'Hours'])

#load to excel and save
df.to_excel (r'H:\Monthly Reports\Completed\Employee Metrics 12202021.xlsx', sheet_name='Summary', index=False, header=True)

[menator01]

Is there more than one file in the directory?

to get the sheet names you could do domething like:

import pandas as pd
import os

df = pd.read_excel(f'{os.getcwd()}/weather/test.xlsx', None)

for sheet in df.keys():
    print(sheet)

Output:
Sheet1

Will list all sheets in the file.

You could use listdir() to get file names

[randolphoralph]

The directory would only contain one file, and the file would have a date in the file name. I had thought about using a similar approach to list the sheet names, but wasn't sure how to account for the sheet name on each daily file to be variable when reading the excel file/sheet.

[snippsat]

The directory would only contain one file, and the file would have a date in the file name.

Can use glob or also pathlib has glob build in.
Example

import pandas as pd
from pathlib import Path

dest = r'.'
for path in Path(dest).rglob('output_*_result.xlsx'):
    if path.is_file():
        print(path)

df = pd.read_excel(path)
print(df)

Now will this match any number.
So in my test one file with these two names read fine.

output_123_result.xlsx
output_99999_result.xlsx

As it's one file should get away with not specify sheet name.

[randolphoralph]

The directory would only contain one file, and the file would have a date in the file name.

Can use glob or also pathlib has glob build in.
Example

import pandas as pd
from pathlib import Path

dest = r'.'
for path in Path(dest).rglob('output_*_result.xlsx'):
    if path.is_file():
        print(path)

df = pd.read_excel(path)
print(df)

Now will this match any number.
So in my test one file with these two names read fine.

output_123_result.xlsx
output_99999_result.xlsx

As it's one file should get away with not specify sheet name.

I added from pathlib import Path, but get an error stating ImportError: cannot import name 'path' from 'pathlib'. Does it matter that I am working within Windows?

[snippsat]

pathlib was new in Python 3.4,what version of Python do you use?
Remember that import is case sensitive,so if i test can make your error.

>>> from pathlib import path
Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
ImportError: cannot import name 'path' from 'pathlib' (C:\Python310\lib\pathlib.py

# It's like this 
>>> from pathlib import Path
>>> 

[randolphoralph]

pathlib was new in Python 3.4,what version of Python do you use?
Remember that import is case sensitive,so if i test can make your error.

>>> from pathlib import path
Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
ImportError: cannot import name 'path' from 'pathlib' (C:\Python310\lib\pathlib.py

# It's like this 
>>> from pathlib import Path
>>> 

That is exactly what the issue was. Case sensitive. Good catch and thank you for your help.