How to search a CSV file for a certain value?

UI.Vision RPA HowTo
,
1

From a user: I’m trying to find a way for the macros to search for a specific value from CSV file and report back if found or not found.

So I would like to be able search from all columns and lines for a certain word or number.

I’m using a command to get a specific value which is appointed for example as ${value} and every time will be different.

So, I want the macros to be able to search from a CSV file if ${value} can be found.

If {value} is present in the CSV file, then it will try again, if {value} isn’t present, then proceed with the next step of macros.

Specifically, I’m using proxies which are getting a random IP address from an IP pool. There’s no guarantee about getting unique IPs or the same over and over again.

Every time the task is done, I ask the macros to save the IP address in a CSV. That CSV will contain all IP addresses used for future reference.

In the beginning of the macros, I would like to verify if the IP address has been used or not before proceeding.

So, that’s where I need to ask the macros to search CSV if the IP address is present. I hope this helps you to understand what I need.

1 Like
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