How to read 2 excel files and produce a 3rd excel file with result

studio

#1

Hi everybody,

I got this issue.
I got the 1st excel file contains 2 columns:-
S/N | Value
1 | Value1
2 | Value2
3 | Value3

Another 2nd excel file contains 2 columns:-
S/N | Value
1 | Value1
2 | Value2

Comparing the 2 excel files, i hope to create a 3rd excel file with 3 columns:-
S/N | Value | Match
1 | Value1 | Yes
2 | Value2 | Yes
3 | Value3 | No

How can i do it


#2
  1. Use excel application scope and read 1st excel file and similarly the next excel file using read range activity.
  2. Use the output from each read range activity and compare according using if condition.
  3. Then open another excel scope and use write range to write the columns to new excel file.

#3

ReadingExcel.zip (3.0 KB)
This is what i have but seems like unable to get the result i wanted in the 3rd file…
Can advise where i gone wrong with it


#4

up…


#5

Hey @Eros,

I just checked your workflow.I guess there might be an error in “Add Data Row” activity.
Instead of row.ItemArray in the ‘Array row’ property, try using {row(0).ToString,“Yes”}
Similarly add a “Add Data Row” activity in the ‘else’ clause as well and try setting the ‘Array row’ property to {row(0).ToString,“No”}
Hopefully it should work! :slight_smile:


#6

Thanks for the tips…
Can advise how to make sure the records just saw once…
so like it will based on the 2nd excel (which has 5 records) and it compares with 1st excel (which has 15 records)… The final output of the 3rd excel just saw me the 5 records and the outcome whether it matches any of the 15 records


#7

Do you mean to day the 3rd excel file should have 15 entries each with a yes or no based on the match?


#8

The 3rd file shld have 5 records with yes or no.

Regards,
Edmund Phua


#9

How many are you getting right now?


#10

Hello @Eros,
Please go through and run the attached file :slight_smile:
I’ve got what you wanted!
Cheers :slight_smile:

ReadExcelFile.xaml (14.7 KB)


#11

I’m getting for the 15 records repeating 7x for each records…

Regards,
Edmund Phua


#12

Cool… thanks man… I will go try it!!!

Regards,
Edmund Phua


#13

Do mark it as a solution if it gives you what you needed :wink: