Using FileSystemObject or the Input # statement to read data from text files

I have to read in some data from text files formated like the example below, with e.g. 5 variables separated by a tab or space delimiter (not f开发者_开发问答ixed-width columns).

Montreal 0.2 h 3 500
Otherplace 0.1 m 50 25
Thirdplace 0.05 m 62 112
[...]

I could read these in using the Input # statement, and that's what I've mostly been doing, because it's the easiest/laziest approach. But:

• The Input # statement doesn't always recognize tabs as delimiters.
• The whole Input # vernacular has a very "deprecated" feel to it!

I'm thinking about modernizing and reading files using the FileSystemObject methods (already do so for writing files).

But doesn't this requires a lot of string manipulation to parse out my 5 variable values? Any suggestions on how best to do this?

Also, what's a good way of determining how many lines there are in the text file? So I can ReDim my arrays ahead of time.

---

Have you considered using Jet to read your textfile as a recordset?

http://www.connectionstrings.com/textfile

Jet is installed with all recent versions of Windows.

---

Instead of INPUT# you could use LINE INPUT#, this will read the whole line.

Determing how many lines there are would be difficult because you're line length is variable. You could however make a educated guess by using : Lines=LOF(Filehandle)/AvarageLineLength And you can dynamically resize your array by using REDIM PRESERVE

Using Fileobject will only add overhead and dependencies to your program. The same goes for the Jet driver. It's not that hard to write a string splitting function ? Search on this site.

---

With FileSystemObject approach can work something like this:

Public Sub ReadLines()

Dim lines ' Variant, so it can take an array
Dim line ' Variant, to make For Each work nicely
Dim fields ' Variant to hold array of field values
Dim i As Integer

    With New FileSystemObject
        With .OpenTextFile("data.txt")
            lines = Split(.ReadAll, vbCrLf) ' assumes you have "normal" Windows line endings
        End With
    End With

    ' Now you can ReDim: UBound(lines) tells you how many records were read

    For i = 1 to UBound(lines)
        fields = Split(line(i), " ")
        CityNames(i) = fields(1)
        ' etc etc
    Next

End Sub

If "ReDim my arrays" means what I think, then I'd also consider creating a Class or Type to hold my input data and a Collection (or Dictionary, since you already have the Scripting runtime loaded) to hold the records.

---

I've decided to use a blend of both approaches (FSO, standard BASIC commands).

I use FileSystemObject most of the time since it's flexible and easy to use. It's a pretty standard thing to use, so I'm not all too worried about dependency issues.

When reading e.g. elements in space-delimited tables one at a time, I use Input #, because it's short to write and I avoid the need to parse strings.