Automation in code generation: Lesson 3 – Changing a string into a vector.

For the purposes of this article, we will consider option 1 from Lesson 2 is being used.

After the .csv file has been read, we are left with a string that is equivalent to the contents of the whole table. Rows are separated by line breaks, and columns by either a semicolon (;) or a comma (,), depending on Windows country and language settings.

Example:

SE1;5201;Driver1;Tag1
SE1;5202;Driver1;Tag2
SE1;5203;Driver1;Tag3
SE2;5204;Driver2;Tag1
SE2;5205;Driver2;Tag2
SE2;5206;Driver2;Tag3
SE3;5207;Driver3;Tag1
SE3;5208;Driver3;Tag2
SE3;5209;Driver3;Tag3

To separate data individually (cell by cell), you must turn the string into a vector. This can be done via vbScript’s Split method, which has one mandatory parameter and three optional ones:

Split(expression[, delimiter[, count[, compare]]])

The expression parameter must be replaced by the desired string. The delimiter parameter indicates which separator must be used. The other two parameters are not relevant to this case, and must therefore be left blank.

Usage example:

auxVetor = Split(auxString, vbNewLine)

In this example, the auxString variable is the string equivalent to the spreadsheet’s content, and vbNewLine constant is the line break character.

Now, auxVetor variable contains a vector with as many positions in it as the number of rows in the table:

auxVetor(0) contains the string "SE1;5201;Driver1;Tag1"
auxVetor(1) contains the string "SE1;5202;Driver1;Tag2"
auxVetor(2) contains the string "SE1;5203;Driver1;Tag3"

and so on.

The next step is to separate each row’s column. To do so, you must scan all positions of the vector, and once again execute Split method, only this time passing a semicolon (;) as parameter.

Example:

'separates the string into a row vector 
auxVetor = split(auxString, vbNewline)

'for each row 
for i=0 to Ubound(auxVetor)

'separates the row into a cell vector
vetorColunas = split(auxVetor(i), ";")

'saves the cells in script variables
auxSE = vectorColumns(0)
auxEquip = vectorColumns(1)
auxDriver = vectorColumns(2)
auxTag = vectorColumns(3)

   'here goes the rest of the automated script, which will use the data
'organized above to create/set up the desired objects
'...
'...
'... 

next

NOTE: The template to be used in the spreadsheet (which data will be saved, and in which order it will happen) can vary according to the user. Therefore, the sample script above will vary according to the defined template.