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? - #6 by newuserkantu
Thank you for your time and your help!