Compare two data tables in different excel files

datatable
excel
activities

#1

Hi Guys,

i have a two different excel sheets ,here i need to do, first take any row value from excel sheet 1 and search the value which we picked in excel1 in to another excel sheet . if value is there in excel 2, ok no issue …if value is not there we need to through exception. this is the scenario …please any one help me on this.


Finding a value from each row in a column in one excel with another column in another excel
#2

Hi @Vamshi

There are some examples here:


and here:


#3

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.


#4

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.


#5

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.


#6

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)


#7

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)


#8

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


#9

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)


#10

Hi @aksh1yada

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


#11

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


#12

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.


#13

Use if condition and check if Output contains any rows.

LinqExp.Any or LinqExp.Count >0