Create new table by comparing two other tables: New table contains items from table1 that were not found in table2

we can mention the column index that usually starts from 0 for the first column
based on which we can just mention the column index without double quotes like this

Datatable Out_NonMatched_Data = DTPart.AsEnumerable().Where(function(row) Not DTFull.AsEnumerable().Select(function® r.Field(Of String)(columnindex)).Any(function(x) x = row.Field(Of String)(columnindex))).CopyToDataTable()

@Asanka

Hey I tried it and got this error:

Workflow looks like this:

Code is:
DTPart.AsEnumerable().Where(function(row) Not DTFull.AsEnumerable().Select(function® r.Field(Of String)(0)).Any(function(x) x = row.Field(Of String)(0))).CopyToDataTable()

In READ RANGE kindly mention the range as “” and also ensure that those sheets have data in it

@Asanka

Added in the quotes and they contain data:

Still same error. Does it have anything to do with the data being string?

any idea why same error?

No…how would i use join data tables?

This way works but in the opposite fashion. Is there just not a way to change it from equals to NOT equals?

(From da In dtfull.AsEnumerable
Join db In dtpart.AsEnumerable
On da(0).ToString.Trim Equals db(0).ToString.Trim
Select da).CopyToDataTable

@Asanka
Have Look on this topic
Unmatched records using LINQ, similar to your question along with Demo xaml as well

As you mentioned finding the Common rows we’re Not the Problem at your so end. So in the xamls you will find on how to use the commmons rows to identify the unmatching rows

Ah
Kindly have a view on this
hope its resolved
BlankProcess6.zip (10.0 KB)

Cheers @Asanka

1 Like

Hey,

Im trying to understand the code. What does d1 and d2 mean?

d1, d2 are like variable names, that are used similar to da, db from your sample from above. More clear?

Yeah but where are d1 and d2 defined? This is what I dont understand. What do they represent?

Yeah it worked! Thank you @Palaniyappan !!!

But I have questions! I want to become a better coder. Can you please take me through step by step what these two lines of code are actually doing?

(From d1 In DTFull.AsEnumerable
Join d2 In DTPart.AsEnumerable
On d1(0).ToString Equals d2(0).ToString
Select d2).CopyToDatatable

AND

dtfull.AsEnumerable().Except(dtfinal.AsEnumerable(),DataRowComparer.Default).CopyToDataTable()

I kind of get it…but not enough. I want to be the best coder I can! Not just a copy and paste guy.

it is representing the datarow of loop, defined in the LINQ statement. and it can be used for later computation it get a variable name, e.g. d1

Oh so it represents the specific enumerable it is on. Like row in a for each…

@Asanka
(From d1 In DTFull.AsEnumerable <- looping over DTFull
Join d2 In DTPart.AsEnumerable <- looping over DTPart
On d1(0).ToString Equals d2(0).ToString <-- Checks if both rows are matching the join clause
Select d2).CopyToDatatable <- selects from the matches the d2 row
all d2 are copied to a datatable

dtfull.AsEnumerable().Except(dtfinal.AsEnumerable(),DataRowComparer.Default).CopyToDataTable()
is working via so called set operations

all rows from dtfull are taken as long these rows are not present in dtfinal, also called the minus operation

https://www.tutorialsteacher.com/linq/linq-set-operators-except

1 Like

Okay seriously…that is so cool. I really appreciate you taking the time to explain this stuff to me @ppr

@Asanka
Statement from @Palaniyappan and that one from my referenced XAML (see link from my post) are close and similar in the meaning. What can happen is that the except statement is returning no rows, e.g there are no uncommon rows. then CopytoDataTable is throwing an exeception. Have a look on the post/on my xaml on how to save up this part with checking the row count and only using copytodatatable if rows are returned.

Happy automation

1 Like

Hey,

I took a look and added in what you said. Thanks again!

Big props to @ppr and @Palaniyappan

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.