VLookup between two different Excel where column could contain null values

[rishiathawale]

Hi All,

I am trying to apply VLookup between 2 different excel where column could have null values in it.

Below is the sample data

Input - DT1

Input - DT2

Output

Below is the query I have written but it is throwing error

(From row1 In in_dt_ML.AsEnumerable()
Group Join row2 In in_dt_SP.AsEnumerable()
On (row1.Field(Of String)(“Primary Owner”)).ToLower Equals
(row2.field(Of String)(“EmployeeID”)).ToLower Into Group
From temp In Group.DefaultIfEmpty()
Where (Not row1.IsNull(“Primary Owner”) AndAlso Not temp.IsNull(“EmployeeID”)) OrElse (row1.IsNull(“Primary Owner”) AndAlso temp.IsNull(“EmployeeID”))
Select dt_OP.Rows.Add({row1.Field(Of Object)(“Compliance_Group”),
If(Convert.ToString(row1.Field(Of Object)(“In Scope”)).equals(“-2146826246”),“#N/A”,row1.Field(Of Object)(“In Scope”)),
If(Convert.ToString(row1.Field(Of Object)(“Primary Owner”)) Is Nothing,“”,Convert.ToString(row1.Field(Of Object)(“Primary Owner”))),
If(temp Is Nothing,“”,temp.Field(Of Object)(“Email”)),
If(Convert.ToString(row1.Field(Of Object)(“Secondary Owner”)) Is Nothing,“”,Convert.ToString(row1.Field(Of Object)(“Secondary Owner”))),
" "})).ToList()

Error

@ppr @Yoichi

Thanks you!

[ppr (Peter Preuss)]

it might be more simple to change it into a lookup approach

• lookup datatable
  OR
• using a lookup dictionary along with the ContainsKey check

[rishiathawale]

Hi @ppr
Could you please give me detailed query regarding same.
Thanks

[mkankatala (Mahesh Kankatala)]

Hi @rishiathawale

You can do it with the LINQ Expressions,
→ Use the read range workbook activity to read the excel1 and store in a datatable called DT1.
→ Use another read range workbook activity to read the excel2 and store in a datatable called DT2.
→ Use the assign activity and use the below LINQ Expression,

- Assign -> dt_Output = DT1.AsEnumerable().Select(Function(r) DT1.Clone().LoadDataRow({r(0), r(1), r(2), If(DT2.AsEnumerable().Any(Function(r2) r("Primary Owner").ToString() = r2("EmployeeID").ToString()), DT2.AsEnumerable().First(Function(r2) r("Primary Owner").ToString() = r2("EmployeeID").ToString())("Email").ToString(),""), r(4), r(5)}, False)).CopyToDataTable()

→ Use the write range workbook activity to write the dt_output to the excel1.

Hope it helps!!

[ppr (Peter Preuss)]

@rishiathawale

please give me

Assign activity:
dictLK | DataType: Dictionary(Of String, String) =

dt2.AsEnumerable().ToDictionary(Function (x) x("EmployeeID").toString, Trim, Function (x) x("Email").toString.Trim)

For each row in DataTable Activity | currentRow in dt

• Assign Activity: strPO = currentRow(“Primary Owner”).toString.Trim
• IF Activity | Condition: dictLK.ContainsKey(strPO)
  • THEN: currentRow(“Owner Email ID”) = dictLK(strPO)
  • ELSE: do nothing or handle the not found actions

As we can see we modelled it with a lower complexity and avoided the datatable reconstruction given by a LINQ Datacolumn update