LINQ query to find data present in another excel if data matches with corresponding value

Help Activities
1

Hi All,
I have two excel files. In excel 1 (datatable : dt1) I have columns for City_Code and State_Name but data present only in City_Code. In another excel
(datatable:" dt2) I have column City_Code and Stata_Name and data present in both column city_code and State_Name. I want to write data in dt1.State_Name where
dt1.City_Code matches with dt2.City_Code.
I Don’t want to use for each loop. Can someone please provide LINQ query to achieve the result?

Please have a look at below excel screenshot.

LINQ
LINQ995×276 36 KB

2

My linq query as below.
dt2.AsEnumerable().where(Function(x) x(2).ToString.Equals(row(“City_Code”)).SingleOrDefault
But it is giving me error as " Object reference not set to an instance of an object" while printing it.

3

Why do you want to avoid usage of Loop? The foreach row is actually quite fast. So I am giving you below suggestions

  1. Use of Foreach row and Filter Datatable activity
  2. Use of Join Datatable activity (whatever the join type you choose, there will be an extra empty column for “State_Code” column.So you have to include logic to remove the empty column as well).
  3. Linq Join (Here you can choose what columns you want in your output table. Reeference - Join Two DataTables Using LINQ In ASP.Net C#)
  4. Use of select statement, which is Linq, but this will need loop too and the output will be array of datarows.
4

@sandyk
the join type is currently not derivable from the description as it is not specifying on how to handle non matching city codes from dt1 in dt2.

The join datatable would help to do it with an out of the box uipath activity.

Doing it with LINQ could look like this:

(From d1 In dt1.AsEnumerable
Join d2 In dt2.AsEnumerable
On d1("City_Code").toString.Trim Equals d2("City_Code").toString.Trim
Select d2).CopyToDataTable

Find starter help here:
Match_1Col_ResultKeyColMatchVal.xaml (8.6 KB)