How to loop through rows and columns in an excel and pass them to a webpage

Help
, ,
1

Hi,

I need to capture data from each row and each column and pass them to a webpage to login.

Excel:
usrname password
user1 pswd1
user2 pswd2
user3 pswd3

webpage
username-textbox
password-textbox

How do i do this?

I started of by

  1. Read Range activity to read data present on the excel.
  2. created a variable to read the column count in to.

I am looking for how would i fetch and pass them to webpage in a loop

1 Like
2

Follow the below steps:

  1. Use ‘Excel Application Scope’ activity and Add ‘Read Range’ activity inside the scope.
  2. Create a DataTable variable and assign it to the output property of Read Range activity. Not all your username and password date is in that DataTable variable. Lets say it is dtCredentials.
  3. Now use a ‘For Each Row’ activity after the ‘Excel Application Scope’ activity.
  4. Add ‘Assign’ activity inside the for each loop and create string variables named ‘strUserName’. Assign should be like this “strUserName = row.item(0).ToString”
  5. Add another ‘Assign’ activity next to the first assign activity. Create another string variable named ‘strPassword’. Assign should be like this “strPassword = row.item(1).ToString”.
  6. Add an ‘Open Browser’ activity with the login page url.
  7. Add a ‘Type Into’ activity inside the Open Browser activity and indicate your username textbox.
  8. Pass the value of the text as ‘strUserName’.
  9. Add another ‘Type Into’ activity after the 1st one and indicate your password textbox.
  10. Pass the value of the text as ‘stePassword’.
  11. Add a ‘Click’ activity and Indicate your submit button.

Note: Step 4 to Step 11 should be inside your for each row loop.

Thanks,
Rammohan B.

13 Likes
3

Thank you so much for your help.

If a third column has to be added to the excel (Ex:URL), should that also be assigned for ‘row.item(0).ToString’?

4

No, It should be row.item(2). Number represents the index of the column.

Thanks,
Rammohan B.

2 Likes
5

Hello Rammohan,

if I have excel data that I want to paste later in the process, how will I do it?
example:
open the website
enter log in credentials
click ‘invoices’ from ribbon
select ‘account id’
select ‘equal’
then I open my excel copy row2 from column1 (account)
then ‘type into’ website
then I enter few more fields and click search

how to make this process in loop so that robot takes one row at a time rather than pasting all the rows at once.

Thanks,
SriRana

6

Something like this,

  1. Open You Application Using ‘Open Browser’
  2. Use ‘Type Into’ & ‘Click’ activities to Login to the application.
  3. Again use associated activities to Click Invoices, Select AccountId & Select Equal.
  4. Use ‘Excel Application Scope’ activity and Add ‘Read Range’ activity inside the scope.
  5. Create a DataTable variable and assign it to the output property of Read Range activity.
  6. Now use a ‘For Each Row’ activity after the ‘Excel Application Scope’ activity with the DataTable just created.
  7. Inside For Each Row activity add all your ‘Type Into’ activities that you need to enter in the website. row.item(0).ToString should give you that value of the first column for that particular row. row.Item(1).ToString fro the 2nd Column and so on.
  8. Inside For Each Row activity add another click activity to search.
  9. Your For Each Loop should iterate for each of the row within the excel file and perform your search. With every search if there is any need to go back to any previous page, make sure that you are adding that operation as the last activity within the for each row loop.

Let me know if it helps,

Thanks,
Rammohan B.

8 Likes
7

Thank you Ram, it worked! this is what i been missing all these days…

thank you so much for your help!

1 Like
8

Hello Rammohan,

once i clicked search i get a PDF (Open, Save) i just open it instead of save, so i cant use Read PDF Text activity as its not downloaded into my system nor do i want it downloaded.

i tried using Screen Scraping also but its not validating the live attachment as i get more invoices from other accounts.

