Comparing two data tables


#1

Hi friends,
Need a quick help!

I want to compare two data tables. I need to compare each and every cell of first data table with those of the second data table (I. E., I need to find a row based on s.no. If that matches then each and every cell of that row has to be compared. ) and add those cells that aren’t same into a separate data table.

Can anyone help me with this?

Thanks in advance.

Regards,
Ananthu


#2

Hi Ananthu,

You can use the DataTable.Rows.Contains method to check if the table already contains a row with same data. The check is done comparing the values in the columns set as Primary Key. So, first we need to set the primary key to the columns we want to use for check.

See attached example on how to use the DataTable.PrimaryKey property and DataTable.Rows.Contains method.
CheckDuplicates.xlsx (8.5 KB)
CheckDuplicates.xaml (10.9 KB)

Regards
Silviu


Check if two strings are equal
#3

Hi Silviu,
Thanks ! But, I need to know which cell exactly is not matching. I have to print that.

Note: The Data table contains more that 25 columns. So, I have to point out in which column exactly the mismatch is happening.

Regards,
Ananthu


#4

Please provide an input file and the expected result, it will be much easier to understand this way.


#5

Hi Silviu,
Thanks for your reply! I resolved it myself.

Regards,
Ananthu


#6

[DataRowComparer] maybe more simple.
Here is my code.
comparing-two-datatables.zip (10.0 KB)


#7

@lainh hi,

you have to do it with a for each row inside of another for each row.

For example:

Call the rows of the first for each -> row
Call the rows of the second for each -> line

Then, you can check the values using:
if(row(“The name of your column”).toString <> line(“The name of your column”).toString)

-> writeLine : “We have a dismatch”

I hope it can help you, I guess it is easy to understand.

Bests,
Pablo


#8

Hi @Pablo_Sanchez

I did what you said in the [invoke code] activity with different method.
You can check the xaml flle on [invoke code].

My way is

  1. use [DataRowComparer] to found if the tow rows are different
  2. to compare the columns of the datarow above to find which pair of cells is unmatched in loop.

#9

@lainh I’m sorry… I don’t have so much time now… so much work to do :disappointed_relieved:

If I have time I will check the .xaml, I hope you will get help before as I will be able to check it

Bests,
Pablo


#10

@Pablo_Sanchez
What I said is before your post#7 , I have already did what I said in post#9


#11

Hi Pablo,
Thanks for your reply!
I have used the same logic in my work flow.


#12

Hi @Ananthu…Could you please share your solution, i have a similar requirement.

Thanks again
Gaurav


#13

Hi Gaurav,
I have attached the comparison logic workflow. Kindly let me know if you got struck somewhere.

Thanks and regards,
Ananthu
Comparison logic.xaml (22.3 KB)


#14

@Ananthu Thank you so much, i am reviewing it now.

Thanks again
Gaurav


#15

Dear @Ananthu
I tried to use your Comparison logic.xaml file.However, when process comes to Assign counter=counter+1 I got message:
Object reference not set to an instance of an object. This error usually occurs using a variable with not set value (not initialized).

I will appreciate if you can explain how to fix it.
test1.xlsx (16.6 KB)
DD.xaml (24.1 KB)

In the finale I want to get 3 rows on the sheet C which contains differences…

Thank you for the advice and help!


#16

Hi @Aggi ,
Just initialize the counter variable with 0. That should resolve your issue.
Kindly let me know if you face any other issues.

Thanks and regards,
Ananthu


#17

Dear @Ananthu, thank you. it is working!


#19

Dear @Ananthu,

would you be so kind to explain me some problem with code. I use it and I got mistake…it is count every second column of the table (not each) AExample.xaml (31.8 KB)
test1.xlsx (17.4 KB)


#20

Hi @Aggi,
Will look into it tonight. I’m in office.

Thanks and regards,
Ananthu


#21

Hi @Aggi,
The problem with your test1 excel sheet is that it haven’t got any unique element to map across sheets for comparison.

In the attached workflow, I have used the row index as unique element and did the comparison. Kindly let me know if you face any issues.
AExample.xaml (34.9 KB)

Thanks and regards,
Ananthu