How to append a column as comma separated variable? Currently get undefined error

Hi All,

Newbie here to ask a question:
How to quickly append an excel column into a comma separated variable.

File looks something like:
A12345678
B12345678
C12345678
D12345678
E12345678

Desired output:
A12345678, B12345678, C12345678, D12345678, E12345678
or as:
A12345678 B12345678 C12345678 D12345678 E12345678
The final output is to be pasted into a search bar as either:-

The aim of the script:
Loop through each column, concatenate contents from first row to the maximum row, paste contents into a search field, ultimately download something from website.

Currently my script gets an error:
[error][ignored] ROW[1] is undefined

Questions:
(1) Why does this error happen? See script below.
(2) Why does this error not happen in the DemoCsvReadArray script?
(3) Previously running at 2.5 mins/100 lines (fast !replayspeed). I wish to apply script to 140,000 lines, so please feel free to suggest anything to increase speed.

The Edited script:

{
      "Name": "Sub_read_csv_details_and_store_v3-2",
      "CreationDate": "2020-6-14",
      "Commands": [
        {
          "Command": "store",
          "Target": "true",
          "Value": "!errorignore"
        },
        {
          "Command": "store",
          "Target": "fast",
          "Value": "!replayspeed"
        },
        {
          "Command": "comment",
          "Target": "Store an empty Var value prior to use in executeScript_Sandbox",
          "Value": ""
        },
        {
          "Command": "store",
          "Target": "",
          "Value": "Var_All_Lines"
        },
        {
          "Command": "store",
          "Target": "",
          "Value": "Var_Max_Columns"
        },
        {
          "Command": "store",
          "Target": "0",
          "Value": "Var_Line_Counter"
        },
        {
          "Command": "store",
          "Target": "0",
          "Value": "Var_Column_Counter"
        },
        {
          "Command": "comment",
          "Target": "Read the array again from csv file",
          "Value": ""
        },
        {
          "Command": "csvReadArray",
          "Target": "test.csv",
          "Value": "myCSV"
        },
        {
          "Command": "comment",
          "Target": "Get the number of rows in file",
          "Value": ""
        },
        {
          "Command": "store",
          "Target": "${!CsvReadMaxRow}",
          "Value": "Var_Max_Rows"
        },
        {
          "Command": "comment",
          "Target": "Get the number of columns in file",
          "Value": ""
        },
        {
          "Command": "executeScript_Sandbox",
          "Target": "return ${mycsv[0]}.length;",
          "Value": "Var_Max_Columns"
        },
        {
          "Command": "comment",
          "Target": "Print the number of rows in the file",
          "Value": ""
        },
        {
          "Command": "echo",
          "Target": "Number of rows = ${Var_Max_Rows}",
          "Value": "green"
        },
        {
          "Command": "comment",
          "Target": "Print the number of columns in the file",
          "Value": ""
        },
        {
          "Command": "echo",
          "Target": "Number of columns = ${Var_Max_Columns}",
          "Value": "pink"
        },
        {
          "Command": "comment",
          "Target": "Store an empty Var value prior to use in executeScript_Sandbox",
          "Value": ""
        },
        {
          "Command": "store",
          "Target": "",
          "Value": "row"
        },
        {
          "Command": "comment",
          "Target": "Loop over all CSV values",
          "Value": ""
        },
        {
          "Command": "forEach",
          "Target": "myCSV",
          "Value": "row"
        },
        {
          "Command": "comment",
          "Target": "Do the following for each column in the file",
          "Value": "brown"
        },
        {
          "Command": "echo",
          "Target": "column1=${row[0]}",
          "Value": "brown"
        },
        {
          "Command": "comment",
          "Target": "Confirm if file status is OK, Confirm current Column Number and current Row Number",
          "Value": "Blue"
        },
        {
          "Command": "executeScript_Sandbox",
          "Target": "return Number(${Var_Column_Counter}) + 1",
          "Value": "Var_Aesthetic_Column"
        },
        {
          "Command": "executeScript_Sandbox",
          "Target": "return Number(${Var_Line_Counter}) + 1",
          "Value": "Var_Aesthetic_Row"
        },
        {
          "Command": "echo",
          "Target": "File status = ${!csvReadStatus},  Column Number = ${Var_Aesthetic_Column} ,  Line Number = ${Var_Aesthetic_Row}",
          "Value": "Blue"
        },
        {
          "Command": "comment",
          "Target": "If statement begin. First product numbers should be listed without the comma before it. First part of if statement ensures this.",
          "Value": ""
        },
        {
          "Command": "if_v2",
          "Target": "${Var_Line_Counter} == 0",
          "Value": ""
        },
        {
          "Command": "comment",
          "Target": " Append the variable called Var_All_Lines with the product numbers value from column",
          "Value": ""
        },
        {
          "Command": "executeScript_Sandbox",
          "Target": "return (${Var_All_Lines})+${row[${Var_Line_Counter}][${Var_Column_Counter}]}",
          "Value": "Var_All_Lines"
        },
        {
          "Command": "comment",
          "Target": "Test the if else loop",
          "Value": ""
        },
        {
          "Command": "echo",
          "Target": "echo ${Var_All_Lines}",
          "Value": "green"
        },
        {
          "Command": "else",
          "Target": "",
          "Value": ""
        },
        {
          "Command": "comment",
          "Target": " Append the variable called Var_All_Lines with the product numbers value from column",
          "Value": ""
        },
        {
          "Command": "executeScript_Sandbox",
          "Target": "return (${Var_All_Lines})+\", \"+${row[${Var_Line_Counter}][${Var_Column_Counter}]}",
          "Value": "Var_All_Lines"
        },
        {
          "Command": "comment",
          "Target": "Test the if else loop",
          "Value": ""
        },
        {
          "Command": "echo",
          "Target": "echo ${Var_All_Lines}",
          "Value": "green"
        },
        {
          "Command": "comment",
          "Target": "End IfElse statement",
          "Value": ""
        },
        {
          "Command": "end",
          "Target": "",
          "Value": ""
        },
        {
          "Command": "comment",
          "Target": "Update the line number",
          "Value": ""
        },
        {
          "Command": "executeScript_Sandbox",
          "Target": "return Number(${Var_Line_Counter}) + 1",
          "Value": "Var_Line_Counter"
        },
        {
          "Command": "comment",
          "Target": "Update the column number",
          "Value": "Var_Column_Counter"
        },
        {
          "Command": "comment",
          "Target": "executeScript_Sandbox // return Number(${Var_Column_Counter}) + 1",
          "Value": "Var_Column_Counter"
        },
        {
          "Command": "comment",
          "Target": "End ForEach Statement",
          "Value": ""
        },
        {
          "Command": "end",
          "Target": "",
          "Value": ""
        },
        {
          "Command": "comment",
          "Target": "Create and show notification once the final list of product numbers is complete",
          "Value": ""
        },
        {
          "Command": "echo",
          "Target": "${Var_All_Lines}",
          "Value": "#shownotification"
        },
        {
          "Command": "",
          "Target": "",
          "Value": ""
        }
      ]
    }

