How to search a CSV file for a certain value?

Ui.Vision HowTo
,
2

To search a CSV file, the best option is to use csvReadArray to import the whole CSV file into a 2-dimensional array. Then you can use the Javascript “findIndex” command to search the array.

The findIndex() method returns the index of the first element in an array that pass a test (provided as a function).

The findIndex() method executes the function once for each element present in the array:

  • If it finds an array element where the function returns a true value, findIndex() returns the index of that array element (and does not check the remaining values)
  • Otherwise it returns -1

Demo: As starting point for the demo I use the DemoCsvReadArray macro that ships with the RPA software. It first creates a test csv with csvSaveArray. Then we read it again and search it.

See the screenshot below. The search CSV part of the code starts at line 13. All the magic is in the Javascript in line 14:

image
image623×675 93.5 KB

Macro:

{
  "Name": "DemoCsvReadArray-1",
  "CreationDate": "2020-10-16",
  "Commands": [
    {
      "Command": "store",
      "Target": "fast",
      "Value": "!replayspeed"
    },
    {
      "Command": "comment",
      "Target": "Create an array and save the content to a CSV file",
      "Value": ""
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "var arr = []; for(var x = 0; x < 5; x++){arr[x] = []; for(var y = 0; y < 3; y++){arr[x][y] = (x+1)*(y+1);}}; return arr",
      "Value": "array1"
    },
    {
      "Command": "comment",
      "Target": "Manually set two array values ",
      "Value": ""
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "var newArr = ${array1}; newArr[0][2] = 'Hello World'; return newArr",
      "Value": "array1"
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "var newArr = ${array1}; newArr[2][1] = 'This is how you set an array value'; return newArr",
      "Value": "array1"
    },
    {
      "Command": "csvSaveArray",
      "Target": "array1",
      "Value": "data_from_array.csv"
    },
    {
      "Command": "csvReadArray",
      "Target": "data_from_array.csv",
      "Value": "myCSV"
    },
    {
      "Command": "echo",
      "Target": "Number of rows = ${!CsvReadMaxRow}",
      "Value": "green"
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "return ${mycsv[0]}.length;",
      "Value": "col"
    },
    {
      "Command": "echo",
      "Target": "Number of columns = ${col}",
      "Value": "pink"
    },
    {
      "Command": "comment",
      "Target": "Search CSV: For example 5 is in the array, and 58 is not",
      "Value": ""
    },
    {
      "Command": "store",
      "Target": "58",
      "Value": "tosearch"
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "var search = ${tosearch};\nreturn ${mycsv}.findIndex(arr => arr.includes(search));\n",
      "Value": "rowfound"
    },
    {
      "Command": "echo",
      "Target": "rowfound=${rowfound}",
      "Value": "green"
    },
    {
      "Command": "if_v2",
      "Target": "${rowfound} >= 0",
      "Value": ""
    },
    {
      "Command": "echo",
      "Target": "Yes, ${tosearch} is in the CSV file (array)",
      "Value": "green"
    },
    {
      "Command": "else",
      "Target": "",
      "Value": ""
    },
    {
      "Command": "echo",
      "Target": "No, ${tosearch} is NOT the CSV file (array)",
      "Value": "blue"
    },
    {
      "Command": "end",
      "Target": "",
      "Value": ""
    }
  ]
}
1 Like