Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Read muli field, multi line text file into 2D string array

Posted on 2004-09-02
Medium Priority
185 Views
Last Modified: 2010-05-02
I have a text file that looks like this....

'314' 'TM15025.PV' 'ON' 'N/A' 'N/A' 'N/A'
'312' 'TM15025.PV' 'ON' 'N/A' 'N/A' 'N/A'
'315' 'TM16027.PV' 'ON' 'N/A' 'N/A' 'N/A'
'316' 'TM16028.PV' 'ON' 'N/A' 'N/A' 'N/A'

I want to have a 2D array that has all this info.

For example.

strArray(0,0) would equal '314'
strArray(0,1) would equal 'TM15025.PV'

I was thinking

----
Dim i as Integer
Do While EOF(1)
Redim Preserve strArray(i, 6)
<Some how read the line into the array>
i = i + 1
Loop
0
Question by:gillgates
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
7 Comments

LVL 4

Expert Comment

ID: 11967253
Hi gillgates,

Take a look at this script :

http://www.vb-helper.com/howto_csv_to_array.html

MrClean
0

LVL 22

Expert Comment

ID: 11967306
Dim i as Long, j as Long
Dim tmpArr() As String

Do While Not EOF(f)
Redim Preserve strArray(i, 6)
Line Input #1, strTmp
tmpArr = Split(strTmp, " ")
for j = LBount(tmpArr) to Ubound(tmpArr)
strArray(i, j) = Replace\$(tmpArr(j), "'")
next j
i = i + 1
Loop
0

LVL 86

Expert Comment

ID: 11969130
The field data is denoted by a beginning single quote ' and an ending single quote '.

If there are NEVER any spaces in the field data, then go with what DarkoLord has given you, otherwise you will have to loop through each input line using Instr() to find the single quotes and extract the data inbetween.

Regards,

Idle_Mind
0

LVL 3

Author Comment

ID: 11969292
I actually figured it out on my own, well almost.  I am not at work anymore so tomorrow morning I will post my code and you can see what I still need...

I can tell you this...

I do a Redim Preserve strData(i, 6) and I get an error, can't remember which one...
I do know that it happens after i goes from 1 to 2
0

LVL 3

Author Comment

ID: 11974455
When I run this I get "Run-time error 9, Subscript out of range"

This happens when it loops around the second time and i = 2
It happens on this line --> ReDim Preserve strInputs(i, 6)

Ok, here is my code...

strInputs() is a global variable

Public Sub ReadFile()
Dim strData As String
Dim i As Integer
Dim y As Integer
Dim q As Integer
Dim s As Boolean

s = False

i = 1
Open app.Path & "\\pisap.tgl" For Input As #1
Do Until EOF(1)
ReDim Preserve strInputs(i, 6)
Line Input #1, strData
q = 1
For y = 1 To Len(strData)
If Mid(strData, y, 1) <> "'" Then
If Mid(strData, y, 1) <> " " Then
strInputs(i, q) = strInputs(i, q) + Mid(strData, y, 1)
s = False
Else
If s = False Then
q = q + 1
s = True
End If
End If
End If
Next
i = i + 1
Loop
End Sub
0

LVL 3

Author Comment

ID: 11974615
I also tried making it so strInputs wasn't a global variable and it didn't help...

Public Sub ReadFile(ByRef strInputs() As String)
Dim strData As String
Dim i As Integer
Dim y As Integer
Dim q As Integer
Dim s As Boolean

s = False

i = 1
Open app.Path & "\\pisap.tgl" For Input As #1
Do Until EOF(1)
ReDim Preserve strInputs(i, 6)
Line Input #1, strData
q = 1
For y = 1 To Len(strData)
If Mid(strData, y, 1) <> "'" Then
If Mid(strData, y, 1) <> " " Then
strInputs(i, q) = strInputs(i, q) + Mid(strData, y, 1)
s = False
Else
If s = False Then
q = q + 1
s = True
End If
End If
End If
Next
i = i + 1
Loop
End Sub
0

LVL 86

Accepted Solution

Mike Tomlinson earned 1000 total points
ID: 11975006
When you redim a multidimensional array with the Preserve keyword, you can only change the size of the last dimension.  So you have to swap your dimensions for it to work like this:

Option Base 1
Option Explicit

Private strInputs() As String

Private Sub Command1_Click()
Dim recNo As Integer
Dim fieldNum As Integer
Dim record As String

ReadFile "c:\someFile.txt"

For recNo = 1 To UBound(strInputs, 2)
record = "RecNo " & recNo & ":"
For fieldNum = 1 To 6
record = record & " " & strInputs(fieldNum, recNo)
Next fieldNum
Debug.Print record
Next recNo
End Sub

Public Sub ReadFile(ByVal fileName As String)
If Dir(fileName) = "" Then
MsgBox fileName, vbCritical, "File not found"
Exit Sub
End If

Dim inputLine As String
Dim firstQuote As Integer
Dim secondQuote As Integer
Dim fieldData As String
Dim fieldNumber As Integer
Dim lineNumber As Integer

lineNumber = 1
Open fileName For Input As #1
Do Until EOF(1)
ReDim Preserve strInputs(6, lineNumber)
Line Input #1, inputLine
firstQuote = 0
For fieldNumber = 1 To 6
firstQuote = InStr(firstQuote + 1, inputLine, "'")
If firstQuote > 0 Then
secondQuote = InStr(firstQuote + 1, inputLine, "'")
If secondQuote > 0 Then
fieldData = Mid(inputLine, firstQuote + 1, secondQuote - (firstQuote + 1))
strInputs(fieldNumber, lineNumber) = fieldData
firstQuote = secondQuote + 1
Else
MsgBox inputLine, vbCritical, "Missing Matching Quote"
End If
Else
MsgBox inputLine, vbCritical, "Invalid Input Line"
Close #1
Exit Sub
End If
Next fieldNumber
lineNumber = lineNumber + 1
Loop
Close #1
End Sub

Regards,

{pseudocode} / {Time} ± ¼*ƒ(Me.Thoughts÷3)^² = Idle »(°_°)« Mind
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month9 days, 1 hour left to enroll

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.