Linq query to match columns of two excel files

[enthusiastic]

Hi,

I am working on a scenario where I have two excel files with two columns namely Column A and Column B. I am writing linq query to match the row of both the excel files. So far I have written this, but its not working fine. For one excel i am looping it by using for each data row.
search =row1(“ColumnA”).ToString.Trim+" “+ row1(“Column B”).ToString.Trim
after that
results= dt.Tables(0).Select(“ColumnA++Column B ='”+search+”'").FirstOrDefault()
where dt is for the second excel file.

your help in this regard will be highly appreciated

[supermanPunch (Arpan)]

@enthusiastic

I am writing linq query to match the row of both the excel files

Hi @enthusiastic ,

Not so sure on what is the exact output required, If you could provide us with Sample Input Data and the Expected Output for it, we could maybe provide you with appropriate suggestions.

However, check the below posts on retrieving the common rows :

Get Common rows from 2 tables to Resultant table and other manipulation

Hi @RobotReader Welcome to Community!! ’ Find common rows commonRows = Table1.AsEnumerable().Intersect(Table2.AsEnumerable(), DataRowComparer.Default).CopyToDataTable() ’ Find unique rows in Table1 uniqueRowsInTable1 = Table1.AsEnumerable().Except(Table2.AsEnumerable(), DataRowComparer.Default).CopyToDataTable() ’ Find unique rows in Table2 uniqueRowsInTable2 = Table2.AsEnumerable().Except(Table1.AsEnumerable(), DataRowComparer.Default).CopyToDataTable()

[enthusiastic]

thank you for ur prompt response.

outer is for each data row… looping on dt1 , storing the the values in search variable
search =row1(“ColumnA”).ToString.Trim+" “+ row1(“Column B”).ToString.Trim

after this
results = dt.Tables(0) .Select(“ColumnA++Column B =‘”+search +”’").FirstOrDefault()

the values from second datatable dt is stored in results

after that if condition is used to check whether results has some value matched or not like this “result is Nothing”, and then values are updating in excel accordingly

[supermanPunch (Arpan)]

@enthusiastic

search =row1(“ColumnA”).ToString.Trim+" “+ row1(“Column B”).ToString.Trim

after this
results = dt.Tables(0) .Select(“ColumnA++Column B =‘”+search +”’").FirstOrDefault()

@enthusiastic ,

We are still confused on this implementation, and what are the types of dt , is it a DataSet type ?

@enthusiastic

I have two excel files with two columns namely Column A and Column B.

The query was more towards whether you could provide us with the Input Excel files that you were working on. And also maybe provide what is the Expected Output for that inputs.

[copy_writes (chethan)]

So You are searching the column A value (Excel 1) in Column B value (Excel 2) is that you are doing.

Your question is not clear please explain bit about your question so we can help you.

[Gayathri_Mk]

dt1.AsEnumerable().Where(Function(row) dt2.AsEnumerable.Any(Function(x) x(“Item Name”).ToString=row(“Item Description”).ToString)).CopyToDatatable

you can try this and modify accordingly the column names.

[J0ska]

What you do is INNER JOIN of two tables.

Alternatively to what @Gayathri_Mk proposed you could use following LINQ query

dt3 = dt2.clone
dt3 = (From a In dt1.AsEnumerable 
Join b In dt2.AsEnumerable
On a("Column A") Equals b("Column A") And a("Column B") Equals b("Column B")
Select dt3.LoadDataRow(b.ItemArray,	False)
).CopyToDataTable

Cheers