How to mark a row in a CSV line as processed?

UI.Vision RPA HowTo
, ,
1

When you loop over a CSV file it can be useful to mark processed rows, so that in case of an interruption of the automation, you know what was already done (e. g. submitted to a website) and what still needs to be processed.

2 Likes
3

Solution: If you want to change imported CSV data and save it back to the same CSV file, the best solution is to use csvReadArray and csvSaveArray.

To change a value in an array, you need to use Javascript and executeScript. You move the array from the Selenium IDE array variable (here called “arr”) into a Javascript array (here called “b”), change the cells that you want to change and then return the modified array into the Selenium IDE variable. This line does it:

executeScript_Sandbox | var b = ${arr}; b[${i}][2] = "done"; return b; | arr

Macro in action: In the macro we know the number of rows the CSV file has with the built-in ${!CsvReadMaxRow} variable. We then simply use the Selenium TIMES command to loop over every row of the array. (We could also use forEach to loop over the array, but in this use case Times with ${!times} is better, as we need the row index to change the value of an array cell, see above). To mark a row as done, we change the text value in the 3rd column of every processed entry to “done”.

The example CSV in the screencast below has 3 rows. The first row is already marked “done” so it is skipped during processing:

Macro source code:

{
  "Name": "DemoMarkCSVasDone",
  "CreationDate": "2021-3-8",
  "Commands": [
    {
      "Command": "comment",
      "Target": "Read CSV file to array",
      "Value": ""
    },
    {
      "Command": "csvReadArray",
      "Target": "readcsvtestdata.csv",
      "Value": "arr"
    },
    {
      "Command": "echo",
      "Target": "Number of rows = ${!CsvReadMaxRow}",
      "Value": "green"
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "return ${arr[0]}.length;",
      "Value": "col"
    },
    {
      "Command": "echo",
      "Target": "Number of columns = ${col}",
      "Value": "pink"
    },
    {
      "Command": "comment",
      "Target": "loop over all CSV values",
      "Value": ""
    },
    {
      "Command": "times",
      "Target": "${!CsvReadMaxRow}",
      "Value": ""
    },
    {
      "Command": "comment",
      "Target": "i -1 because arrays start with index=0",
      "Value": "arr"
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "return ${!times} - 1;",
      "Value": "i"
    },
    {
      "Command": "echo",
      "Target": "Current CSV Row: col1=${arr[${i}][0]}, col2=${arr[${i}][1]}, col3=${arr[${i}][2]}",
      "Value": "brown"
    },
    {
      "Command": "if_v2",
      "Target": "${arr[${i}][2]} == \"done\"",
      "Value": ""
    },
    {
      "Command": "echo",
      "Target": "Row ${!times} is already done, skipping it.",
      "Value": "green"
    },
    {
      "Command": "else",
      "Target": "",
      "Value": ""
    },
    {
      "Command": "echo",
      "Target": "Row ${!times} -- Form filling etc would be here",
      "Value": "blue"
    },
    {
      "Command": "comment",
      "Target": "Change last column to DONE <== HERE IS WHERE THE MAGIC HAPPENS",
      "Value": "arr"
    },
    {
      "Command": "executeScript_sandbox",
      "Target": "var b = ${arr}; b[${i}][2] = \"done\"; return b; ",
      "Value": "arr"
    },
    {
      "Command": "comment",
      "Target": "Save array after each loop, use same CSV name",
      "Value": "arr"
    },
    {
      "Command": "csvSaveArray",
      "Target": "arr",
      "Value": "readcsvtestdata.csv"
    },
    {
      "Command": "end",
      "Target": "",
      "Value": ""
    },
    {
      "Command": "end",
      "Target": "",
      "Value": ""
    }
  ]
}
6 Likes
4

Hi,

This solution doesn’t work now, i got this error:
[error] Command csvReadArray not supported yet

What can I do?

Thanks!
Gy

5

Which rpa version do you have? It sounds like you have an old one?