Process:
once i click search i get a pop up with (OPEN, SAVE PDF) I CLICK open and PDF is opened from there i want to extract invoice amount and paste it back on to the same excel file in column B. Then again i want to run the whole process above of entering new account id and search for invoice and getting PDF. I used both write range and write cell but the results are null.

Please help…
Thanks
Srirana

9

I was able to get part of the problem solved

used ‘Excel application scope’ to open the excel file
‘Get OCR text’ to extract the wanted data from each PDF as its opened in the loop
‘Write Cell’ to paste the extracted data

last step is where its i am having trouble, its pasting the value in B2 column just replacing the previous amount that was copied over. how can i create it such that for A2 this is the Invoice amount B2, A3 its corresponding invoice amount B3…

10

Have a look at this,

ExcelWriteCell_Sample.zip (10.2 KB)

Thanks,
Rammohan B.

3 Likes
11

it helps a lot, thank you for your idea

12

Hi Rammohan,

Just i need to ask a question because my case is alittle bit different.

I have an excel which has 4800 rows and it goes to EE . and it is 4.6 mb file.

the question here is, i need to do automation by

  1. get each row’s data, and go to the website, type the corresponding row data into the corresponding textbox.

So i think i should do iteration among the .item(?)

or what else?

thank you

1 Like
13

@Rammohan91 Thanks a ton for this beautiful answer with canonical steps. I rate this answer as one of the top most answer in this forum. I was stuck for 2 days on this issue and your detailed steps solved it for me.

3 Likes
14

Hi Rammohan,

In the above scenario, how to capture the excel data(column values) if the column values are like 15-20. How can i iterate through the column values of a particular row instead of storing all the values inside variable one-by-one

1 Like
15

Hello @Rammohan91
I have a similar problem where I have to click on 1 row, one after another (60 clicks total, 20 per pages.
the column that I care about looks like this:

linkstoclick
linkstoclick86×525 2.77 KB

I have to do some operation in between the clicks but I am having trouble making the tablerow dynamic or use the for each row with the click text that always gets an error.I have the entire process working for the first row and would like to loop it for the other 19 on the first page, go to next page and do 20 more, go to next page, click the last 20.
please let me know if you have a way to do this.
Thank you.
Boyses

1 Like
16

Hi @boyses

Check each and every selector ensue the selector has unique property except aaname
based on that set a counter as 1 and in while condition click and manipulate the steps

Thanks
Ashwin S

1 Like
17

Hello @AshwinS2
all selectors are unique, only the table row is changing when I manually change the row. but it is only 1 row being completed. To be honest, I did not understand much from what you wrote, sorry.
How can I make the tableRow dynamic? so I can link two sequences and add an if statement for the count to be 20 and change page and start again?
Thank you.

1 Like
18

Hello Rammohan,

Thanks for sharing the steps in a simpler way.
I referred your answers and trying to solve my issue.
Can you let me know what is wrong in my below steps:

The problem is, it takes only the latest value from the excel sheet.
Example if the excel sheet has 4 usernames, it takes the last user(4) and perform the tests.

  1. Open the application through windows start menu
  2. Login with user name and password
  3. Navigate to a particular file option, perform some actions
  4. Now here I need to pass some user id from an excel file and capture the status of each user
  5. Open excel application scope
  6. Read range created a datatable variable(exceldata)
  7. For each loop and passed that datatable variable (exceldata)
    For each row in exceldata
  8. Assign strUsername = row.item(0).ToString
  9. Type Into and text as (strUsername)
    10.Press Enter
  10. Steps 7-10 are in for each loop
1 Like
19

Hello Rammohan,

I did a silly mistake , it worked fine as per your steps :slight_smile:

20

Hi Rammohan,
I tried the same steps to read the text from excel and type into the windows application.
But, even after i use row.Item(0).Tostring , all the values from the first column are entered continuously in the selected field of the web application. Can you please let me know where am i going wrong

image
image524×741 16.7 KB