I want to compare two excel files in such a manner that after comparison
1.I would get data rows that are not present in the second file but present in first
2.I would get data rows that are present in second file but not present in the first.
This should work, you just need to switch your datatable variables for 2nd case.
dtMissing = (From x In dt1.AsEnumerable() where Not (From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On a(colname).ToString() Equals b(colname).ToString() select a).Contains(x) select x).CopyToDataTable()
You could do below way, will check if there is any easier way.
(From x In dt1.AsEnumerable() where Not (From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On a(colname).ToString() Equals b(colname).ToString() AND
a(colname1).ToString() Equals b(colname1).ToString() AND
a(colname2).ToString() Equals b(colname2).ToString() select a).Contains(x) select x).CopyToDataTable()
Your solution seems to be working.But i have another issue.
Say for example there are two excel sheets sheet1 and sheet2…
sheet1 and sheet2 are to be compared on certain columns such that
the result should be two new sheets result1 and result2
result1 - contains rows present in sheet1 but not in sheet2
result2-contains rows present in sheet2 not in sheet1.
How ever there is a column key in sheet1 which is not compared but the requirement is the result1 in addition to containing the rows present in sheet1 should also contain the key number corresponding to each row present in sheet1.
Please note this sheet column is not suppose to be compared.
Please help on this one
(From x In dt1.AsEnumerable() where (x(“KeyNumber”).ToString <> string.Empty) and Not (From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On
a(colname).ToString() Equals b(colname).ToString() AND
a(colname1).ToString() Equals b(colname1).ToString() AND
a(colname2).ToString() Equals b(colname2).ToString() select a).Contains(x) select x).CopyToDataTable()
Say for example there are two excel sheets sheet1 and sheet2…
sheet1 and sheet2 are to be compared on certain columns such that
the result should be two new sheets result1 and result2
result1 - contains rows present in sheet1 but not in sheet2
result2-contains rows present in sheet2 not in sheet1.
How ever there is a column key in sheet1 which is not compared but the requirement is the result1 in addition to containing the rows present in sheet1 should also contain the key number corresponding to each row present in sheet1.
Please note this sheet column is not suppose to be compared.
Please help on this one
(From x In dt2.AsEnumerable() where (x(col0).ToString <> string.Empty )Not (From a In dt2.AsEnumerable() Join b In dt1.AsEnumerable() On a(col1).ToString Equals b(col1).ToString And a(col2).ToString() Equals b(col2).ToString And a(col3).ToString equals b(col3).ToString and a(col4).ToString equals b(col4).ToString and a(col5).ToString equals b(col5).ToString and a(col6).ToString equals b(col6).ToString and a(col7).ToString() equals b(col7).ToString and a(col8).ToString equals b(col8).ToString and a(col9).ToString equals b(col9).ToString and a(col10).ToString equals b(col10).ToString and a(col11).ToString equals b(col11).ToString and a(col12).ToString equals b(col12).ToString and a(col13).ToString equals b(col13).ToString and a(col14).ToString equals b(col14).ToString and a(col15).ToString equals b(col15).ToString and a(col16).ToString equals b(col16).ToString select a).Contains(x) select x).CopyToDataTable()
FY1 here col0 is the column that contains the key and is present in sheet1 ,We are finding all those rows that are available in sheet 1 alongwith the unique key corresponding to each row present in sheet1.
However the sheet2 does not have any value in col0…
please help on this one
I do not think we need to check for col0
I automatically got that key after comparsion
Please can you confirm if this is correct
(From x In dt2.AsEnumerable() where Not (From a In dt2.AsEnumerable() Join b In dt1.AsEnumerable() On a(col1).ToString Equals b(col1).ToString And a(col2).ToString() Equals b(col2).ToString And a(col3).ToString equals b(col3).ToString and a(col4).ToString equals b(col4).ToString and a(col5).ToString equals b(col5).ToString and a(col6).ToString equals b(col6).ToString and a(col7).ToString() equals b(col7).ToString and a(col8).ToString equals b(col8).ToString and a(col9).ToString equals b(col9).ToString and a(col10).ToString equals b(col10).ToString and a(col11).ToString equals b(col11).ToString and a(col12).ToString equals b(col12).ToString and a(col13).ToString equals b(col13).ToString and a(col14).ToString equals b(col14).ToString and a(col15).ToString equals b(col15).ToString and a(col16).ToString equals b(col16).ToString select a).Contains(x) select x).CopyToDataTable()