Can you make a script to cross check names in excel column with filenames in the explorer folder?

Copper Contributor

Hi, as written in subject, is it possible to make a script/macro (preferably with python) that cross checks X amount(in my current case in the hundreds+) of names in an excel sheet with existing filenames in your explorer folder/sub folders, and if a name in the excel column matches a name in the file explorer/folder, highlight or basically put a checkmark of "this exists" or if not found "cannot be found" for example, in the excel sheet or create another excel sheet with the name pair that were found and not found, basically i just want to know if the file already exists or not and if it exists show something to let me know that a file with X name exists. And if possible help me make something like this or atleast the "framework" for it as im practically a beginner when it comes to programming/scripting, Hope this made some sense.. :grinning_face_with_sweat:, Appreciate any help i can get.

2 Replies

@Legion97 Not much of a programmer myself, but with PowerQuery (PQ) you can achieve this pretty easily.

 

Step one is to connect to the main folder (that may include sub-folders). Normally you would want to combine relevant files from a folder with PQ, but in your case, you just want to connect to folder and "read out" all the file names in there,

 

Then, create a table from the list of file names you want to check their existence for and connect to that table with PQ.

 

Finally, you perform a merge of the table with file names against the (connection only) full list of files in the main folder and sub-folders.

 

Peace of cake, though, perhaps hard to believe and difficult to imagine if you have never worked with PQ before.

 

 

Thx for the reply, Ok i'll give that a try. :)