Read text file into multi-dimensional array 2

[wvandenberg]

I am attempting to read a text file into a multi-dimensional array. I am able to read the file into a one-dimensional array (each line is one element in the array) However, I now need to split each line into it's own array and then store that into an array. Here is an example of the text file:

12/01/2003 13:32:45 21.67 0.005 8.12 8.10 8.326
12/01/2003 13:33:00 21.67 0.005 8.12 8.10 8.312
12/01/2003 13:33:15 21.67 0.005 8.12 8.11 8.325
12/01/2003 13:33:30 21.67 0.005 8.12 8.11 8.311
12/01/2003 13:33:45 21.67 0.005 8.12 8.11 8.331
12/01/2003 13:34:00 21.68 0.005 8.12 8.09 8.320

So I would like the multi-dimensional array to store it something like this:

arrValues(0,0) would hold 12/01/2003
arrValues(4,6) would hold 8.331
arrValues(5,1) would hold 13:34:00 etc.

Here is the code I have so far:

Dim fsoFile As FileSystemObject
Dim tsoText As TextStream
Dim i As Integer
Dim arrTextLines()
Dim strLine As String

Set fsoFile = New FileSystemObject
Set tsoText = fsoFile.OpenTextFile(strFile, ForReading)
Set rsTxtStrm = New Recordset

i = 0

Do Until tsoText.AtEndOfStream
strLine = tsoText.ReadLine

'The following I statement skips any header information that may be in the file
If Left(strLine, 2) = "**" _
Or Left(strLine, 2) = " " _
Or Left(strLine, 2) = "==" _
Or Left(strLine, 2) = "--" Then
Else
ReDim Preserve arrTextLines(i)
arrTextLines(i) = strLine
i = i + 1
End If
Loop
tsoText.Close

As I mentioned earlier, the code above will only read the text file into a one-dimensional array (line by line) and I need it read into a multi-dimensional array (line by line and value by value). Just so you know, there are consecutive delimiters (2-3 spaces because it a fixed width file) between the numerical values in each line. I do have a function that is similar to the Split() function but it will ignore consecutive delimiters if it is needed (I haven't included it here). Also, there will only ever be a total of 8 columns (they didn't all fit neatly so I chopped two for this post) but the number of rows will vary.

If anyone has any suggestions I would be really grateful.

Thanks in Advance,
Wendy

 

[zevw]

Use the Split Function I don't use this code for an array but you can get all the values in seperate elements

Dim adrr, splt As Variant
Dim formlink, cnt

Me.FullAddress = "1234 56 Street"

