Search Excel File with a list of values

[deanhystad]

I would read the excel spreadsheet and extract a list of department, machine ID strings. I would format the strings to match the format of the department, machine ID in the csv files. Something like this:

df = pd.read_excel("machines.xlsx")
machine_names = [
    f"{dpt}, {id}" for dpt, id in zip(df["Department"].values, df["Machine"].values)
]
print(machine_names)

I assumed the spreasheet filename is "machines.xlsx", that it has column headers, and that the column headers for the department and machine ID columns are "Department" and "Machine". If the spreadsheet doesn't have column headers you will use index values 0, 1, 2 for spreadsheet columns A, B, C, like this:

df = pd.read_excel("machines.xlsx", header=None)
machine_names = [f"{dpt}, {id}" for dpt, id in zip(df[0].values, df[1].values)]

Now you can use this list to check for matches in the CSV files. Something like this:

csv_folder = '.'
for filename in Path(csv_folder).glob("*.csv"):
    with open(filename, "r") as file:
        values = file.readline().split(",")
    if len(values) > 1:
        machine_name = f"{values[0].strip()}, {values[1].strip()}"
        if machine_name in machine_names:
            print(machine_name)   # Match found.  Do your processing here

This code assumes all the csv files will be in some folder ("." is the current working directory) and that the files all have the extensions ".csv". It opens the file, splits the first line into columns using a comma as the delimiter, and makes a string using the department name and machine ID. This format must match the format of the strings constructed from the excel spreadsheet.

To determine if the CSV file is a valid machine, test if the csv machine string matches any of the machine strings from the spreadsheet.