Match file names against pre-defined excel list and highlight non-matching items

Hi,

I’m trying to develop a workflow which read the file names from the particular folder (dynamic) and should match each name against pre-defined file name list in excel file. Any file name which is present in the pre-defined excel sheet but not in the folder, should be noted and mentioned in another new excel sheet/file or body of outlook mail.

I’m done with discovering dynamic path folder and getting file names in to data tables, but facing difficulties on later part fo matching it against pre-defined excel sheet and keep noting if not found in folder, any help on this would be appreciated.

Example :

Folder (C:\Users\Administrator\Document\Test) has files : Test.txt, Test1.txt, Test2.txt, Test4.txt & Test6.txt

Pre-Defined Excel sheet (C:\Users|Administrator\Document\compare.xls) has row entry of Test,Test1, Test2, Test3, Test4, Test5, Test6 in column called “Data”

Now as Test3 & Test 5 name is there in the excel sheet but same file name not present in the file Folder, name “Test3 & Test5” should be noted and mentioned in another output excel file or else body of outlook mail.

1 Like

Hi,

So, let’s say we have an enumerable of the file paths from the folder and we have a data table consisting of filenames in column “Data”

You could essentially, filter the datatable for all rows that are not found in the folder. So it would only take a couple steps.

Here is my idea in pseudocode:

Assign fileList = System.IO.Directory.GetFiles(folderPath, "*")
Excel Read Range of compare.xls to data

Assign nonMatchingData = data.AsEnumerable.Where(Function(r) Not fileList.Any(Function(f) System.IO.Path.GetFileNameWithoutExtension(f) = r("Data").ToString.Trim) ).CopyToDataTable

Excel Write Range of nonMatchingData to new sheet

The nonMatchingData assignment basically says to go through each row (r) and see if the item exists in fileList using GetFileNameWithoutExtension on each filepath.

Regards.

Made a correction here, because if there are no non-matching rows found, it will say data table contains no rows error message.

To correct this, we can create an Array Of DataRows to use for non-matching data. Then, check its count before writing the data.

Assign fileList = System.IO.Directory.GetFiles(folderPath, "*")
Excel Read Range of compare.xls to data

Assign nonMatchingRows <Of DataRow[]> = data.AsEnumerable.Where(Function(r) Not fileList.Any(Function(f) System.IO.Path.GetFileNameWithoutExtension(f) = r("Data").ToString.Trim) ).ToArray

If nonmatchingRows.Count > 0
    Excel Write Range of nonMatchingRows.CopyToDataTable to new sheet
2 Likes

Thanks @ClaytonM for your quick response and suggestion :grinning: , I’m new to this (specially codes :roll_eyes:) hence might get time to understand your pseudo code and form it on workflow… Will try this and let you know if required more clarity or info…

I tried to build workflow based on your pseudo code but it seems working as expected :smile: The ‘If’ was giving public location reference error, hence added “ToList()” in nonMatchingRows assignment, and changed type to List(Of DataRow).

Now i got the data in sheet 2, i want this data to be put in email body as well as list of string. Do i need to read the sheet 2 into data table and convert in to list ? or any better way of doing it directly from “nonMatchingRows” ?

Cool. You got it working. Let me know if you have issues with it.

To put data in email body, you can just take the list of filenames that were non-matching and put it in like a string. If you have a more structured data set like multiple columns, then you would need to convert the data table into HTML code (for this you could try using vvaidy’s custom activity in community feed.

Let’s assume you just want to list all the non-matching filenames:
Based on my previous suggestion, I would say it would be easiest to just use .Select() to pull in an array of the non-matching filenames.

nonMatchingString = String.Join(System.Environment.Newline, nonMatchingRows.Select(Function(r) r("Data").ToString.Trim ) )

And you can place that inside the If activity that checks if .Count > 0

So essentially, you are “selecting” the values in the “Data” column to create an array, then using .Join() to combine them using a newline for delimiter.

Regards.

1 Like

Can i get a sample workflow for this

Hello @ClaytonM

Can i get a sample workflow for this

Regards,
Shiv