How to read a CSV file with a changing number of columns

In this use case we want to read data from a CSV file, but the number of emails per row changes. So if we would put each email in its own column, we don’t know how many columns to read.

Solution: Put all emails in one column, separated by a separator character (here we use |, e. g. aaa@bbb.com|qqqq@ccc.de|qq@sap.fr). Then in the macro we split this string into an array, and can loop over every email.

  • First WHILE loop: Read CSV (this part is copied from DemoReadCSVwithWhile)
  • Second WHILE loop: Loop over array content

Macro:

{
  "Name": "ReadEmailFromCSV",
  "CreationDate": "2019-10-24",
  "Commands": [
    {
      "Command": "store",
      "Target": "fast",
      "Value": "!replayspeed"
    },
    {
      "Command": "comment",
      "Target": "The file ReadCSVTestData.csv is pre-installed with Kantu.",
      "Value": ""
    },
    {
      "Command": "csvRead",
      "Target": "email.csv",
      "Value": ""
    },
    {
      "Command": "echo",
      "Target": "Status = ${!csvReadStatus}, line = ${!csvReadLineNumber}",
      "Value": ""
    },
    {
      "Command": "while_v2",
      "Target": "${!csvReadStatus} == \"OK\"",
      "Value": ""
    },
    {
      "Command": "echo",
      "Target": "status = ${!csvReadStatus}, line = ${!csvReadLineNumber}",
      "Value": ""
    },
    {
      "Command": "echo",
      "Target": "Batch= ${!col1}",
      "Value": "blue"
    },
    {
      "Command": "echo",
      "Target": "EmailList= ${!col2}",
      "Value": "pink"
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "return ${!col2}.split(\"|\")",
      "Value": "varArray"
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "return ${varArray}.length",
      "Value": "varLength"
    },
    {
      "Command": "echo",
      "Target": "Number of  emails in this line: ${varLength}",
      "Value": "pink"
    },
    {
      "Command": "store",
      "Target": "0",
      "Value": "i"
    },
    {
      "Command": "while_v2",
      "Target": "${i} < ${varLength}",
      "Value": "green"
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "return ${varArray}[${i}]",
      "Value": "email"
    },
    {
      "Command": "echo",
      "Target": "Email Nr. ${i} is ${email}",
      "Value": "blue"
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "return Number(${i})+1",
      "Value": "i"
    },
    {
      "Command": "end",
      "Target": "",
      "Value": ""
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "return Number(${!csvReadLineNumber})+1",
      "Value": "!csvReadLineNumber"
    },
    {
      "Command": "store",
      "Target": "true",
      "Value": "!errorIgnore"
    },
    {
      "Command": "echo",
      "Target": "Reading CSV line No.  ${!csvReadLineNumber} ",
      "Value": "!errorIgnore"
    },
    {
      "Command": "csvRead",
      "Target": "email.csv",
      "Value": ""
    },
    {
      "Command": "store",
      "Target": "false",
      "Value": "!errorIgnore"
    },
    {
      "Command": "end",
      "Target": "",
      "Value": ""
    }
  ]
} 

Example CSV:

Batch1, aaa@bbb.de|qqqq@ccc.de|qq@sap.fr
Batch2, 111@333.com
Batch3, e@cnn.cn|jim@ccc.es