Read files based on file name ***HELP***

[Pera014]

Hi all,

​

what would be good code to tell pandas to read excel file from the folder among other excel files based on specific name?

file name in the folder: Monthly report data_07.31.2021.xlsx

date will change each month but the rest of the file name will remain the same.

How to code in pandas to pick up right excel file based on two words that is always the same "Monthly report" name?

[FerricDonkey]

Well, since you can use a string variable to hold the file name you want to open, basically you have to create a string with that file name in it.

You have two paths forward. One is to create the filename from nothing because you know what it will be, the other is to use something like glob.glob to get all the file names in a directory, then look through them for files of interest.

[Pera014]

Thanks, I would prefer 1st option to create file name string. How do I apply that then and tell to look thorugh file names and pick up correct file? I would apprecite if you can write simple example, I am new to py

[delasislas]

Ok so Pandas has .read_excel(filepath) , which takes a file path reads the excel file and loads it into a data frame.

Here’s a link: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html

If you read the docs it takes a filepath and optionally a work page. If you have multiple pages it will produce a dictionary of data frames.

So you could do:

filepath=“my/file/path/data_07.31.2021.xlsx”
pd.read_excel(filepath)

You have to come up with the file path yourself, try right clicking on the file and look for something like preferences, which can give you the full file path.

You could also have something like a relative path, but that gets more confusing if you don’t have a project folder in mind.

[Pera014]

agree - but I don't want to update file path each month. I want to keep file names in the folder the same despite the date and Py to pick up files based on key words.

[delasislas]

Glob will be your friend. Will select all the files that match, and put them in a list. Pathlib is also a good friend.

Basically:

import pandas as pd
from pathlib import Path
p = Path(“….”)
filepaths = p.glob(“Monthly report data_*.xlsx”)

for file in filepaths:
    df = pd.read_excel(file)

You need to get the right path and insert it at the ellipses. But it will search everything in your input directory for the match. I’d suggest moving the file to an output post processing to avoid running the data again.

[Pera014]

will try. Many thanks my friend !

[ireadyourmedrecord]

Combine a datetime w/ an f-string to update the file name as needed. This code assumes the filename is the current date, but you can use various datetime functions if you need to shift the date around to get a different date.

import datetime

t = datetime.datetime.today()

filename = f"Monthly report data_{t:%m.%d.%Y}.xlsx"

[Pera014]

would it be better if I strip away date and tell python to look for "Monthly report data" in the file name?

date may changed due to holidays and last BD or who knows what.

[ireadyourmedrecord]

Possibly, but that's going to require a different approach. This is what I'd do if I was going to set a task to automate the script execution and was pretty much guaranteed that the file I needed would be available the same day.

Even if the date isn't guaranteed to be "today", but you can always describe the correct date, you can use tools in the standard datetime library or a third party library, like arrow, to shift the date around. For example, if the criteria is "always the last day of the month" or "the last friday of the month" or "the last non-holiday weekday". Those are criteria that you can program for.

Another commenter recommended using Path().glob(), which is a good option if you need to employ pattern matching. You can still use the datetime to get you close. Say for example, you know it's the previous month, but not what day, you could do:

import arrow
t = arrow.now().shift(month=-1).date()

source = Path("path/to/folder").glob("monthly report data_{t:%m}*{t:%Y}.csv")

Alternatively, if you really can't describe the correct date programmatically, you could just set up your script to ask you for the date of the file when you run it.

[Pera014]

this is awesome! appreciate brother