Extra Info:
Nb. Linked to this earlier question: How to select a whole column of data in csv file at once and save as a variable?

@ulrich
@Plankton

Thank you for your time and your help! :slightly_smiling_face:

I have not tried your code either because it is too long and because in the forum it is formatted incorrectly and it would take too long to correct it and even to understand it, it is really too long.

One possible mistake you made may be what you used some of my code to calculate the number of rows but there is a difference between the csvread I use and the cssreadarray. In the csvread the first row is 1 (1 to 100), in the csvreadarray it is 0 (from 0 to 99) so maybe when you calculated Var_Max_Rows you did not consider this difference and your macro works on a non-existent row (csvread start from 1 but csvreadarray start from 0).

Mine is a hypothesis because your code is really too long and it takes too long to study it.

I do not use the csvreadarray precisely because it starts from 0 and it creates confusion with number of rows of the csv and therefore I always prefer the csvread that starts from 1.

https://ui.vision/rpa/docs/selenium-ide/csvread

1 Like

@newuserkantu Thank you for your very fast reply. Yes, I am trying to see the difference between the csvReadArray and the csvRead in terms if time to run the script. I will make sure to look into this difference! Thanks for the tip

Let me check and will be back to update soon.

Also, I have updated the script now, for anyone interested in looking it. :slight_smile: Hope this format is better now.

I tried your macro code and give more errors.

Hi All,
Thanks @newuserkantu for your fast replies.
I’ll be sure to add the working script on here after work! It uses and internal variable: ${!times} and so avoids looping over a user-made counter variable.