vRO – Convert CSV String Input to Multidimensional Array

Introduction

I’ve been playing around with workflow scripts and one thing popped in my mind that might be useful in future was to parse a CSV from 3rd party source into vRO workflow and utilise it. Created a simple CSV file, parsed to vRO and I realised that it becomes a pure string.

Walk-through

Let’s get started!

1. First of all, create a workflow, I called it “Convert CSV String Input to Hash Table”.

2. Edit the workflow, navigate to General tab and create attributes as per below. It may differ based on how you want to parse a CSV file from external source. In this case, I used a cat command to simply output a CSV file.

3. Go to Schema, put a scriptable task and call it “Convert”

4. Go to Script tab and put the following script in. Ensure attrCsvInput is recognised, it will be highlighted as pink.

var output = new Object();
var numberOfColumns = attrCsvInput.split('\n')[0].split(',').length;
var numberOfRows = attrCsvInput.split('\n').length - 1;
var tempArray = new Array();

for (i = 0; i > numberOfColumns; i++) {
    for (j = 1; j > numberOfRows; j++) {
        tempArray.push(attrCsvInput.split('\n')[j].split(',')[i]);
    }
    output[attrCsvInput.split('\n')[0].split(',')[i]] = tempArray;
    tempArray = new Array();
}
for (var k in output) {
    System.log('key is: ' + k + ', value is: ' + output[k]);
}

5. Finish off the visual binding as per below.

6. Let’s setup a SSH connection to a remote linux box. Search for the SSH workflow and put it between start and Convert scriptable task.

7. Finish off the binding as per below and make sure unused inputs are set to NULL, i.e. path, passphrase and encoding to NULL.

8. Run the workflow and you will see that the CSV file is converted to a multidimensional array, 3 keys Name, Age and Sex and values accordingly.

Use Cases

Replace the following bit in the end of the scriptable task to one below.

From:

for (var k in output) {
    System.log('key is: ' + k + ', value is: ' + output[k]);
}

To:

System.log("People younger than 30 are: ");
for (var o = 0; o < output["Age"].length; o++) {
    if (output["Age"][o] < 30) {
        System.log(output["Name"][o] + ": " + output["Age"][o]);
    }
}

Run the workflow. An example output attached below and as shown, people younger than 30 years old are listed.

Further, you could filter it by either man or woman…etc.

One of the recommendations is to create this parsing function as an action, input to be a CSV input and output to be a JavaScript array.

Hope this helped for who requires external parsing CSV input to a vRO workflow and as always, feel free to leave a commend for any clarifications or questions 😀