Getting cell value from one DateTable from another DataTable

Hi guys, currently I’m facing a problem is how do I get a cell value based on 1 excel file column from another excel file column. For now I read both excel file and create 2 DT in UiPath and I join them together to get my value, but there is 1 scenario is that when the value does not exist in the excel column, the robot will automatically end the workflow because it’s in a For Each loop and there is no value in the excel file, instead I want it to key in a default value. Please help

@sam.lee

Try unchecked the Add Headers for read range

Currently I get the value based on the header, so I think this does not help. Because I use like row(“header”).ToString to get the value

For Example: Problem Number 1

DT1 consist of "PIC"
DT2 consist of “PIC” & "Username"

Now, I need to get the PIC(DT1) to map with PIC(DT2) and get the value of “Username”. Something like VLookup.

My current solution is to join both DT1 and DT2 then create a new DT3. After that using a For Each activity in DT3 to key in the "Username"

But there is a minor problem is that sometimes the “PIC” in DT1 does not exist in DT2 which cause the robot ending the workflow because the “PIC” does not exist in DT3. Instead, I want it to key in a default value like “SAM”.

@sam.lee

In that case, try using LookUp Data table activity.

Before processing the value, check value exist and then process it.

If value exist ,it will give the rowindex or cellvalue. If not, it will give -1 value

1 Like

Hi @lukas_krishnan, I really appreciate your advice! Now I able to get the Username if there is a match. But it would not return -1 value if the PIC does not exist.

@sam.lee

Refer mentioned link.
Kindly get back if required further assist.

When the PIC(DT1) could not found in PIC(DT2), it will not return as -1 value. Currently I put it in a IF activity, how should I write the Condition?

@sam.lee

Try it once.

For merging datatable with common values.

DT3=(From t1 In DT1.AsEnumerable
Join t2 In DT2.AsEnumerable
On t1(“PIC”).ToString.Trim Equals t2(“PIC”).toString.trim Select t2).CopyToDataTable

DT3 will contains the Values of PIC which is in DT1 and DT2 with colums of PIC and USERNAME

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.