How to search the values from one data table into the another data table, if size of the data is huge

Help Studio
, ,
1

Hi

I am working on two databases and I am extracting the data from both the database and storing that in datatable. The volume of each data table is 5 lakh

Suppose dt1 is in the following format

Item Lot No
1 abc,xyz,pqr
2 pqr,sas,abz
3 taw,swq,tsa

And dt2 is in the below format

Item Lot No
1 abc
1 xyz
1 pqr
2 pqr
2 sas
2 abz
3 taw
3 swq
3 tsa

Now my requirement is I have to search item and lot number combination from dt1 into the dt2

For ex : item 1 and lot abc,xyz,pqr, from dt1 database have to search in dt2

so lot combination in dt1 is comma separated and in dt2 it’s not comma separated. As the volume of data is too huge and if l use for each activity here then it will take a long time to execute
Anyone, could you please help me to solve this issue

2

By Using LinQ or Invoke Code activity we can reduce the time to execute

3

Hi @sambulkar ,
I think Linq can help you
But you can share input and expect output file
Regards,
LNV

4

Hi @sambulkar ,

Could you let us know what is the Expected Output for the Inputs provided ?

Considering the DT1 has a column which contains values separated with comma. We can capture all values in that column as a list.

lotNos = DT1.AsEnumerable.SelectMany(Function(x)Split(x("Lot No").ToString,",").toarray).Select(Function(x)x).ToArray

Here, lotNos is a variable of Array of String and DT1 is your First Datatable where you have the Comma separated values.

Next, we can Filter the DT2 based on these lot numbers values captured.

DT2 = DT2.AsEnumerable.Where(Function(x)lotNos.Contains(x("Lot No").ToString)).CopyToDatatable

To Handle errors when Direct CopyToDatatable is used you could check the below post :

We have assumed you would need a filtered DT2 Datatable based on the Lot no values in DT1.

If the assumption is incorrect, then do provide us with the Expected Output, so we could analyse and suggest the appropriate solution.

1 Like