How to select a whole column of data in csv file at once and save as a variable?

msrhcf · June 4, 2020, 1:52pm

Hi,

I’m newbie to UI.Vision/Selenium RPA tools and need some help. I have imported my CSV data to the UI Vision RPA window. How do you select the whole of column one?

I would like to copy the whole of column one into a search bar at once, but do not know how to store it as a variable.

So far I have used the following to store the file’s data in a variable called myCSV:
csvReadArray | filename.csv | myCSV

I know it works because I am able to echo the first line of the column in blue using:
echo | E1,0=${myCSV[1][0]} | blue

Thank you for all your help!

Ps. After doing this I would like to do the same for every column in the file, not just column one and would appreciate any tips.

newuserkantu · June 4, 2020, 3:58pm

This work require loop or times command to read all columns lines and after you merge all value

msrhcf · June 4, 2020, 10:23pm

Thanks for your fast reply. Please coule you give an exame of how this could look?

newuserkantu · June 4, 2020, 11:20pm

This is an hardest macro to create but i create it for you with my method.

CSV test.csv
1 Line
2 Line
3 Line
4 Line
5 Line
6 Line
7 Line
8 Line
9 Line
10 Line

Log macro successful

[status]

Playing macro Store_All_Row_Of_Column_In_Variable

[info]

Executing: | store | true | !errorignore |

[info]

Executing: | comment | Store An Empty Var Value | |

[info]

Executing: | store | | Var_All_Lines |

[info]

Executing: | comment | Times All Csv Lines | |

[info]

Executing: | csvRead | test.csv | |

[info]

Executing: | times | ${!csvReadMaxRow} | |

[info]

Executing: | csvRead | test.csv | |

[info]

Executing: | executeScript_Sandbox | return ({Var_All_Lines})+{!COL1} | Var_All_Lines |

[info]

Executing: | executeScript_Sandbox | return Number(${!csvReadLineNumber})+1 | !csvReadLineNumber |

[info]

Executing: | end | | |

[info]

Executing: | times | ${!csvReadMaxRow} | |

[info]

Executing: | csvRead | test.csv | |

[info]

Executing: | executeScript_Sandbox | return ({Var_All_Lines})+{!COL1} | Var_All_Lines |

[info]

Executing: | executeScript_Sandbox | return Number(${!csvReadLineNumber})+1 | !csvReadLineNumber |

[info]

Executing: | end | | |

[info]

Executing: | times | ${!csvReadMaxRow} | |

[info]

Executing: | csvRead | test.csv | |

[info]

Executing: | executeScript_Sandbox | return ({Var_All_Lines})+{!COL1} | Var_All_Lines |

[info]

Executing: | executeScript_Sandbox | return Number(${!csvReadLineNumber})+1 | !csvReadLineNumber |

[info]

Executing: | end | | |

[info]

Executing: | times | ${!csvReadMaxRow} | |

[info]

Executing: | csvRead | test.csv | |

[info]

Executing: | executeScript_Sandbox | return ({Var_All_Lines})+{!COL1} | Var_All_Lines |

[info]

Executing: | executeScript_Sandbox | return Number(${!csvReadLineNumber})+1 | !csvReadLineNumber |

[info]

Executing: | end | | |

[info]

Executing: | times | ${!csvReadMaxRow} | |

[info]

Executing: | csvRead | test.csv | |

[info]

Executing: | executeScript_Sandbox | return ({Var_All_Lines})+{!COL1} | Var_All_Lines |

[info]

Executing: | executeScript_Sandbox | return Number(${!csvReadLineNumber})+1 | !csvReadLineNumber |

[info]

Executing: | end | | |

[info]

Executing: | times | ${!csvReadMaxRow} | |

[info]

Executing: | csvRead | test.csv | |

[info]

Executing: | executeScript_Sandbox | return ({Var_All_Lines})+{!COL1} | Var_All_Lines |

[info]

Executing: | executeScript_Sandbox | return Number(${!csvReadLineNumber})+1 | !csvReadLineNumber |

[info]

Executing: | end | | |

[info]

Executing: | times | ${!csvReadMaxRow} | |

