Multi-Dimensional Array

[noname]

Hi,

I am trying to parse a SAS output text file (running into >4,00,000
lines containing multiple combinations), into a multi-dimensional
array (4 dimensions). i know that only the last dimension can be
increased and the middle dimensions are fixed.

My question is how do i redim preserve the middle dimensions (1st, 2nd
and 3rd)? i have seen people use Transpose function with 2 dimensions.
but i came to know it has its limitations when it comes to size of
data. so is there a function UDF to change the 1st 3 dimensions
dynamically while adding / removing data?

please let me know if someone has tried this before.

 

[isabelle]

hi noname,

here is an example, i hope this may help

'copy the table into a temporary array the same size.
'resize the table from two dimensions at once which in practice destroyed
'and recreated a table of the same name but different sizes
'pour the contents of the temporary array in the table "starting" resized. Table "start" should be resized larger than the table starting
'add new data in the table

Sub RedimArray_xD()

Dim Tblo() As Integer
Dim Tmp() As Integer
Dim a As Integer
Dim i As Integer, j As Integer

ReDim Tblo(3, 4)
For i = 1 To 3
For j = 1 To 4
a = i * 10 + j
Tblo(i, j) = a
Next j
Next i

ReDim Tmp(3, 4)
Tmp = Tblo

'depending on the version of Excel so can not work in this case using the loop below'For i = LBound(Tblo, 1) To UBound(Tblo, 1)
' For j = LBound(Tblo, 2) To UBound(Tblo, 2)
' Tmp(i, j) = Tblo(i, j)
' Next j
'Next i


ReDim Tblo(5, 7)

For i = LBound(Tmp, 1) To UBound(Tmp, 1)
For j = LBound(Tmp, 2) To UBound(Tmp, 2)
Tblo(i, j) = Tmp(i, j)
Next j
Next i


For i = 1 To 5
For j = 5 To 7
a = i * 100 + j
Tblo(i, j) = a
Next j
Next i
For i = 4 To 5
For j = 1 To 4
a = i * 100 + j
Tblo(i, j) = a
Next j
Next i

Range("A1").Resize(UBound(Tblo), 6) = Application.Transpose(Tblo)

End Sub

 

[noname]

Hi Isabelle,

Is this example for a 4 dimensional array or a 2 dimensional one?

Cheers!

 

[noname]

as an example, if i have an array:

Arr(0 to 5, 0 to 4, 0 to 10, 0 to 7)

i can only increase the last dimension.

if i want to dynamically dimension the 1st three, how do i do that? can a UDF function be written which can be passed a multi-dimensional array which will dynamically increase any of its dimensions?

Arr(0 to 20, 0 to 10, 0 to 15, 0 to 7)

 

[GS]

noname wrote on 10/16/2011 :

Hi,

I am trying to parse a SAS output text file (running into >4,00,000
lines containing multiple combinations), into a multi-dimensional
array (4 dimensions). i know that only the last dimension can be
increased and the middle dimensions are fixed.

My question is how do i redim preserve the middle dimensions (1st, 2nd
and 3rd)? i have seen people use Transpose function with 2 dimensions.
but i came to know it has its limitations when it comes to size of
data. so is there a function UDF to change the 1st 3 dimensions
dynamically while adding / removing data?

please let me know if someone has tried this before.

I'd prefer to 'dump' the entire file into a Variant (resulting in a
zero-based array) and parse each line as per need dictates. So each
line in the text file is an element of the array, and each element gets
parsed according to its delimiter.

Example <aircode>:
'Get the text
Const sFilename As String = "C:\SAS.txt"
Dim sFileText As String
sFileText = ReadTextInFile(sFilename)
'..where 'ReadTextInFile()' is a function that takes a filename arg

'Dump file contents into an array
Dim vTextIn As Variant
vTextIn = Split(sFileText, vbCrLf)

'Parse the array one element at a time
Dim n As Long, x As Long, v As Variant
Const sDelimiter As String = vbTab '//edit to suit
For n = LBound(vTextIn) To UBound(vTextIn)
v = Split(vTextIn(n), sDelimiter)
For x = LBound(v) To UBound(v)
'//do stuff code goes here
Next 'x
Nxt 'n

This way you don't have to dimension arrays. You could even create each
element in vTextIn as an array, resulting in an array of arrays.

 

[GS]

GS made a typo...:

Example <aircode>:
'Get the text
Const sFilename As String = "C:\SAS.txt"
Dim sFileText As String
sFileText = ReadTextInFile(sFilename)
'..where 'ReadTextInFile()' is a function that takes a filename arg

'Dump file contents into an array
Dim vTextIn As Variant
vTextIn = Split(sFileText, vbCrLf)

'Parse the array one element at a time
Dim n As Long, x As Long, v As Variant
Const sDelimiter As String = vbTab '//edit to suit
For n = LBound(vTextIn) To UBound(vTextIn)
v = Split(vTextIn(n), sDelimiter)
For x = LBound(v) To UBound(v)
'//do stuff code goes here
Next 'x

Next 'n

Sorry about that!

 

[noname]

Hi Garry,

thanks for replying.

what did you mean by "You could even create each element in vTextIn as an array, resulting in an array of arrays."

can you explain this with an example?

Thanks in advance.

 

[GS]

noname brought next idea :

Hi Garry,

thanks for replying.

what did you mean by "You could even create each element in vTextIn as an
array, resulting in an array of arrays."

can you explain this with an example?

Thanks in advance.

Using the example I posted, if you take each element of your vTextIn
array and break it into an array using the Split() function then each
element of vTextIn array is now an array in itself. So...


For n = LBound(vTextIn) To UBound(vTextIn)
vTextIn(n) = Split(vTextIn(n), sDelimiter)
If lMaxCols < UBound(vTextIn(n)) Then lMaxCols = UBound(vTextIn)
Next 'n
'Dump the array into a worksheet
Range("A1").Resize(UBound(vTextIn), lMaxCols) = _
Application.WorksheetFunction.Transpose(vTextIn)

 

[noname]

Hi Garry,

i get a type mismatch error on the following line:

vTextIn(n) = Split(vTextIn(n), sDelimiter)

any ideas why this is happening? i have declared vTextIn as a variant.

 

[noname]

Hi Garry,

i am getting a "Type Mismatch error" on this line:

vTextIn(n) = Split(vTextIn(n), sDelimiter)

i have defined vTextIn as a variant.

any ideas why this is happening?

 

[GS]

noname used his keyboard to write :

Hi Garry,

i get a type mismatch error on the following line:

vTextIn(n) = Split(vTextIn(n), sDelimiter)

any ideas why this is happening? i have declared vTextIn as a variant.

The code I posted was flagged as '<aircode>' meaning it was intended
to illustrate the concept of creating an array of arrays. In all
probability it will be necessary to use ReDim Preserve on a new array
and put each element of the source array into a new array rather than
back into the source array. Also, it may require using another variable
before populating the element...

Dim vTemp As Variant
vTemp = Split(vTextIn(n), sDelimiter)
vTextIn(n) = vTemp

...where you create an array of the source array's element and place
that back into the same element 'as an array'. This might be how VBA
needs it to be so you don't get the 'type mismatch' error. Otherwise...

Dim vNewArray() As Variant, vTemp As Variant
vTemp = Split(vTextIn(n), sDelimiter)
ReDim Preserve vNewArray(n): vNewArray(n) = vTemp

...which is probably the best approach.

I don't have data to test this and so is why I flagged it as
'<aircode>'. Mostly, I dump entire ranges into a Variant and work with
that because it's usually easier/faster than looping cells. For
example, a range of table data ("A1:E10") results in a 10Row x 5Col 1
based array. So...

myArray(1, 1) reps Cells(1, 1) [$A$1] of the range;
myArray(2, 2) reps Cells(2, 2) [$B$2] of the range;
myArray(1, 5) reps Cells(1, 5) [$E$1] of the range...

 

[noname]

Hi Garry,

I realized that as Split function creates a one dimensional array, this wont work as its assigning an array of elements to the same index n:

vTextIn(n) = Split(vTextIn(n), sDelimiter)

so i modified my code like this:


'**************************************
Option Explicit

Const sFilename As Variant = "SAS.txt"
Const sDelimiter As String = "<"

Sub ParseFile()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim sFileText As String
Dim vTextIn As Variant, vStr As Variant, vTextIn2 As Variant
Dim n As Long, x As Long, v As Variant
Dim lMaxCols As Long

sFileText = ReadTextInFile(ThisWorkbook.Path & "\" & sFilename)

'Dump file contents into an array
vTextIn = Split(sFileText, vbCr)

ReDim vTextIn2(UBound(vTextIn), 0)
For n = LBound(vTextIn) To UBound(vTextIn)
If n = 22 Then Stop
vStr = Split(vTextIn(n), sDelimiter)
For v = LBound(vStr) To UBound(vStr)
If vStr(v) <> vbNullString Then
vTextIn2(n, v) = vStr(v)
ReDim Preserve vTextIn2(UBound(vTextIn2, 1), UBound(vTextIn2, 2) + 1)
End If
Next v
If lMaxCols < UBound(vTextIn2, 2) Then lMaxCols = UBound(vTextIn2, 2)
Next n
ReDim Preserve vTextIn2(UBound(vTextIn2, 1), UBound(vTextIn2) - 1, 2)

'Dump the array into a worksheet
Range("A1").Resize(UBound(vTextIn2, 1), lMaxCols) = Application.WorksheetFunction.Transpose(vTextIn2)

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Function ReadTextInFile(sFilenameAndPath As String) As String
Dim vFile As String

Open sFilenameAndPath For Input As #1
vFile = Input$(LOF(1), 1)
Close #1
ReadTextInFile = vFile
End Function
'**************************************



just to show you what i am trying to grab from the text file, here is an excerpt:

'**************************************
<th class="r b Header" scope="col">Number in<br>Model</th>
<th class="r b Header" scope="col">R-Square</th>
<th class="r b Header" scope="col">SSE</th>
<th class="l b Header" scope="col">Variables in Model</th>
</tr>
</thead>
<tbody>
<tr>
<th class="r RowHeader" scope="row">1</th>
<th class="r Data">0.6484</th>
<td class="r Data">189.36323</td>
<td class="l Data">x1</td>
</tr>
<tr>
<th class="r RowHeader" scope="row">1</th>
<th class="r Data">0.5855</th>
<td class="r Data">223.25558</td>
<td class="l Data">x2</td>
</tr>
<tr>
<th class="r RowHeader" scope="row">1</th>
<th class="r Data">0.5845</th>
<td class="r Data">223.80530</td>
<td class="l Data">x14</td>
</tr>
<tr>
<th class="r RowHeader" scope="row">1</th>
<th class="r Data">0.5843</th>
<td class="r Data">223.88649</td>
<td class="l Data">x13</td>
</tr>
<tr>
<th class="r RowHeader" scope="row">1</th>
<th class="r Data">0.5790</th>
<td class="r Data">226.77963</td>
<td class="l Data">x6</td>
</tr>
<tr>
<th class="r RowHeader" scope="row">1</th>
<th class="r Data">0.5764</th>
<td class="r Data">228.17697</td>
<td class="l Data">x10</td>
</tr>
<tr>
<th class="r RowHeader" scope="row">1</th>
<th class="r Data">0.5758</th>
<td class="r Data">228.49651</td>
<td class="l Data">x1 x5</td>
</tr>
<tr>
<th class="r RowHeader" scope="row">1</th>
<th class="r Data">0.5485</th>
<td class="r Data">243.19389</td>
<td class="l Data">x2 x9</td>
</tr>
<tr>
<th class="r RowHeader" scope="row">1</th>
<th class="r Data">0.5419</th>
<td class="r Data">246.76102</td>
<td class="l Data">x1 x15</td>
</tr>
<tr>
<th class="r RowHeader" scope="row">1</th>
<th class="r Data">0.5253</th>
<td class="r Data">255.71472</td>
<td class="l Data">x1 x12</td>
</tr>
<tr>
....
....
'**************************************

here i am trying to get the only these values in the array lying between the table dimensions (td's), starting with example:
"l RowHeader" (Number in Model) i.e. 1
"r Data" (R-Square) i.e. 0.6484
"r Data" (SSE) i.e. 189.36323
"l Data" (Variables in Model) i.e. x1


Right now while using above code i am getting another error:
'Out of Memory - error 7'
i think thats because the no of rows in the array is exceeding 30,944,438 & the columns (2nd dimension) is MAX 29. So as an array can hold data only as per the available memory ie. Rows X Columns. So i think its becoming insufficient.
is there a better way to do this? like use a Dictionary object and nest Dictionary objects within it? i would have to use the above values as per each Model combination to calculate Shapeley coefficient.

Looking forward to your valuable advice.
please advice.

 

[GS]

See my followup post regarding optional approaches to building the
array of arrays. As I said, I usually dump the contents of a range into
an array and work with that, then dump the results back into the range.

Note that large amounts of data are usually handled (using arrays) in
comfortable sized 'blocks' rather than trying to process the entire
file contents.

This data appears to be XML or HTML and so yes, there's a much more
efficient way to work with it. I haven't done much parsing of XML files
but if you google for info to read/write XML files with VB6/VBA you'll
find lots of stuff. I believe there's even a Class component available
that lets you work the XML files similar to how INI files are handled.

If you're pulling this from a website then you'll even find samples of
how to parse the webpage source.

Good luck!

 

[noname]

Well the SAS output is in the form of an HTML file.

in Internet Explorer it looks like this:

Number in
Model R-Square SSE Variables in Model
1 0.6484 189.36323 x1
1 0.5855 223.25558 x2
1 0.5047 266.78526 x3
1 0.4643 288.52191 x4
2 0.6716 176.88309 x1 x2
2 0.6671 179.33269 x1 x4
2 0.6628 181.60644 x1 x3
2 0.6165 206.56222 x2 x4
2 0.6014 214.71458 x2 x3
2 0.5394 248.07512 x3 x4
3 0.6803 172.18992 x1 x2 x4
3 0.6740 175.61857 x1 x2 x3
3 0.6701 177.68955 x1 x3 x4
3 0.6183 205.57403 x2 x3 x4
4 0.6854 174.17081 x1 x2 x3 x4

.....
.....
currently this file has 16 variables and their combinations, but the variables could be more. hence the large size. i have to parse it in an array like this and then calculate the Shapeley coeff & score for each variable occurring with others & without others.

so a better design is required so that i can group and sum variable combinations properly.

 

[GS]

noname presented the following explanation :

Well the SAS output is in the form of an HTML file.

in Internet Explorer it looks like this:

Number in
Model R-Square SSE Variables in Model
1 0.6484 189.36323 x1
1 0.5855 223.25558 x2
1 0.5047 266.78526 x3
1 0.4643 288.52191 x4
2 0.6716 176.88309 x1 x2
2 0.6671 179.33269 x1 x4
2 0.6628 181.60644 x1 x3
2 0.6165 206.56222 x2 x4
2 0.6014 214.71458 x2 x3
2 0.5394 248.07512 x3 x4
3 0.6803 172.18992 x1 x2 x4
3 0.6740 175.61857 x1 x2 x3
3 0.6701 177.68955 x1 x3 x4
3 0.6183 205.57403 x2 x3 x4
4 0.6854 174.17081 x1 x2 x3 x4

....
....
currently this file has 16 variables and their combinations, but the
variables could be more. hence the large size. i have to parse it in an array
like this and then calculate the Shapeley coeff & score for each variable
occurring with others & without others.

so a better design is required so that i can group and sum variable
combinations properly.

Now that I know this, you'll find what you need by googling "parse web
page in VB6/VBA". Best wishes...

 

[noname]

Hi Garry,

its a text output file containing HTML tags and not an HTML document. That is why, i am parsing it using Open File for Input As #1.

instead of using arrays, can a Dictionary Object be incorporated?

 

[Mike S]

Hi Garry,
its a text output file containing HTML tags and not an HTML document. That is why, i am parsing it using Open File for Input As #1.
instead of using arrays, can a Dictionary Object be incorporated?

Is it possible to post the file online so it can be downloaded and viewed?

Would a VB6 program work for your needs?

Mike

 

[noname]

Hi Mike,

Yes, its possible. Also a VB program would do.
please give me ur email addy.

thanks