How to compare two data tables and add rows if no match

datatable
studio

#1

Im trying to filter through two datatables and find any matches were the column A (name) is the same between the two datatables.

  1. I have a large datatable (dt1) that containes a list of customer names and the value
  2. I run a report and extract that data into another datatable (dt2)
  3. If the Name from dt2 = the name from dt1. Then we update dt1 with the value from dt2
  4. If the Name from dt2 <> the name from dt1. Then we add a new row to the datatable (dt3).

Right now i have a build datatable (dt3) at the top of my sequence. I use that datatable (dt3) to store the data from conditional. I have a foreach that loops through dt1 and then another foreach inside the 1st foreach that loops through dt2. My if condition is inside the 2nd foreach loop.
The issue im having it that im gettting duplicate rows inside dt3. I think its from having a nested foreach, but im not sure how to fulfill my requirements without it. Any ideas on how to resolve this?

image

image


Comparing Data Tables
Remove a data table from another datatable
#2

Hi there @KaylaC,
When copying a DataRow from one DataTable to another, you may need to convert it into an ItemArray.

This will allow you to use the Add Data Row activity, but with an ItemArray in place of a given DataRow object.

Thanks,
Josh


#3

Hi @Mr_JDavey,
Thank you so much for your time and response!
I currently add the data row using the ArrayRow Input. My datatable is getting the correct data, the problem is, is that i’m getting multiple copies of the same rows. For example, my data table looks like this at the end of my automation:
row1
row1
row1
row2
row2
row2
row3
row3
row3

I’m pretty sure this is due to the add data row activity being inside a nested for each loop. So its running through the 2nd foreach loop the same amount of time its running through the 1st foreach loop.

I’m just not sure how to compare the items in data table 1 with the items in data table 2 without having the two foreach loops.


#4

I solved my issue! I followed the example on

I added a Boolean inside the nested for each, if statement
found = true, and added a break to break out of the inner most foreach loop

I then added another if statement inside the 1st foreach loop where
found = false
add data row.



image