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