I am extracting multiple data tables from the web. It is reading data from an excel file, opening a web page, extracting data. After each time the data is collected I want to add a column and put a string(fixed value stored in variable) for each row of that data table.
How can I do so? The data tables have dynamic values and sizes and I want to also append all the data tables in 1 excel sheet one after the other.
Example:
For 1st row I get the following data
Price Name X-Value
23 Chips 234
46 Coke 123
For 2nd row I get the following data
Price Name X-value
12 Boo 654
456 Blip 123
123 Kick 123
… for around 600 more rows
The result I want:
Price Name X-value Question
23 Chips 234 ok?
46 Coke 123 ok?
12 Boo 654 All good?
456 Blip 123 All good?
123 Kick 123 All good?
and then put entire big data table in 1 excel file using write range.
Please note the question column has the same value for the data table but different for each data table extracted after entering the row input as demonstrated above.
there are two possible ways that I see,
Lets say you are getting each items from the excel as a URL, and scrapping data for each URL and adding a extra column with come constant value
First create a datatable using build datatable of required column names , then use a for loop to loop through the excel(URLs) datatable , for each url you scrape the data, once scrapping is done and you have the complete table for that particular URL , then use a add data column activity to add a column names"Question", and a for each row in the scrapped table to populate the “Question” column with required constants for that particular url , finally use a merge datatable activity to add the final table to the new datatable that we built using the build datatable at starting(in properties panel use the “add columns” in the missing schema part - this will add extra columns if some columns are missing anywhere), finally once all URLs are checked use a write range
2.For each URL scrape the data , once data is scrapped , use add data column activity , and in properties panel set “default value” with the constant the you want for that particular table , and then append range this datatable to excel , remember to redefine the datatable =Nothing once processing for a single url is done, otherwise next time the “questions” column value wont get changed and you may get wrong outputs
Hope this helps
Let me know If you have any questions
The approach works fine. You just have to do it correctly. You don’t need a For Each. That’s the point. If you create a new column and assign it a default value, all rows will automatically get that value. No loop needed. Your error is because you are looping and therefore trying to add the column more than once.
you can use assign =Nothing or the other ways suggested by others above , both will work,
the 2nd method that I have suggested is the optimisation for the overall process that you said, if you need more optimisation then we will have to go through the complete process, in order to suggest something.
You can try to add log messages at each major sequence and try to analyse which part of the flow is taking how much time , and try to track down the part which is most slow and check that part for some optimisations
Better first build your completely running flow end to end and once its stable , then look for optimisations , and mostly UI automation parts don’t have much code based optimisations ,
Hope you get my points
Anil_G
shaik.muktharvalli1
ashokkarale
Akash_Javalekar1
sudster
mohamed.shaker
Affan_Basar
lrtetala
johnom1
erkanceylan