For Each Row Excel

Help
, , ,
1

Hi

I’m quite new to UiPath and would really appreciate any help with the following issue.

  1. I have an Excel spreadsheet which has two columns (with headers).
    The 1st column is called ApplicationNumber’ and the column is populated with 11 rows of numbers:
    16820476
    18170887
    13826160
    13809575
    12838877
    13829425
    13852082
    13805189
    15733981
    16810144
    15726444
    The 2nd column is called ‘ApplicationDate’ and the rows are empty.

  2. What I want to do is to
    (a) Read the number in cell A2
    (b) Paste this copied number to a particular field called ‘Search term’ on a particular website (Smart Search - European Patent Register)
    (c) Then click the ‘Search’ button on this web page after the number has been pasted
    (d) The web page that I am then taken to has one piece of information (a date) that I want to scrape/copy+paste to cell B2 of my spreadsheet.
    (e) I then want to do the same for cell A3/B3, A4/B4, A5/B5 etc

  3. I have so far done the following:
    (a) Create ‘Excel Application Scope’ and ‘Read Range’ with datatable called ‘EPdata’.
    (b) Create a ForEach activity and assigned a variable called ApplicationNumber which iterates through
    the numbers in column 1 of my spreadsheet and performs 1(a),1(b) & 1(c) above.

  4. I am stuck on what to do next.
    (a) Do I screen/data scrape the date from this web page to cell B2 or copy and paste it and how do I do this for each row in column 2 of my spreadsheet?

Thanks in advance for any help.

2

You can use the Get Text activity for the date from the webpage and pass this variable into your table using an assign activity:

row(1) = Get Text variable

Once your robot has run through each row, use a write range activity to write all the date variables back to the original spreadsheet

Essentially in your for each loop you will:

Search for the the value row(0).tostring in the website
Get text on the date result
Assign this date to row(1)

3

Hi @tarquin,

1.Use Excel application scope and pass the excel path after that use read range and add a sheet name and remove “A1” and keep empty and set a variable as (varDT).
2.Next use For Each row activity and pass the varDT and inside the body use Type into activity and write row(“ApplicationNumber”).Tostring
3.And use click activity to click the search button.
4.And use screen scraping and scrape the date which you want and set output variable.
5.Then drag and drop Assign activity and In the To side write row.item(“ApplicationDate”) = and in the value side pass the variable which you scraped (i.e. 4th point out variable)
6.At last use write range and pass the datatable varDT and remove "A1"and keep as empty “”
and check the add headers in the property panel

cool,
Regards,
Gulshiyaa

If you find this as solution please mark as solution
Thank you

4

Hi

Thank you, This has worked and populated column “ApplicationDate”.

However, there is now a new problem. The cells in column “ApplicationDate” are not scraping the same field always from their respective web pages.

When I look at the GetFullText selector, the scrape attribute tableRow = 12. I believe that for some of the web pages it has opened, the same attribute should be tableRow = 10 or tableRow = 11. This is because some of the web pages contain more information than others and therefore the “ApplicationDate” data is not always in exactly the same position on the web page.

Is there a solution to keep the scrape in the same field on the web pages?

Thanks

Tarquin