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

excel
activities

#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


Read row by row and column by column in excel
Please clarify doubt in Building datatable
#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.


How to import excel data into web application
#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.


#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.


#7

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

thank you so much for your help!


#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.


#11

it helps a lot, thank you for your idea