I have two excel files. In which 1 file has column RTO state and other file has RTO Code. if they both matches then I need to assign data from 2nd excel file column RTO location to 1st excel file RTO location. How to write this in Linq query. Because normally it’s taking huge time. Please help me with this.
Hi
assuming Dt1 is excel file read datatable
dt2 is excel File2 read data table
Use invoke code activity
Arguments ->In type–dt1 and dt2
Code:
dt1.AsEnumerable.ToList.ForEach(Sub (x)
If(dt2.AsEnumerable.Any(Function(a) a(“RTO Code”).ToString.Trim.Equals(x(“RTO state”).toString.Trim)))
x(“RTO Location”)=dt2.AsEnumerable.Where(Function(c) c(“RTO Code”).ToString.Trim.Equals(x(“RTO state”).toString.Trim)).ElementAt(0)(“RTO Location”)
Else
x(“RTO Location”)=“No Match Found”
End If
End Sub
)
Hope it helps
You can try this in invoke code it will work
For Each r1 As DataRow In in_dt1.AsEnumerable()
For Each r2 As DataRow In in_dt2.AsEnumerable()
If(r1("RTO").ToString.Trim.Equals(r2("RTO").ToString)) Then
r1("RTO location")=r2("RTO location").ToString 'Mapping the dt2 RTO Location to the dt1 RTO Location'
Exit For 'Exist the loop if dt1 RTO Matched with the dt2 RTO'
Else
r1("RTO location")=String.Empty
'if not matched the dt1 RTO with the dt2 RTO then in else assigning the null'
End If
Next r2
Next r1
