SQL Function in Excel automation

datatable
excel
activities

#1

Hi All,

I am exploring some automation options in excel and I am trying to compare two tables and fetch the correct value , in short I am trying to create some of the functions you might do in SQL ( join , compare etc )

Can you please help me by pointing to the correct functionalities or suggesting an approach or tell me if this doable in Uipath.

I have the following Data Tables ( DT 1 & DT 2 )

Dt1:

DT 2

I need to have a sequence that does the following :

  • looks at type/wt from data table 1 -

  • Compare to Data Table 2 - Join on type see if wt falls between min1 and min 2 and get new value

  • Apply new value back to Data table 1 and populate 0 for the rows with no match

  • Add a new column Y/N flag depending on the new value

The output should be something like this :

Thanks


#2

add datacolumns new value and y/n to DT1

Foreach DT 1 row
{

rowArray = DT2.Select("Max> int.parse(row(2).Tostring) and Min<int.parse(row(2).Tostring())

you will get row array here (1 row)—> get new value and y/n

row(“newvalue”)= newvalue from rowarray;
row(“y/n”)= y/n from rowarray;

}


Database and excel operation
#3

Hi @vvaidya

How to join the first two columns between dt1 and dt2 ?


#4

Hi @vvaidya

I am sharing the code and input I have tried based on your input, can you please take a look and advice on what needs to be added.

MERGE.zip (14.0 KB)