Compare two data tables in different excel files

Hi @ovi

already i have tried that example for my scenario but i couldnā€™t able to get the success message . Please if you have any other example could you please post me.

1 Like

You could do something like this:

where dt1 is datatable from Excel1 and dt2 is from Excel2

ForEach row In dt1
If dt2.Select("[columnname2] = "+row.Item(ā€œcolumnname1ā€).ToString)).Count > 0
Then ā€¦ //do actions here

So in other words, you loop through each row, and filter the second table using that value. If it finds any rows with that value, the .Count will be > 0 and become true. If it doesnā€™t find anything then the .Count will be Zero.

Thanks.

4 Likes

HI ClaytonM,

Thanks for your response i am trying like as you said but didnā€™t get the flow ā€¦if you dont mind can you please post me the flow of solution please help me.

1 Like

Hi ,
I have tried like below ,please check this and if changed need please let me now.Book1.xlsx (20.6 KB)
Book12.xlsx (8.6 KB)
excel data tesing.xaml (11.2 KB)

1 Like

You can fetch the unmatched rows in one line code. I hope this is similar to your requirement. Not necessary that you need to use it as it could be a bit confusing.

comparedt.xaml (10.4 KB)

2 Likes

Hey @Vamshi

You can use Following linq to get what you want :slight_smile:

Scenario is fetch the tags data from excel1 column and find in excel2 in Controller column only.

Datatable Final_result = (From x In dt1.AsEnumerable() where (From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On a(ā€œControllerā€).ToString() Equals b(ā€œCritical tagsā€).ToString() select a).Contains(x) select x).CopyToDataTable()

726.xlsx (8.9 KB)
Book1.xlsx (20.6 KB)

comparedt.xaml (11.2 KB)

Regardsā€¦!!
Aksh

6 Likes

HI aksh,

Please help me on this

Requirement:

Please find the attachment.

If there is a blank cell in any of the column and email should be triggered.
If there is an duplicate row( The entire row should be a duplicate ) an email should be triggered.
testfile.xlsx1 (199.1 KB)testfile.xlsx (199.1 KB)

Hi @aksh1yada

Your LINQ givs results where they match.
What would the LINQ be if you wanted to see where they donā€™t match?

Hi @aksh1yadav,
Can you please help me in understanding this equation:

Datatable Final_result = (From x In dt1.AsEnumerable() where (From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On a(ā€œControllerā€).ToString() Equals b(ā€œCritical tagsā€).ToString() select a).Contains(x) select x).CopyToDataTable()

Thanks in advance.

Regards,
snehal

Hi I used this for comparing two data table. But when both data tables are same, it throws an error as ā€˜the source contains no data rowsā€™. could you please help in resolving this.

1 Like

Use if condition and check if Output contains any rows.

LinqExp.Any or LinqExp.Count >0

1 Like

I have used below LINQ, hope do same for u.

In_DataTable1.AsEnumerable().Where(function(row) In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of variable type i.e string)(In_DT1_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of variable type i.e string)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable

Hi all,
I have two different datatables of different size.I have to check the all values of single column from one datatable in another column of second datatable.If that value is present I have to get the corresponding value from that same row and replace the value in my first datatable of another column.So basically I can put my scenario in this way.
datatable1: datatable2:
Id(column1) status(column2) id (column1) status(column2)
1 open 1 close
2 close 3 close
3 open 5 open
In the above scenario i have to search whether all the ids from column 1 of datatable is present in column 1 of second data if id is present i have to replace the value of status of column 1 with the value of status from datatable2

How can I use condition?
column a is x and b is 242 then result is deleted
column a is null

@Abubakkar
Elaborate the Use case

1 Like

Hi Akash,

I have 2 Excels and each excel contains 9 sheets of same sheetnames and column names ā€¦Now i need to compare the values in Both the excels corresponding to their respective column names and if the values are same them ā€¦it should write as ā€œsamevalueā€ in the 3rd excel and if not it should print as "values are different " . Can anyone help me with the query .

Thanks in Advance ā€¦!

@Suresh

Give this a try.

Component for Two Datatable Column Match and Get The Matched Records.xaml (5.9 KB)

2 Likes

Hi Rahat,

As i have 9 sheets in each excel to compare , I donā€™t understand whether i need to go for multiple looping statements for comparision .Please suggest me an other way as it is not working for my req criteria.

1 Like

Hi,
You can try this library package. It takes two datatables as inputs and it compares perfectly and provides 3 datatables as result. First table with matched rows. Second table with Unmatched rows from first Input table. Third table with Unmatched rows from Second Input table. I tried other methods such as looping through each row & each column to compare datatables. But it takes more time to execute.

https://go.uipath.com/component/compare-datatables

This can be easily achieved by using a for each row activity for the first datatable(dt1) and a linq similar to the one below.

//For each row for dt1
dt2.AsEnumerable().Any(Function(x) Convert.ToString(x(ā€œColumnNameā€)) = Convert.ToString(row(ColumnName)))

The method ā€œAnyā€ returns a boolean and it can be used to check the condition before throwing the exception in this case.