Compare Excel Files

News Tutorials
1

Rate.xaml (16.4 KB)Excel1.xlsx (8.1 KB)
Excel2.xlsx (8.0 KB)

This is best example for two excel sheets comparison and update the one excel based on other excel. Hope this can be useful for others

7 Likes
2

Hello,
For these kind for informative threads can be tagged in how-to section, please tag how-to, excel to this thread.
Thanks,
Meg

3

Hi @UI_Path1 and @megharajky

could you inform me, what it is using for?
because I try to run program, and nothing happen in excel1 and excel2

thank you

4

Hello @zonda_rugmiaga ,

@UI_Path1 was tring to compare the two sheets, first he was reading both sheets and for each row of sheet1 he was comparing with sheet2 either any matching row exist or not, if matching writing new value to sheet.

I guess as per topic title you were looking for sheet comparer.
Please refer the below flow.

input.xlsx (9.1 KB) SheetCompare.xaml (7.0 KB)

Thanks,
Meg

5

Basicly my problem is I have two excel, first excel is master user and second excel is download data.
I need to compare both files use key name,department and title.

if data from download data cannot find in master user, it will insert new row in master user.
status will be active and insert current date.

if data from master user cannot find in download data, it will changing status in master user become inactive and insert current date.

for excel file and xaml file is attached.
hopefully, you could help me to fix this problem

[quote=“megharajky, post:4, topic:97514, full:true”]
Hello @zonda_rugmiaga ,

@UI_Path1 was tring to compare the two sheets, first he was reading both sheets and for each row of sheet1 he was comparing with sheet2 either any matching row exist or not, if matching writing new value to sheet.

I guess as per topic title you were looking for sheet comparer.
Please refer the below flow.

input.xlsx (9.1 KB) SheetCompare.xaml (7.0 KB)

Thanks,
Meg
[/quote]DownloadData.xlsx (15.0 KB) MasterRPAUserBeforeUpdate.xlsx (13.4 KB) MasterRPAUserAfterUpdate.xlsx (14.6 KB) Main.xaml (18.8 KB)

6

Hello,

What I suggest is split the task into 2 parts,
1st compare the downloaded data with master data and insert the entries,
2nd compare master data to downloaded data for status update.

  1. for comparing 2 sheets take the datatables with same headers(name,dept,title)
    based on that we get the entries which are in downloaded but not in master data, now using that list write the data to master file.

  2. here too compare the datatables with same headders, and based on result update the status in master file. if you able to achive first one second onw will be similar

Thanks,
Meg

8

Hi @megharajky and other UIPATH master

I try use your program,
it is work,
but it will compare all column in every sheet.

if I have two sheets.
sheet 1 contain of 10 columns,
and sheet 2 contain of 5 columns.
if i only want to compare column 1, 2 and 3 only from each sheet.
how i can do it?
please kindly to help me.

9

Hello,

To get the specific columns to compare the datatable you can use Defaultview method

[datatablename].DefaultView.ToTable(True,“col1”,“col2”,“col3”)

once you read range from both the sheets take the columns which you want and use the linq query to compare.

Thanks
Meg

1 Like
10

thank you @megharajky.

and currently i face problem to prevent error when both files does not have any gap.
could you help me again?

11

I didn’t get you, can you explain whats the issue in brief please.

12

I compare download data with master user.
if download data have new data, it will insert to master user.
if download data and master user has same data, it should only send messsage “no new data”.
but currently if download data and master user has same data, it is error.

13

Hello,

It is showing error because the datatable which you are storing the result is not initiated.

To initiate you can use two methods

  1. Build datatable
  2. Default variable value as new system.data.datatable

both are demonstrated here use any one:

image
image768×393 15.9 KB

Thanks,
Meg

14

Thank you for your help @megharajky
i finish for this case.

I use this way:

After comparing two datatables,
I use this:
dt1.AsEnumerable.Except(dtCommon.AsEnumerable,DataRowComparer.Default).Count

If count>0
I will continue compare
else end task.

1 Like
15

Hello @zonda_rugmiaga, do you have the updated workflow of you case? Mind if you can share?

I have the same scenario now. I want to study how the workflow.

Thank you :slight_smile:

1 Like
17

@redbee

please use this to compare download data with master user.
if download data have new data, it will insert to master user.
if download data and master user has same data, it should only send messsage “no new data”.

CompareData.xaml (12.3 KB) Master User.xlsx (15.6 KB) DownloadData.xlsx (15.0 KB)

if you have any problem, let me know

18

Hi @UI_Path1

Here is a detailed article on that :slight_smile:

Regards,

2 Likes
19

i am not able to open the .xaml file

21

Awesome!!
@sharathraju489 :smile:

1 Like
22

Please find attached file for comparing Two Excels.

Compare_Excel.zip (28.6 KB)

23

Please tell me, I’m a newbie in this business. If I need to find common elements between two Excel data tables (two files), and not between the created tables by the “Build DataTable” action. What operations need to be performed?