Need to loop through web table data and store every value in CSV

UI.Vision RPA
1

Hi,

I need to extract webtable data and store into excel or CSV. Currently i am using store text command which stores just single element.
I need to traverse through all TD/TR entries in webtable and save the webtable data in excel or csv.
Can you please help with the solution.
Appreciate your response.

2

Post site url and your macro code to understand

3

Did you manage to do this?

4

hi! you found a way to do it? I’m having the same problem here.

5

To do this su need to generate some counter to add in store command xpath

6

here is example which scrape coronavirus case date from COVID Live - Coronavirus Statistics - Worldometer

{
“Name”: “DemoCsvSaveTable”,
“CreationDate”: “2020-12-10”,
“Commands”: [
{
“Command”: “store”,
“Target”: “fast”,
“Value”: “!replayspeed”
},
{
“Command”: “open”,
“Target”: “COVID Live - Coronavirus Statistics - Worldometer”,
“Value”: “”
},
{
“Command”: “executeScript”,
“Target”: “function getElementByXpath(path) {\n return document.evaluate(path, document, null, XPathResult.FIRST_ORDERED_NODE_TYPE, null).singleNodeValue; \n}\nvar row =getElementByXpath("//[@id=‘main_table_countries_today’]/tbody[1]").rows.length;\n\nreturn row;",
“Value”: “x”
},
{
“Command”: “executeScript_Sandbox”,
“Target”: “var d = new Date(); m = d.getFullYear()+"-"+(d.getMonth()+1)+"-"+ d.getDate()+" "+ d.getHours()+":" + d.getMinutes() + ":" + d.getSeconds(); return m”,
“Value”: “timestamp”
},
{
“Command”: “echo”,
“Target”: “table length: ${x}”,
“Value”: “”
},
{
“Command”: “comment”,
“Target”: “store // ${timestamp}”,
“Value”: “!csvLine”
},
{
“Command”: “echo”,
“Target”: “First column in the CSV is time (${timestamp})”,
“Value”: “”
},
{
“Command”: “echo”,
“Target”: “Set i = 1 as we start the extraction with the 2nd table cell.”,
“Value”: “”
},
{
“Command”: “store”,
“Target”: “0”,
“Value”: “i”
},
{
“Command”: “while_v2”,
“Target”: “(${i} < 14)”,
“Value”: “”
},
{
“Command”: “executeScript”,
“Target”: “return Number (${i}) + 1”,
“Value”: “i”
},
{
“Command”: “echo”,
“Target”: “Current value of i = ${i}”,
“Value”: “i”
},
{
“Command”: “comment”,
“Target”: "storeText // //[@id="gcw_mainFNGP5XSu6"]/div[2]/table/tbody/tr[2]/td[${i}]/a”,
“Value”: “c2”
},
{
“Command”: “comment”,
“Target”: “storeText // //[@id="main_table_countries_today"]/tbody[1]/tr[1]/td[${i}]",
“Value”: “c2”
},
{
“Command”: “storeText”,
“Target”: "//[@id="main_table_countries_today"]/thead/tr/th[${i}]”,
“Value”: “c2”
},
{
“Command”: “executeScript”,
“Target”: “result=${c2}.replaceAll("\n", " ");\nresult=result.replaceAll("\r", " ");\nreturn result;”,
“Value”: “c3”
},
{
“Command”: “store”,
“Target”: “${c3}”,
“Value”: “!csvLine”
},
{
“Command”: “echo”,
“Target”: “Extracted Value for i=${i} is exchange rate = ${c3}”,
“Value”: “”
},
{
“Command”: “end”,
“Target”: “”,
“Value”: “”
},
{
“Command”: “echo”,
“Target”: “${!csvLine}”,
“Value”: “”
},
{
“Command”: “comment”,
“Target”: “Append content of !csvLine to CSV file (or create file if none exists)”,
“Value”: “”
},
{
“Command”: “csvSave”,
“Target”: “CurrencyConverterData”,
“Value”: “”
},
{
“Command”: “store”,
“Target”: “4”,
“Value”: “j”
},
{
“Command”: “while_v2”,
“Target”: “(${j} < 7)”,
“Value”: “”
},
{
“Command”: “executeScript”,
“Target”: “return Number (${j}) + 1”,
“Value”: “j”
},
{
“Command”: “echo”,
“Target”: “Current row number value of j = ${j}”,
“Value”: “j”
},
{
“Command”: “store”,
“Target”: “0”,
“Value”: “i”
},
{
“Command”: “while_v2”,
“Target”: “(${i} < 14)”,
“Value”: “”
},
{
“Command”: “executeScript”,
“Target”: “return Number (${i}) + 1”,
“Value”: “i”
},
{
“Command”: “echo”,
“Target”: “Current value of i = ${i}”,
“Value”: “i”
},
{
“Command”: “comment”,
“Target”: “storeText // //[@id="gcw_mainFNGP5XSu6"]/div[2]/table/tbody/tr[${j}]/td[${i}]",
“Value”: “c2”
},
{
“Command”: “storeText”,
“Target”: "//[@id="main_table_countries_today"]/tbody[1]/tr[${j}]/td[${i}]”,
“Value”: “c2”
},
{
“Command”: “comment”,
“Target”: “//*[@id="main_table_countries_today"]/thead/tr/th[${i}]”,
“Value”: “c2”
},
{
“Command”: “executeScript”,
“Target”: “result=${c2}.replaceAll("\n", " ");\nresult=result.replaceAll("\r", " ");\nreturn result;”,
“Value”: “c3”
},
{
“Command”: “echo”,
“Target”: “${c3}, ${i}: ${c3}=="" && ${i}==1”,
“Value”: “”
},
{
“Command”: “if_v2”,
“Target”: “${c3}=="" && ${i}==1”,
“Value”: “”
},
{
“Command”: “executeScript”,
“Target”: “return Number (${i}) + 14”,
“Value”: “i”
},
{
“Command”: “else”,
“Target”: “”,
“Value”: “”
},
{
“Command”: “comment”,
“Target”: "end // ",
“Value”: “”
},
{
“Command”: “store”,
“Target”: “${c3}”,
“Value”: “!csvLine”
},
{
“Command”: “echo”,
“Target”: “Extracted Value for i=${i} is exchange rate = ${c3}”,
“Value”: “”
},
{
“Command”: “end”,
“Target”: “”,
“Value”: “”
},
{
“Command”: “end”,
“Target”: “”,
“Value”: “”
},
{
“Command”: “comment”,
“Target”: “echo // ${!csvLine}”,
“Value”: “”
},
{
“Command”: “comment”,
“Target”: "end // ",
“Value”: “”
},
{
“Command”: “comment”,
“Target”: “Append content of !csvLine to CSV file (or create file if none exists)”,
“Value”: “”
},
{
“Command”: “if_v2”,
“Target”: “${i}>14”,
“Value”: “”
},
{
“Command”: “comment”,
“Target”: “if_v2 // ${i}>14”,
“Value”: “”
},
{
“Command”: “else”,
“Target”: “”,
“Value”: “”
},
{
“Command”: “csvSave”,
“Target”: “CurrencyConverterData”,
“Value”: “”
},
{
“Command”: “end”,
“Target”: “”,
“Value”: “”
},
{
“Command”: “end”,
“Target”: “”,
“Value”: “”
},
{
“Command”: “comment”,
“Target”: “If needed, you can download (save) the CSV data from the CSV tab to the the download folder”,
“Value”: “”
},
{
“Command”: “localStorageExport”,
“Target”: “currencyconverterdata.csv”,
“Value”: “”
}
]
}
note:

  1. this example is by modifying “DemoCsvSave”.
  2. Command 3 is to get row number of table, which can be used to control to loop table rows.
  3. how to get XPath of table and cell of table is by requesting if needed.
7

The macro code you wrote is too complex and confusing, if you explain well what you need to do you can make a new macro code much simpler that works better.

A short macro code is faster, and is easier to edit.

8

how long code writing is depending on the table you scape, from command number 9-22 scape header of table, from 22-52 scape table body(data), because table structure is complicated, command 36 is checking first cell of row if blank then skip to next row.

9

I scrape with best efficient xpath axes

With this I can detect more easy elements and relative elements (near other)

With !times command you can reuse part of code to have a short macro code, more fast and easy to edit.

With storexpathcount you can counts the element in the page (to set dynamic loop)

With executeScript_Sandbox you can create an array to use dynamic data inside loop

In this mode you with few line can have a long work that adapt data using array and loop counter.