PDA

View Full Version : Playing with arrays... sort of



bdl004
07-20-2010, 04:37 AM
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
07-20-2010, 05:00 AM
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

bdl004
07-20-2010, 05:19 AM
That works great! Thanks for the reply!

bdl004
07-21-2010, 12:17 AM
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
07-21-2010, 12:39 AM
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

bdl004
07-21-2010, 12:45 AM
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
07-21-2010, 01:58 AM
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
07-21-2010, 02:10 AM
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)

bdl004
07-21-2010, 02:23 AM
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
07-21-2010, 02:26 AM
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
07-21-2010, 02:39 AM
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
07-21-2010, 04:52 AM
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
07-22-2010, 03:08 AM
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
07-22-2010, 04:18 AM
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