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.

1 2 3 4 5 6 7 8 9

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:

1 2 3 4 5 6 7

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

1 2 3 4 5 6 7 8 9 10

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.

1 2	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

1 2 3 4 5 6 7 8 9 10

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.

1 2	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.

1 2 3 4 5 6 7 8

>>> 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.

1 2 3 4 5 6 7 8

>>> 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.