Playing with arrays... sort of

[bdl004]

Hi Guys,

I have a bit of a strange question...

What I have is an array such as the following: myArray(1)=(1 2 3 4 5), myArray(2)=(6 7 8 9 10), myArray(3)=(11 12 13 14 15), and myArray(4)=(16 17 18 19 20).


What I would like to do is to change this array such that I end up with: newArray(1)=1, newArray(2)=2, newArray(3)=3, etc. until myArray(20)=20.

I have tried using "split" to no avail thusfar and I am wondering if it is possible to do this?

Thanks very much in advance!
Brett

[Bob Phillips]

[vba]

ReDim myarray(1 To 4)
myarray(1) = Array(1, 2, 3, 4, 5)
myarray(2) = Array(6, 7, 8, 9, 10)
myarray(3) = Array(11, 12, 13, 14, 15)
myarray(4) = Array(16, 17, 18, 19, 20)

ReDim NewArray(1 To 1)
For i = LBound(myarray) To UBound(myarray)

For j = LBound(myarray(i)) To UBound(myarray(i))

k = k + 1
ReDim Preserve NewArray(1 To k)
NewArray(k) = myarray(i)(j)
Next
Next i
[/vba]

[bdl004]

That works great! Thanks for the reply!

[bdl004]

Is it possible to alter this method if the values are space/tab delimited instead of comma? I am creating the array by reading from a .txt file, line by line, where values are separated by either tab or space. I would prefer not to create a .csv file as there is a lot of data to process.

[mdmackillop]

[VBA]Sub GetData()
Dim fs, a
Dim arr
Dim newArray()
Dim i

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.openTextFile("c:\AA\testfile.txt")
arr = Split(a.readall, vbTab)
a.Close
ReDim newArray(UBound(arr))
For i = 0 To 200
newArray(i) = arr(i)
Debug.Print newArray(i)
Next
End Sub[/VBA]

[bdl004]

Thanks for your quick reply. I have tried this method as well but I arrive at a new problem, as the text files that I am reading all have different numbers of lines of garbage on top. Can your code be modified to start at line "n" of the text file?

[bdl004]

I have attached an example text file to show what I mean... In this case, the data must start being read at "2004-...." and continue to EOF (reads a date, time, number, date, time, number, etc. in to array(1),array(2),array(3), array(4), array(5),array(6)..... )

Also note, sometimes there are more than 3 columns (the user will input this to a sheet prior to running the code).

Cheers,
Brett

[Bob Phillips]

[vba]

Dim NewArray As Variant
Dim LastRow As Long
Dim i As Long

Workbooks.OpenText Filename:="C:\example.txt", _
Origin:=xlMSDOS, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Space:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
Columns("A:A").EntireColumn.AutoFit
Do

i = i + 1
Loop Until IsDate(Cells(i, "A"))

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
NewArray = Cells(i, "A").Resize(LastRow - i - 1, 3)
[/vba]

[bdl004]

Thanks for your replys: I would like to use your method, xld, but I am trying to avoid printing to a sheet, as the files I will be inputting are very large and take a long time to print. Is there a way around this?

Thanks

[GTO]

Hi Brett,

Does this go along with http://www.vbaexpress.com/forum/showthread.php?t=33134 ?

If so, did you decide against the .csv?

Sorry if I'm misunderstanding.

Mark

[bdl004]

Hi Mark,

It does indeed go along with that thread... I have trying to entertain different ideas but am constantly running in to problems. The .csv method works well, however I am noticing that even in the creation and opening of the .csv, the program takes a very long time (not due to your code, simply due to the shear size of file).

I am now trying to work only with the original text file. I have found that I can either input the entire file element by element, which has the problem of unkown amounts of garbage, or I can input the data line by line, which eliminates the garbage problem (I know how many lines of garbage), but then I don't know how to parse each line such that one element fills one spot in an array.

The closest I have been is using a combination of "Split" and "Join", but this seems to either freeze the program or give me a mismatch error. This is what I was using:

newArray = Split(Join(myArray, Chr(9) OR Chr(32)), Chr(9) OR Chr(32))

[bdl004]

I found a solution:

Open filename for Input as #1
Do While Not EOF(1)
iCount=0
iCount=iCount+1

If iCount<=Number_Garbage_Rows Then
Line Input #1, dummyvariable
End If

If iCount>Number_Garbage_Rows Then
Input #1, gooddata
End If

Loop



My last (hopefully) issue is how to specify the delimiters when opening a file in this way? It wants to use "-" as a delimiter and I want only "space" and "tab". Anyone have any thoughts on this?

[GTO]

Hi Brett,

I would think that the native text import would be faster, but you could try a Regular Expression to delimit the results and test speed.

Option Explicit
    
Sub Main()
Dim strFullName As String
Dim ArrayRes    As Variant
    
    strFullName = "G:\2010\2010-06-30\bdl004\vbax33155#7_example\Copy of example.txt"
    ArrayRes = RetArrays(strFullName, 5)
    
    MsgBox ArrayRes(4, 1) & vbTab & ArrayRes(4, 2) & vbTab & ArrayRes(4, 3)
    
End Sub
    
Function RetArrays(FFName As String, NoLines2Skip As Long) As Variant
Dim FSO             As Object ' FileSystemObject
Dim fsoTFile        As Object ' TextStream
Dim REX             As Object ' RegExp
Dim rexMatches      As Object ' MatchCollection
Dim strRaw          As String
Dim i               As Long
Dim x               As Long
Dim y               As Long
Dim aryLayedOver    As Variant
Dim aryTransposed   As Variant
    
ReDim aryLayedOver(1 To 3, 1 To 500000)
    
    Set REX = CreateObject("VBScript.RegExp")
    With REX
        .Global = False
        .MultiLine = False
        
        '2004-10-01 00:00:00    0
        .Pattern = "(\b[0-9]{4}-[0-9]{2}-[0-9]{2}\b)" & _
                   "(\ |\t)" & _
                   "([0-9]{2}\:[0-9]{2}\:[0-9]{2})" & _
                   "(\ |\t)" & _
                   "([0-9]*)"
    End With
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set fsoTFile = FSO.OpenTextFile(FFName, 1, False, &HFFFFFFFE)
    
    With fsoTFile
        For i = 1 To NoLines2Skip
            .SkipLine
        Next
        
        i = 0
        
        Do While Not .AtEndOfStream
            strRaw = .ReadLine
            If REX.Test(strRaw) Then
                Set rexMatches = REX.Execute(strRaw)
                i = i + 1
                aryLayedOver(1, i) = CDate(rexMatches(0).SubMatches(0))
                aryLayedOver(2, i) = CDate(rexMatches(0).SubMatches(2))
                aryLayedOver(3, i) = CLng(rexMatches(0).SubMatches(4))
            End If
        Loop
        .Close
    End With
    
'// If you wanted to leave the array as 3 rows * x columns, we could trim to fit here.  //
'    ReDim Preserve aryLayedOver(1 To 3, 1 To i)
    ReDim aryTransposed(1 To i, 1 To 3)
    For x = 1 To i
        For y = 1 To 3
            aryTransposed(x, y) = aryLayedOver(y, x)
        Next
    Next
    
    RetArrays = aryTransposed
End Function

Mark

[bdl004]

Hi Mark,

Thanks again for helping me out! I think you are right about the native text import being faster. I'll definitely work through these further and see if I can trim some minutes off the run time

Cheers,
Brett