Comparing two separate Excel files and keeping only unique results

Hi, I have two separate excel files of 1 column each but might have different number of rows. I need to compare these two files and find the difference between them. For example:
File 1 contains:
123
abc
456

File 2 contains:
123
abc
xyz123
456
789

So I need the result to look like this:
xyz123
789

So I have a workflow, but I cannot make it work. My workflow is:

  1. Read excel 1 and keep it in a datatable, dt1.
  2. Read excel 2 and keep it in a datatable, dt2.
  3. For each row1 in dt1
    For each row2 in dt2
    If row1(0).ToString = row2(0).ToString
    Break
    Else
    Write cell in another excel file, say output.xlsx, value row2(0).ToString
    Increase counter by 1
    Break

The counter variable has default value of 1 and scope is entire sequence including step 1 and 2. This isn’t working…so really in need of your help!

Thanks in advance!

@abdul.aziz1

Check below for your reference

Tutorial : How to check a string is present in Excel Sheet || Part - 1

Also you can for try as below

Hope this helps you

Thanks

@abdul.aziz1
find some starter help here:
Find_Common_NonCommon_By1Col.xaml (12.3 KB)

Also it is recommended to explore the Join DataTable Activity

Hi @ppr, I think join datatable activitiy would not be able to use in this case

when we join the two datatables with Join type FULL we can detect in the join result

  • matching items - left join col AND right join col are not null (and the same)
  • not matching in right table - left join col is not null and right join col is null
  • not matching in left table - left join col is null and right join col is not null

Well explained also here

U can check here

Same scenario for this question but cannot apply join datatable

I think join datatable is a kind of looping

@NIVED_NAMBIAR
lets do one thing: as it is the post of @abdul.aziz1 just keep it clean for his topic. In case of your question just open a new topic. The join operator will still be = and not != (!= has a different meaning, see description above on how to detect the matchers and non matchers)

2 Likes

@abdul.aziz1

Please find attached a possible solution to your exercise.
UnqValuesFrom2Dt.xaml (9.8 KB)


image

The idea behind it is to merge the 2 datatables and then filter the values that are found only once in the merged datatable.

Happy automation!

2 Likes

Thanks a lot for your help! But I get this error message: “The source contains now Data Rows”…what’s the issue?

I guess that might happen if there are no unique values from the 2 datatables. Basically, the 2 datatables are identical.

I made some changes to the initial workflow in order to handle this.

UnqValuesFrom2Dt_v2.xaml (13.7 KB)

1 Like

working now! i find the enumerable function pretty confusing! still, thanks for your invaluable help!!!

1 Like

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