Loop to get values from different excel files

Hi Friends,

I need your help in creating a logic for the below scenario.

I tried to create one logic but am confused.

I have 2 excel files.

The first excel file have some app codes like below.

The Below file is generated on daily basis and data in the column E can be in different.
image

The 2nd Excel file have the email ids corresponding to the app codes in the first excel file.

image

So I want to create a logic where BOT will pick app code from the 1st excel file and will automatically pick the corresponding email address from the 2nd excel file.

The data in 2nd excel file will be Fixed ( no changes will be made in the 2nd excel file )

Example if the app code selected by BOT is PSG_DBSAPP_EDGE then it should send an email to – ddd@gmail.com

Please Help.

@karan_singh2a you can do it by using 2 For Each loops: for each application code takes from file 1 check each row in file 2. If the 1st element in the row is the same as the application code, use the 2nd element as the email.

Here is a sample script.
two-loops.zip (886 Bytes)

Thanks alot alot …let me try and update you the outcome.

you are the best

Alesia,

So we need to Use Open 2 Excel files fucntion ( one with app code and the other with email ids) and then use then get column and save the column in List variable under appcode variable . correct.

Or i think there is no need to open the 2nd file as we are already declaring the emails in the table variable.

I assume in a real life use case you would have the emails in an Excel file (I just put them in a table to show an example), so you will need to open the file and read the contents in a table.

Noted , i got your point , thanks for the clarification

Aleisa , thank you for your help.

My last query is like - Now finally we have taken Email ids corresponding to the app codes .I am unable to use my brain to create one more logic to get the requestor information corresponding to the email id and the app code.
Eg - for FrontArena app code the requestor should be Linfu Lee and so on for other app codes as well.

Please be informed this is data is dynamic the requestors might be diff i
nthe next generated excel file.

But the App codes will be constant.
Sorry for being dumb.

Please refer below.

following code we are using.

The logic is the same as in the example above, you just need to assign a certain element in the row to a String variable (Constant value).
https://kb.workfusion.com/display/RPAe/Recorder+Variables#RecorderVariables-GettingValuefromListsandTables

Noted.

Am i doing it correct

I am using for each loop in the 2nd loop to get the details of the requestor corresponding to the app code and email.

I am unsure on the thing that what constant value do i need to declare for the string variable i.e requestor_name.

attaching my RPAE File.Dyntarce word file.docx (13.7 KB)

I am reading app codes and emails from the attached excel files.Dynatrace extracted data.xlsx (9.2 KB) Dynatrace_onboarded_apps.xlsx (9.5 KB)

Actually m really getting confused on which element should i use here.

or it should be like this ->o

What should i enter as the " Set value "

It has to be like on the very last screenshot. You need to use element with a required index in the action. If the requester is the 3rd element in the row, then you need index 3 - ${element[3]}

Hi Alesia,

Apoligies for taking your time on this small thing.

As discussed, I am using ${element[3]} for the constant value for the requestor_name (string)

But am getting below error message.

image

IF am using this element for the constant value ${requestor[3]} it is taking same value for all the app codes.
image

IF am using this element for the constant value ${requestor} it is taking same value for all the app codes.
image

Since i have shared the BOT and excel files with you , just a request to you to kindly review the BOT and make changes as i am not able to do so and its really giving me alot of stress.

Please help.

attaching my RPAE File.Dyntarce word file.docx (13.7 KB)

I am reading app codes and emails from the attached excel files.Dynatrace extracted data.xlsx(9.2 KB) Dynatrace_onboarded_apps.xlsx (9.5 KB)

Judging by the error message, you have only 2 elements in the row, so it cannot take the 3rd element from it.

You need to copy all the data you will need to use from excel to the table variable. The element index will be the number of the column in the resulting table variable where the data you need is stored.

My Actual requirements are like this -> The BOT should check IF Schedule Start (DD/MM/YYYY) >= Current date + 2 ( Logic is prepared )

Then it should match the app code in the excel file 1 with the corresponding email ids to it in the excel file 2. ( Logic is prepared )

Then BOT should need to define the CORRESPONDING values of the below from the excel file 1 to send email later.

Example : if the appcode ( ) is image PSG_DBSAPP_FRONTARENA
then it should take requetsor as image Shawn Han Wei TAY

Dynatrace_onboarded_apps.xlsx (9.5 KB)

Need your kind help on this.BOT CODE.docx (14.7 KB)


Dynatrace extracted data.xlsx (11.3 KB)

I have attached the excel files and BOT code for your reference.

Please see if you can review the BOT and make the changes, it will going to be a great help for me.

Extremely sorry to bother you and dragging you in this.

Hi ,

I was able to do so. thanks for your help and support.

The BOT is completed and running fine.