Read Binary file records where length varies

[vb6novice]

I want to use VBA in Excel to open a .txt file in which length of each string of characters varies from record to record. The goal is to peel off either characters 1 to 11 if they are numeric, or some other other set of characters if they are not.

What is the best way to read in each line if the character length varies.

 

[Bong]

    Open fname For Input As #1
    Do While Not EOF(1)
        Line Input #1, strLineVar
    Loop
    Close #1

Where, obviously, fname is the file name. The variable, strLineVar, holds each line. Since you said your file was .txt, I'm assuming it has linefeeds. If not, as in truly binary files, I find it necessary to read 1 character (byte) at a time:

    Open fname For Binary As #1
    strOneRec=""
    For i = 1 To iSomeNumberOfBytes
        a1 = Input(1, #1)
        strOneRec=strOneRec & a1
    Next

_________________
Bob Rashkin

 

[vb6novice]

Thanks for the input Bong,

Actually the file I received does not have an extension, so it isn't really a .txt. When I open it with UltraEdit, however, it appears as though there is some sort of line ending character at the end of each line.

When I use the Line Input #1, it tries to read in the entire file (which has over 100,000 rows), so Len(strLineVar) is > 6 million. Which is why I created this thread.

 

[PHV]

What is the ascii (or hex) code of this line ending character ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vb6novice]

Ascii = 13

Here's a sample:

12815886397 A0000001009684 0220717381 8134805304
12816320455 A0000003428B6D 9999999999
NA A00000010171AC 000000001 5059777417
NA A0000001018694 000000001 8044566537
12805591940 NA 0520759191 4045566294


The apparent spaces between the values are either spaces or tabs and spaces - it varies, even when records seem to have similar data.

 

[PHV]

Ascii = 13
Seems like a Mac file ...
You may try something like this:
Dim fso As Object, f As Object, i As Long, a
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fspenTextFile("\path\to\file.ext", 1)
a = Split(f.ReadAll, vbCr)
For i = 0 To Ubound(a)
If IsNumeric(Left(a(i), 11)) Then
' do your stuff here
End If
Next
f.Close
Set f = Nothing
Set fso = Nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886