adrr = Split(Me.FullAddress, " &quot

cnt = 0

For Each splt In adrr
If cnt = 0 Then
Me.addr = splt
ElseIf cnt = 1 Then
Me.strnum = splt
ElseIf cnt = 2 Then
Me.strtyp = splt
Else
Me.FullAddress = ""
End If
cnt = cnt + 1
Next

End Sub

 

[cmmrfrds]

Instead of an array, you could create a temporary ADO recordset and manipulate your data from the recordset. What are you going to do with the data once it is loaded into the array?

 

[wvandenberg]

Once in the array, I need to be able to do two things:

1. append the last line to another text file that will be uploaded to the internet

2. access certain (as yet undetermined) values contained within the last four lines and perform calculations with them


Wendy

 

[wvandenberg]

I noticed that if I change the following line:
arrTextLines(i) = strLine
to:
arrTextLines(i) = Split(strLine)

That I think it is storing an array of each line within arrTextLines() but I cannot access the info in the debug window using for example arrTextLines(0,1) ("subscript out of range&quot or arrTextLines(0) ("Type mismatch&quot.

Not sure if this helps at all or just confuses the issue but I'll keep trying.

Thanks,
Wendy

 

[RoyVidar]

Hi!

Must confess I agree with cmmrfrds about using some other kind of temporary storage, either as his suggestion or dump the lines one to a temporary table with sql/docmd.

The Preserve keyword with arrays/loops has significant recourse implications.

Addressing only the array issue:

Two dimentional arrays. Only the last dimension of the array might be changed using the Redim Preserve (check F1):

[tt] ReDim Preserve arrTextLines(N,i) ' N=Number of fixed columns-1
arrTextLines(0,i) = <first string>
arrTextLines(1,i) = <second string>
arrTextLines(2,i) = <third string>
...
i = i + 1[/tt]

HTH Roy-Vidar

 

[RoyVidar]

continuing... (really just considering the array issue;-))

just thinking, if the txt file is fixed with, the following should do the trick.

[tt]Else
ReDim Preserve arrTextLines(6, i)
arrTextLines(0, i) = Mid$(strLine, 1, 10)
arrTextLines(1, i) = Mid$(strLine, 12, 8)
arrTextLines(2, i) = Mid$(strLine, 21, 5)
arrTextLines(3, i) = Mid$(strLine, 28, 5)
arrTextLines(4, i) = Mid$(strLine, 36, 4)
arrTextLines(5, i) = Mid$(strLine, 42, 4)
arrTextLines(6, i) = Mid$(strLine, 47, 5)
i = i + 1
End If [/tt]

Just the arrvalues would turn something like this:
arrValues(0,0) would hold 12/01/2003
arrValues(4,6) would be <Out of context> (your sample data only contained 6 rows)
arrValues(5,1) would hold 8.10

Roy-Vidar

 

[CajunCenturion]

One approach that you can try is to use ADO with a separate connection and recordset, and the GetRows method to read the entire file directly into a two-dimensional variant array. To do that, you first need to build a Schema.ini file which will look something like the following, based on the sample data set provided.

[SimpleText.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=0
CharacterSet=OEM
Col1=TheDate Char Width 10
Col2=TheTime Char Width 9
Col3=Value1 Float Width 6
Col4=Value2 Float Width 7
Col5=Value3 Float Width 7
Col6=Value4 Float Width 6
col7=Value5 Float Width 6

This file must be named Schema.ini, and must be in the same directory as the text file, and the name enclose in [] is the text file name as well. Once that is set up, the following function is all you need.

Private Function cmdReadTextFile_Click() As Variant

   Dim lCon_ConnObj           As ADODB.Connection
   Dim lRst_TextRecSet        As ADODB.Recordset
   Dim lStr_TextDirName       As String
   Dim lStr_TextFileName      As String
   Dim lVar_DataArray         As Variant
   
   lStr_TextDirName = "C:\DirectoryName\"
   lStr_TextFileName = "SimpleText.txt"
   
   Set lCon_ConnObj = New ADODB.Connection
   lCon_ConnObj.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
                                   "Dbq=" & lStr_TextDirName & ";" & _
                                   "Extensions=asc,csv,tab,txt;" & _
                                   "Extended Properties='text;HDR=NO;FMT=Delimited'"

   lCon_ConnObj.Open
   If (lCon_ConnObj.State = adStateOpen) Then
      Set lRst_TextRecSet = New ADODB.Recordset
      lRst_TextRecSet.Open "Select * From " & lStr_TextFileName, lCon_ConnObj, adOpenStatic, adLockReadOnly, adCmdText
      If (lRst_TextRecSet.State = adStateOpen) Then
         lVar_DataArray = lRst_TextRecSet.GetRows
         lRst_TextRecSet.Close
      End If
      Set lRst_TextRecSet = Nothing
      lCon_ConnObj.Close
   End If
   Set lCon_ConnObj = Nothing
   cmdReadTextFile_Click = lVar_DataArray
   
End Function

This function will return a two-dimensional variant array populated from the text file.

Important Note: Unfortunately, the GetRows method returns the array in Column Major order so you need to revise you subscripting. The FieldID is the first subscript and the RecordID is the second subscript.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[wvandenberg]

Thank you for everyone's help. I will try out the suggestions.

Wendy