Loop through columns using csvRead

I would like to loop through columns using csvRead, in addition to looping through rows, so I can copy one file data to another. It seems the variable names for columns are {COL1}, {COL2}, and so on, but I would like to set a column counter and have the column number automatically get set in the variable name, such as {COL{columnCounter}} (however this code doesn’t work). So I can loop through all columns that have data in them without needing a separate step in the code for each column. Is this possible?

The solution is probably this

If you not solve post macro code here please.

I’m trying to loop through columns, not rows. To loop through rows, yes I need to increment the !csvReadLineNumber variable

There is not any option to loop columns i do not know it.

Ok. here is the code example:

{
“Name”: “demo cols”,
“CreationDate”: “2020-2-9”,
“Commands”: [
{
“Command”: “csvRead”,
“Target”: “input.csv”,
“Value”: “”
},
{
“Command”: “while_v2”,
“Target”: “{!csvReadStatus} == \"OK\"", "Value": "" }, { "Command": "store", "Target": "{!COL1}”,
“Value”: “!csvLine”
},
{
“Command”: “store”,
“Target”: “{!COL2}", "Value": "!csvLine" }, { "Command": "store", "Target": "{!COL3}”,
“Value”: “!csvLine”
},
{
“Command”: “store”,
“Target”: “{!COL4}", "Value": "!csvLine" }, { "Command": "csvSave", "Target": "output.csv", "Value": "" }, { "Command": "executeScript", "Target": "return Number({!csvReadLineNumber})+1”,
“Value”: “!csvReadLineNumber”
},
{
“Command”: “csvRead”,
“Target”: “input.csv”,
“Value”: “”
},
{
“Command”: “end”,
“Target”: “”,
“Value”: “”
}
]
}

I think it would be nice if you have a lot of columns to be able to say {COLx}, {COLx+1}, etc if that makes sense

You can obtain a similar function to merge all columns of your csv after you split in part and cut interested part

I use this method, i create one columns with all data with a separator after I use 1 column but I cut the part i need, it’s simply and fast

In my example

separator is - in macro code is escaped (\"-\")
first part is [0]
second part is [1]

You can have unlimited part and split always with [n]

N is the number starting from 0

Macro code, you can use !COL1 like variable or other !COLX

{
  "Name": "Split_Function",
  "CreationDate": "2020-2-9",
  "Commands": [
    {
      "Command": "store",
      "Target": "11111-22222",
      "Value": "Col"
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "return ${Col}.split(\"-\")[0].trim();",
      "Value": "First_Part"
    },
    {
      "Command": "echo",
      "Target": "${First_Part}",
      "Value": ""
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "return ${Col}.split(\"-\")[1].trim();",
      "Value": "Second_Part"
    },
    {
      "Command": "echo",
      "Target": "${Second_Part}",
      "Value": ""
    }
  ]
}
1 Like

That is a great idea. @newuserkantu Thanks again for your help! I will try that way :slight_smile: It would still be nice if in the future the UI.Vision RPA could be updated to loop through columns by default just like it loops through rows :slight_smile:

1 Like