How to compare two excels in uipath?

Hi Guys,

i have two excels .in first excel i have id, name and pan id and second excel has id,name and pan id
if the employee matches in both the excels…the out put - in first excel sheet1, in column 4 it should return as a matched profile with the green color and else it should return as a non matched profile with red color.

Can anyone of you help me here ,how to do this task.

Thanks in Advance,

Read both excels with Read Range, once you have them in separate DataTable apply foreach for datatable and get row item name and if name matches use write range to write in excel sheet

3 Likes

hey…i have tried the same way.But no luck :frowning:

1 Like

Hello,

Initially, you need to identify unique value(For example pan id or id is unique). You have to compare pan id from excel 1 and pan id from excel 2.

Procedure:

1)Read Excel 1
2)Read Excel 2
3)For each row in a data table
4)Get row item (Pan id) (1st excel) (You will be getting each row in pan id column)(Stored in a variable)
5)Get row item (Pan id)(1st Excel) (You will be getting each row in pan id column)(Stored in a variable)

Compare 2 variables using if
Write range into Excel

See attached workflow, ExcelSheet2 would have the result once you run it -

Main.xaml (17.9 KB)

ExcelSheet1.xlsx (8.5 KB)
ExcelSheet2.xlsx (8.5 KB)

2 Likes

Hi,

I have to compare Data from two separate sheets of same Excel columns and write the status as pass or fail in first sheet.but,iam unable to do it,since iam new to ui path.please,provide me solution.

thanks.

One of my friend “Anmol” helped me to find out the difference and create an excel with diff. Use the attached
He has used the below “DataTable2.AsEnumerable().Where(function(row) Not DataTable1.AsEnumerable().Select(function® r.Field(Of String)(“Temp”)).Any(function(x) x = row.Field(Of String)(“Temp”))).CopyToDataTable()”
AccuWeather.zip (26.5 KB)

hey how to use this code to compare DOUBLE values. Where should I put the convert.todouble?
When used the below code getting castin error. Can you please help?

DataTable2.AsEnumerable().Where(function(row) Not DataTable1.AsEnumerable().Select(function® r.Field(Of double)(“Temp”)).Any(function(x) x = row.Field(Of Double)(“Temp”))).CopyToDataTable()

Hi i tried executing this code it checks only first row with all rows in second sheet…how do u check same row with sheet 1 and sheet 2

read excel 1 and take one for each row pass values in for each rows of excel sheet 1 and take another for each activity for excel 2 , and compare it using if condition like row.item(0) contain row.item1(0)

Hey this is really interesting! Are you able to explain what the code is doing step by step? I want to learn it. Also what would I do if I only had 1 column for both excel files and it didn’t have a header named “temp”? What if it had no header at all?

Looking forward to hearing from you!

Hello,
That is the only way, else you will have to use desktop and web automation to tweak the results
Like open the browser, got to some ur where excel can be compared.
Download the compared file and use it

Why dont you try using For Each, anyhow that is going to be much faster than human calculating it and comparing.

Hello!! Thanks for your solution. Your solution is very helpful.
Can this solution will be working fine if more files are added into folder. I want dynamic solution for it. I have some Employee Records Files data in column wise such as Employee_Name, Employee_ID, Employee_Age_Employee_Gender, Employee_Job_Role, Employee_Salary, Employee_Attendance, Employee_Job_Role, Employee_Job_Description in 3 excel files and One Master Excel file in which I have all these columns in row wise data.
I want to search these Employee Records files and match data with Master Excel file and add entries into Master Excel file accordingly. For example-: In Employee Records File Emp_Name it might be Name in another excel Records file. Accordingly search for Employee_Name and assign value in Master Employee Records File. The Employee Records files should be searched for those entries present in the Master Data File and Add Data accordingly in Actual Headers columns. I have added header name for Employee Records 1.
If any field is not present it will be blank in Master File. I want dynamic solution working for it. If there are more files added in directory. It should be working fine. All File Name such as ABC1 initial should be added into Masters Employee Records file. I am sharing you the Employee Records Excel file and Master Record Excel file. Please suggest proper solution for this issue as early as possible.
Also the challenge is that headers position in all Employee Records are not fixed. They are dynamic, Please help me out with this solution as early as possible.

ASFTAS_AQW_MOSIP_1212023230.xlsx (9.9 KB)

BBSDSDCD_2_1007223232.xlsx (8.9 KB)

DEEASAS_MQW_1612121212.xlsx (8.7 KB)

Master_Employee_Result_Header.xlsx (9.1 KB)

Regards
Rohit Lanjewar