How to compare two excel files, find the same and the different content, when write to a new excel files

Learning Hub Academy Feedback
, , , ,
1

dear all,

i had met a problem need to deal with it . there are two excel files , how could i comapre the two excel

files, find the same and the different content between the two excel files, and output the compare results,

write to a new excel files. Anybody could guide to me , give me a demo code ?

thanks a lot for help!!!

2

Hi @chrisjiyiwei,

We can get excel data in datatable using read range activity, lets say 1st excel data as dt1 and 2nd as dt2.
by using following code in assign you’ll able to get unmatched rows.

dt1.AsEnumerable().Where(Function(row)  Not dt2.AsEnumerable().
Select(Function(r) String.Join("",r.ItemArray)).Any(Function(x) x = String.Join("",row.ItemArray))).
CopyToDataTable()

And to get matched rows, just remove Not from the code.

Above code basically search for rows from dt1 into dt2, so to get common rows, there’s no problem but to get unmatched row you have to search rows from dt1 to dt2 as well as dt2 to dt1 and merge both data’s.
Here I’m attaching sample workflow for your better understanding —> DataRowCompare.xaml (11.3 KB)

Note —> first check both build data table’s data and then run the workflow. In first MessageBox you’ll have common rows and 2nd MessageBox with unmatched rows from both data.

3

hey samir ，

i check the workflow, could i write to the output datatable to an excel files? your workflow is only focus on text

4

i use your demo workflow , it generates error Assign: The source contains no DataRows.

how could I solve it ?

5

@chrisjiyiwei

Yes, you can use write range to save datatable data in excel file.

It is working perfecting on my side, if you’ve altered build datatable data then there’s possibility of having no row in output datatable.

Have you run that workflow before using logic on your data?

6

i use my excel files, read range as dt1 and dt2 , it generates that error, how could i solve it ?

7

Okay then, that means either your 2 files has no common data (Both data have different rows) or no unmatched row (Both data have similar rows)

So to handle this situation you must put those assigns in seperate Try-Catch assign should be in try and catch should contain messageBox to specifying no rows. (For testing)

8

Hi @chrisjiyiwei

Below is the link where i have already solve this type of query

Hope this may help to solve your query
Mark as solution if this helps you and like it :innocent:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

1 Like
9

could you give me some demo workflow? thanks a lot in advance

10

@chrisjiyiwei
find some demo flow here:
Get_MatchingNonMatching_JoinActivity.xaml (13.7 KB)

if a more custom compair is needed LINQ can help. For this have a look here:
DataTable_Join_1Col.xaml (10.3 KB)