Comparing Two excel files

Hi,

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.

Please help its urgent

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()

2 Likes

Hi,

I really appreciate your quick response.
But i would me more than happy if you can provide me with a working flow .
Thanks in advance

I think this should have it.

3 Likes

Hi,

And if we want to compare certain columns out of many columns in a data table how can we do that ?

Hi,

Your solution i think compares only the first column .If i want to compare all the columns of a spread sheet how can i do this ?

please i will be very greatful for a quick response

can you please help

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()

Hi,

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

?Can you please help …It is very urgent

Something like this?

(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

can you please tell me what is the key number
I am getting an error that ) end of expression expected

That would be the column name you specified above. I’m checking if that column has any value.

(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()

getting an error ) expected …

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

we need to find all those rows present in dt2 but not in dt1 along with the key value corresponding to each row present in dt2 and which is col0

Hey,

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()

Hi,

The above method does not give any output if there is only one row in data table.
It throws an exception saying the source contains no data rows