How can I combine two datatables without using a for each or filter?

Help Studio
, ,
1

Hi guys! I have a question. How could I join two data tables into one? The difficulty of this is as follows:

  1. Must contains ALL the data in DT1.
  2. It must be filtered according to column N° Doc.
    If the DT2 has a Doc No. that the DT1 does not contain, add to the DT3. Otherwise, do not add.
  3. Don’t use filter for each activity with Filter data table activity (I used that method and these are usually tables of more than 100,000 rows. so it takes a long time and I am looking for something faster, like with LinQ, or else a faster method to process MANY rows and, using a foreach activity to check the N°Doc and then using a Filter Data Table activity to filter DT2, uses A LOT OF TIME)
    Libro1.xlsx (26.6 KB)
2

Use Join DataTable,Filter DataTable and Merge DataTable.

Regards,
NaNi

3

Could you tell me how to use them properly? Since I don’t know how to make the merge filter through the Doc No. column and not through the other columns.

4

@Enzo

Check as below for your reference

image
image1524×1069 97.4 KB

dt1.AsEnumerable().Except(dt2.AsEnumerable(),System.Data.DataRowComparer.Default).CopyToDataTable

Hope this may help you

Thanks

5

Hi @Enzo,

For each or For each row are not slow. That is a myth. Yes linq query may be a little faster but I am quite sure the standard activites themselves use linq query in the background / source code. So looking to avoid using well structured standard activities is not what I would recommend. Standard activities are also easy to understand and comprehend than a linq query to all in your team / organisation. Nothing against linq, but the forum lately is so focused on using linq that they forget standard activities are not slow in comparision.

See the test results here:
There is no need to try to avoid For Each loops on large amounts of data - Help / Studio - UiPath Community Forum

You have to also evaluate other criteria as stated in this post : LINQ vs Loops like for, for each and while - Help / Something Else - UiPath Community Forum

1 Like
6

I know, but using a for each loop and using a “Filter datatable” by N°Doc takes at least 1 sec per filter. Then… it comes slow.

7

@Enzo

It looks like your case description and the requirements are mixed up with implementation portions.

Here we do see a clear requirement: dt3 will have all dt2 rows, where no dt1 exists for the particular DocNO

We can do it with join datatable
left: dt2
right: dt1
join type: left

and will use (afterwards we do a column removal on unneeded cols from join result) from Join result these rows where dt1 DocNo col is null/empty

doing it with LINQ give a try on:

dt3 =

(From dl In dtData2.AsEnumerable
Group Join dr In dtData1.AsEnumerable 
On dl(1) Equals dr(1) Into gj = Group
From g In gj.DefaultIfEmpty
Where isNothing(g)
Select r=dl).CopyToDataTable

In case of empty results are expected CopyToDataTable is to shift to a more defensive approach e.g toList and Result.Count Check

grafik
grafik707×346 46.6 KB

So for this scenario we do use Join DataTable or a LINQ. But it is not recommended to re-implement a join datatable with a for each / filter construct.

8

For Each is your fastest option. Everyone needs to stop with this “avoid For Each” obsession.

If you use other methods, like copy then filter then merge etc, you’re still processing the same number of rows, just processing them more than once.

https://forum.uipath.com/t/there-is-no-need-to-try-to-avoid-for-each-loops-on-large-amounts-of-data/

9

Why are you using Filter Datatable inside the For Each? That makes no sense.