Comparing two big data

Help Studio
, ,
1

Hi,
I have two excels and both have 200.000 rows. For comparing I get data as datatable from excels and use nested two “For Each Row in Data Table” activity. But it is taking too long(3 hours). Is there any other way to this?

image
image676×647 17 KB

2

in run or debug mode?

Yes. Unfortunately we cannot derrive all reuqirements (e.g. check for a match, grab data from second datatable). Maybe you can share some more details

Alternates:

  • Join DataTable Activity
  • LookUp Datatable Activity
  • LINQ Statements

A general building block could be

Assign Activity:
dictLK | Dictionary(Of String, String) =

dt2.AsEnumerable.toDictionary(Function (x) x("number").toString.Trim, Function (x) x("Stat...").toString.Trim)

For each row in datatable Activity | CurrentRow in dt1

  • IF: dictLK.ContainsKey(CurrentRow(“number”).toString.Trim)
    • Then → Assign Activity:CurrentRow(“Stat…” = dictLK(CurrentRow(“number”).toString.Trim)
3

For additional Performance Tuning also have a look at the Parallel For each Activity

4

Hello @murat.karasoy
You can try get the unique calues from datatble2 and create one dictionary (key, value) key as number column and value is status column and directly provide assign inside thefor each row in datatable1.

For unique values you no need to read full table also. you can read number and status column only by providing datatable2.defaultview.totable(true,“number”,“status”) assign dict inside this