Linq query to Match column values with another DT and copy values of another column

[Sathish_Kumar_S]

I have 2 data table,

DT1 = contains Likely SAM name & Termnation Date Columns

DT2 = contains UserID & Logon

I want to check the UserID’s available in DT2 also available in DT1 under “Likely SAM name” column and if it is available copy only those users values available under “Termination Date” date to DT2

Note : Attached DT1 & DT 2 & Expected output fie for reference

DT1.xlsx (10.7 KB)
DT2.xlsx (10.7 KB)
ExpectedOutput.xlsx (10.8 KB)

[lrtetala (Lakshman Reddy)]

Hi @Sathish_Kumar_S

Try this

Code:

For Each row2 As DataRow In DT2.Rows
    Dim userID As String = row2("UserID").ToString()
    Dim foundRow As DataRow = DT1.AsEnumerable().FirstOrDefault(Function(r) r("Likely SAM name").ToString() = userID)
    If foundRow IsNot Nothing Then
        row2("Termination Date") = foundRow("Termination Date")
    End If
Next

Output:

Regards,

[rlgandu (Rajyalakshmi Gandu)]

@Sathish_Kumar_S

(From row2 In DT2.AsEnumerable()
         Join row1 In DT1.AsEnumerable()
         On row2("UserID").ToString().Trim() Equals row1("Likely SAM name").ToString().Trim()
         Select resultDT.LoadDataRow(New Object() {
           row2("UserID"),
           row2("Logon"),
           row1("Termination Date")
         }, False)).CopyToDataTable()

[mkankatala (Mahesh Kankatala)]

Hi @Sathish_Kumar_S

Follow the below steps by using LINQ Expressions,
→ Use Read range workbook activity to read the DT.xlsx and store in a datatable called DT1.
→ Then use another Read range workbook activity to read the DT2.xlsx and store in a datatable called DT2.
→ Create a Datatable datatype variable called Output_dt and use the below assign activity,

- Assign -> Output_dt = DT2.Clone()

→ Use the add data column activity to add the “Termination Date” column to Output_dt.
→ Then use the Assign activity to write the below expression,

- Assign -> Output_dt = (From row In DT2 
                         Let TerminationDate = If(DT1.AsEnumerable.Any(Function(X) X("Likely SAM name").ToString.Equals(row("UserID").ToString)), DT1.AsEnumerable.Where(Function(Y) Y("Likely SAM name").ToString.Equals(row("UserID").ToString)).Select(Function(Y) Y("Termination Date").ToString).FirstOrDefault(),"") 
                         Select Output_dt.Rows.Add({row("UserID").ToString, row("Logon"), TerminationDate}) 
                               	).CopyToDataTable()

→ Then use the Write range workbook activity to write the Output_dt to ExpectedOutput.xlsx.

Check the below workflow for better understanding,
Sequence2.xaml (13.0 KB)

Hope it helps!!