[info]

Executing: | csvRead | test.csv | |

[info]

Executing: | executeScript_Sandbox | return ({Var_All_Lines})+{!COL1} | Var_All_Lines |

[info]

Executing: | executeScript_Sandbox | return Number(${!csvReadLineNumber})+1 | !csvReadLineNumber |

[info]

Executing: | end | | |

[info]

Executing: | times | ${!csvReadMaxRow} | |

[info]

Executing: | csvRead | test.csv | |

[info]

Executing: | executeScript_Sandbox | return ({Var_All_Lines})+{!COL1} | Var_All_Lines |

[info]

Executing: | executeScript_Sandbox | return Number(${!csvReadLineNumber})+1 | !csvReadLineNumber |

[info]

Executing: | end | | |

[info]

Executing: | times | ${!csvReadMaxRow} | |

[info]

Executing: | csvRead | test.csv | |

[info]

Executing: | executeScript_Sandbox | return ({Var_All_Lines})+{!COL1} | Var_All_Lines |

[info]

Executing: | executeScript_Sandbox | return Number(${!csvReadLineNumber})+1 | !csvReadLineNumber |

[info]

Executing: | end | | |

[info]

Executing: | times | ${!csvReadMaxRow} | |

[info]

Executing: | csvRead | test.csv | |

[info]

Executing: | executeScript_Sandbox | return ({Var_All_Lines})+{!COL1} | Var_All_Lines |

[info]

Executing: | executeScript_Sandbox | return Number(${!csvReadLineNumber})+1 | !csvReadLineNumber |

[info]

Executing: | end | | |

[info]

Executing: | times | ${!csvReadMaxRow} | |

[info]

Executing: | echo | ${Var_All_Lines} | #shownotification |

[echo]

1 Line 2 Line 3 Line 4 Line 5 Line 6 Line 7 Line 8 Line 9 Line 10 Line

[info]

Macro completed (Runtime 18.97s)

Macro Code

{
  "Name": "Store_All_Rows_Of_Column_In_Variable",
  "CreationDate": "2020-6-5",
  "Commands": [
    {
      "Command": "store",
      "Target": "true",
      "Value": "!errorignore"
    },
    {
      "Command": "comment",
      "Target": "Store An Empty Var Value",
      "Value": ""
    },
    {
      "Command": "store",
      "Target": "",
      "Value": "Var_All_Lines"
    },
    {
      "Command": "comment",
      "Target": "Times All Csv Lines",
      "Value": ""
    },
    {
      "Command": "csvRead",
      "Target": "test.csv",
      "Value": ""
    },
    {
      "Command": "times",
      "Target": "${!csvReadMaxRow}",
      "Value": ""
    },
    {
      "Command": "csvRead",
      "Target": "test.csv",
      "Value": ""
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "return (${Var_All_Lines})+${!COL1}",
      "Value": "Var_All_Lines"
    },
    {
      "Command": "executeScript_Sandbox",
      "Target": "return Number(${!csvReadLineNumber})+1",
      "Value": "!csvReadLineNumber"
    },
    {
      "Command": "end",
      "Target": "",
      "Value": ""
    },
    {
      "Command": "echo",
      "Target": "${Var_All_Lines}",
      "Value": "#shownotification"
    }
  ]
}

Image Macro Successful

msrhcf · June 5, 2020, 8:13am

Thank you for this! I will test this out today and update you soon.

newuserkantu · June 5, 2020, 2:46pm

Try it working like a charm i tested it before posting in the forum

msrhcf · June 5, 2020, 9:42pm

Genius! It works perfectly. Have an amazing weekend and thanks again!

Jeff_Mennuti · October 9, 2020, 3:45pm

How do you do this loop?

newuserkantu · October 9, 2020, 10:54pm

I use !imes command is more simple of loop and working in same mode

Andrew77 · April 26, 2021, 11:26am

how to add \n so the data
1 Line
2 Line
3 Line

not 1 Line 2 Line 3 Line

newuserkantu · April 26, 2021, 3:34pm

You need not to add /n in csv you can simply edit the data before pasting in form using store and after edit you can paste text in form.