Find similar time in filename

I have excel file where specific times are provided e.g 17:40, 18:15, 10:11 for specific dates
and i have Folder where multiplie files are stored ad some of them have "similar time" in name
e.g

XXXXXMCCAAS_17_43_22_Timecheck.csv

So by looking into folder i have to find file out of multiple ones which has something similar to 17:40 in name so it would be 17_43_22

Is there any way to automatically print files that matches my specific "pattern"?

My first shot would be to go for files using regex expression and assuming lets say 10-15 minute window

But is there a better way to do this?

You can use Python's built-in libraries to accomplish this task. Below is a Python script using the os, re, pandas and datetime libraries. This script reads the times from the Excel file, formats them, and then loops through the files in the specified directory, looking for matches within a defined time window.

import os
import re
import pandas as pd
from datetime import datetime, timedelta

# Read the Excel file
df = pd.read_excel('times.xlsx')  # adjust this to your file path and name

# Convert the times to the expected format and create a list
times = df['Time'].dt.strftime('%H_%M_%S').tolist()  # adjust 'Time' to your column name

# Define a function to check if a filename time is within a time window
def within_window(filename_time, excel_time, window=15):
    FMT = '%H_%M_%S'
    excel_time_obj = datetime.strptime(excel_time, FMT)
    
    # Add seconds if missing
    if excel_time_obj.second == 0:
        excel_time_obj = excel_time_obj + timedelta(seconds=0)
        
    tdelta = datetime.strptime(filename_time, FMT) - excel_time_obj
    if abs(tdelta.total_seconds()) <= window * 60:  # compare in seconds
        return True
    return False

# Loop through the files in the directory
for filename in os.listdir('/path/to/your/files'):  # adjust this to your directory path
    # Use regex to extract the time from the filename
    match = re.search(r'\d{2}_\d{2}_\d{2}', filename)
    if match:
        filename_time = match.group()
        # Check if the filename time is within the window for any excel times
        for time in times:
            if within_window(filename_time, time):
                print(filename)
                break  # if a match is found, no need to check the other times

Make sure to adjust the Excel file path, the directory path, and the column name according to your setup.

For the first part of your question, I would do it this way :

from pathlib import Path
import pandas as pd

times = (
    pd.read_excel("tmp/file.xlsx", usecols="A", dtype="str")
    .squeeze().str[:4].replace(":", "_", regex=True).tolist()
) #['17_4', '18_1', '10_1']

matches = []
for t in times:
    matches.extend(Path("tmp").glob(f"*_{t}*"))

Output :

for m in matches:
    print(m)

tmp\XXXXXMCCAAS_17_43_22_Timecheck.csv
tmp\XXXXXMCCAAS_10_10_34_Timecheck.csv
tmp\XXXXXMCCAAS_10_11_23_Timecheck.csv

Spreadsheet & Tree used :

tmp/
┣━━ file.xlsx # <-- doesn't need to be in the same location
┣━━ XXXXXMCCAAS_00_03_45_Timecheck.csv
┣━━ XXXXXMCCAAS_00_25_45_Timecheck.csv
┣━━ XXXXXMCCAAS_00_45_43_Timecheck.csv
┣━━ XXXXXMCCAAS_01_16_35_Timecheck.csv
┣━━ XXXXXMCCAAS_03_01_51_Timecheck.csv
┣━━ XXXXXMCCAAS_03_04_12_Timecheck.csv
┣━━ XXXXXMCCAAS_04_47_22_Timecheck.csv
┣━━ XXXXXMCCAAS_09_27_07_Timecheck.csv
┣━━ XXXXXMCCAAS_10_10_34_Timecheck.csv
┣━━ XXXXXMCCAAS_10_11_23_Timecheck.csv
┣━━ XXXXXMCCAAS_13_28_15_Timecheck.csv
┣━━ XXXXXMCCAAS_14_09_30_Timecheck.csv
┣━━ XXXXXMCCAAS_14_59_41_Timecheck.csv
┣━━ XXXXXMCCAAS_16_33_31_Timecheck.csv
┣━━ XXXXXMCCAAS_17_24_02_Timecheck.csv
┣━━ XXXXXMCCAAS_17_43_22_Timecheck.csv
┣━━ XXXXXMCCAAS_18_24_16_Timecheck.csv
┣━━ XXXXXMCCAAS_19_19_42_Timecheck.csv
┣━━ XXXXXMCCAAS_20_42_18_Timecheck.csv
┗━━ XXXXXMCCAAS_21_21_25_Timecheck.csv