How to compare two excel files for one column

Help
1

Hi All,

I have 2 excel files, one excel files with one sheet and the other file with three sheets.

Description

Excel file1 has1 sheet with 30+ columns out of which only email id have to be compared with all the three sheets of the Excel file 2 and write back the matched mail id information with Excel file 1 data in result file.

Excel file 2 has 3 sheets with 5+ columns out of which email id is the one of the column that has to be compared with the Excel file 1.

Result file should have the matched email ids information as per excel file 1 information (i.e. 30+ column) from excel file 1 and excel file 2. Added to that the column 1 from excel file 2’s sheet 1 and sheet 2 has to be added. i highlighted the it as column 12 in yellow.Sample Files.zip (28.4 KB)

attaching the sample files for your perusal.

Your help is highly appreciated.

Regards,
Selvarani

2

Hi @selvarani1390,

Just a suggestion, You can use existing activities to compare each row and column of both the excels If time is not a factor for the entire process.

If you need to complete it asap, then try Invoke code activity and write a bit of code for VLOOKUP to compare the columns .

3

Hi @HareeshMR,

Can you please suggest which activity to use and where to refer for writing the code for VLOOKUP.

Since this is the first time for me to work on this kind of logics I am really getting struck in the beginning of the process itself.

If you can give me some sample code or Workflow for this logic i can take that as lead and try to complete it.

Thanks for your suggestion.

Regards,
Selvarani M

4

Please find the attached workflow to compare sheet1 from both Excel1 and Excel2.

Same way you can loop for sheet2 and 3 also.

compareExcel.xaml (14.2 KB)

Hope it helps and let me know if you still have doubts.

Thanks,
MR

5

Here you can find the duplicate row from excel,go through it
Displaying duplicate row with index number.zip (8.7 KB)

6

HI ,

For “for each row” activity am getting the error as “Could not find member ‘CurrentIndex’ in type ‘http://schemas.uipath.com/workflow/activities:ForEachRow’. Row: 104, Column: 28”

I updated the packages also but still Activity couldnot be loaded because of the error in xaml.

Please suggest what should i do now

Regards,
Selvarani M

7

@selvarani1390

Close the xaml file and open xaml using notepad by right clicking on xaml file, the find

 CurrentIndex "{x:Null}"

Remove it completely and find for other occurence also and remove all, Save the file and open normally again

8

Hi @Manjuts90,

there is no such content in xaml. can you please help me with the workflow.

And also please suggest some links to start learning for regex and understanding the concepts of datable and arrays for UiPath.

Regards,
Selvarani M

9

@selvarani1390

Sorry it will be like below

CurrentIndex=“{x:Null}”

10

Hi @Manjuts90,

Thanks :slight_smile:
Can you please suggest some links to start learning for regex and understanding the concepts of datatable, looping and arrays for uipath.

Regards,
Selvarani M

11

@selvarani1390 U can search regex compiler in online, u will get many compiler and documents regarding it. I learnt concepts of datatable, looping and arrays in uipath forum itself. I didn’t followed any specific webpage or documents.

12

Hi @Manjuts90,

when i use this condition,
image

it is showing the below error,

image
image904×303 11.3 KB

my varaiables are declared correctly any everything is in scope., please let me know what would be the prob.

Regards,
Selvarani M

13

@selvarani1390 It should be like

variable1.equals(variable2)

not like
variable1 equals variable2

14

Hi @Manjuts90,

Can you please provide me some solution for the below problem, i tried it with my own workflow but it iis not fetching the right data. your help is highly appreciate able.

I have 2 excel files, one excel files with one sheet and the other file with three sheets.

Description

Excel file1 has1 sheet with 30+ columns out of which only email id have to be compared with all the three sheets of the Excel file 2 and write back the matched mail id information with Excel file 1 data in result file.

Excel file 2 has 3 sheets with 5+ columns out of which email id is the one of the column that has to be compared with the Excel file 1.

Result file should have the matched email ids information as per excel file 1 information (i.e. 30+ column) from excel file 1 and excel file 2. Added to that the column 1 from excel file 2’s sheet 1 and sheet 2 has to be added. i highlighted the it as column 12 in yellow.Sample Files.zip (28.4 KB)

attaching the sample files for your perusal.

Your help is highly appreciated.

Regards,
Selvarani

15

Hi @Manjuts90 ,

I tried it using datattable, for each and if condiiton… it is reading the files and not executing the if statement. My ask is very simple i want to compare the email ids and write the matched email records in the result file.

i used this format “variable1 equals variable2” no compilation error. but if condition is not working

Please let me know if you have any idea.

16

@selvarani1390 use below format

17

yeah i used the same @Manjuts90 . Please find below screen shot

image

this message box is not returning any value, if condition is not working that is the reason.

I am getting proper input for both the variables.

Above is my problem statement , kindly help me with the work flow.

18

@selvarani1390 Don’t give space after equals

SubmittedbyEmail.Equals(“Email Address”)

If EmailAddress is a variable use without quotes.

SubmittedbyEmail.Equals(EmailAddress)

19

even if i use this it is not working

Kindly help

20

@selvarani1390 Print SubmittedbyEmail and EmailAddress variables using message box and writeline and check whether they are equal are not. Then u can identify where actually went wrong