Read filet data from Excel Doc and match data in Another doc

Help
1

I am trying to read an excel sheet and filtering only Emp Id, First and Last name. I am able to do this using Filter Data table. However I have to cross check this data based on Empid in another excel having multiple columns like Emp id, Joining, Requestor, Approver, Status.

Now I have to match all emp id from Excel 1 to matching records in Excel 2 based on Emp id.

Requirement is that there could be more than 1 records in excel 2 having emp id.

Any suggestion how to filter and match records…

image

2

Hi @p4uk80

Welcome to the community!! :smiley:
Yes… To get the matching records only, you can use the Join Data Table activity with the Inner Join type.

I have a sample workflow which I created for something similar. In this it takes two excel data tables, Join them to get matching records, and get a output to an excel

Hope this helps
JoinDataTables.xaml (6.0 KB)
MatchDataTables.xaml (10.6 KB)

If this works for you, please make sure to mark the answer as the solution too :slight_smile:

3

Thanks @Lahiru.Fernando. However I want to match the records one by one depending some condition.

So I am having in one Datatable a cost centre code and I want to search this code in Datatable 2.

Using below Linq in Assign however getting below error:

(from s in Dt_CheckFIle2.AsEnumerable where s.Field(Of String)(“Cost Centre”) = row(“Authoriser Cost Centre”).ToString and s.Field(of String)(“First Name”) = row(“Authoriser Name”).ToString select s).CopyToDataTable.Rows.Count

Unable to cast object of type ‘System.Double’ to type ‘System.String’

Please suggest.

image
image528×626 21.3 KB

Also this matching will give me a list which I want to save in an array or datatable:

I tried this one also:

Dt_CheckFIle2.select(“[Cost Centre] = ‘”+row(“Authoriser Name”).ToString+“’”)