Using binary file access

 1999-12-17    File access    2    59

With binary access it's possible to store information in any way you want, you are not limitied to a fixed record length. This means that you have to know how the information is stored in a binary file to retrieve it again. Binary access files uses less diskspace by allowing variable sized records. You can also read and write information at the same time like a random access file.

In a binary file each record can be of variable size. This is achieved by using an user-defined data type. User defined data types are defined in a the declaration section of a module (at the beginning of a module) like this:

Type MyBinaryRecordInfo
    ID As String ' variable length
    LastName As String ' variable length
    FirstName As String ' variable length
    BirthDate As Date ' fixed length
End Type
To open a file for binary access from VBA you use the Open statement:
Open filename For Binary As filenumber
It's not necessary to specify the buffersize/recordlength, it's ignored with binary files.
You must Close all files you Open before reopening the files for another type of operation.

Since records with binary access can be of variable length, it is necessary to store information about the size of each field in the record so that the information can be read later. A good way to do this is to store an integer with each string to indicate the length of the string. The macros below shows how to create a new binary file using records of the userdefined type MyBinaryRecordInfo defined above:

Sub WriteBinaryFile()
Dim MyRecord As MyBinaryRecordInfo, MyBinaryFile As String, f As Integer, i As Integer
    MyBinaryFile = "C:\FolderName\BinaryFile.dat"
    If Len(Dir(MyBinaryFile)) > 0 Then
        ' deletes the file if it exists
        Kill MyBinaryFile
    End If
    f = FreeFile ' next free filenumber
    ' create the new file
    Open MyBinaryFile For Binary As #f
    ' write records to the binary file
    For i = 1 To 100
        With MyRecord
            .ID = RandomLengthString(10, 3) ' just a dummy
            .LastName = "LastName" & i
            .FirstName = "FirstName" & i
            .BirthDate = Date - i
        End With
        WriteBinaryRecord MyRecord, f ' save the record
    Next i
    Close #f ' close the file
End Sub

Sub WriteBinaryRecord(MyRecord As MyBinaryRecordInfo, f As Integer)
' writes the content of MyRecord to the binary file
Dim intSize As Integer
    With MyRecord
        intSize = Len(.ID) ' determine the length of the variable
        Put f, , intSize ' write the length of the variable
        Put f, , .ID ' write the variable
        
        intSize = Len(.LastName) ' determine the length of the variable
        Put f, , intSize ' write the length of the variable
        Put f, , .LastName ' write the variable
        
        intSize = Len(.FirstName) ' determine the length of the variable
        Put f, , intSize ' write the length of the variable
        Put f, , .FirstName ' write the variable
        
        Put f, , .BirthDate ' write the variable (fixed length variable)
    End With
End Sub

Private Function RandomLengthString(Optional lngMax As Long = 100, Optional lngMin As Long = 1) As String
Dim i As Long, j As Long, c As Integer, strResult As String
' returns a random string of random length, the function has no practical use outside this example
    Randomize
    j = Int(Rnd * (lngMax - lngMin + 1) + lngMin)
    strResult = vbNullString
    For i = 1 To j
        c = Int(Rnd * (122 - 97 + 1) + 97)
        strResult = strResult & Chr(c)
    Next i
    RandomLengthString = strResult
End Function
From the example macros above you can see that a binary access file requires more code to handle the read and write operations than a random access file that can read and write an entire record using a single function call.

To read information from a binary file you use the Get statement.
When you use Get with a variable-length string, the number of bytes read from the file equals the current length of the string variable.
To set the length of a variable-length string you can use the String$ function.
The String$ function can set the variable equal to a specific number of spaces (or other characters).
The macros below shows how you can read information from a binary file:

Sub ReadBinaryFile()
Dim MyRecord As MyBinaryRecordInfo, MyBinaryFile As String, f As Integer, i As Long
    MyBinaryFile = "C:\FolderName\BinaryFile.dat"
    f = FreeFile ' next free filenumber
    ' open the binary file
    Open MyBinaryFile For Binary As #f
    ' read records from the binary file
    i = 0
    Do While Loc(f) < LOF(f)
        i = i + 1
        ReadBinaryRecord MyRecord, f
        ' do something with the input
        With MyRecord
            Debug.Print "Record #" & i, .ID, .LastName, .FirstName, .BirthDate
        End With
    Loop
    Close #f ' close the file
End Sub

Sub ReadBinaryRecord(MyRecord As MyBinaryRecordInfo, f As Integer)
' reads the next record from an open binary file
Dim intSize As Integer
    With MyRecord
        Get f, , intSize ' read the size of the ID field
        .ID = String(intSize, " ") ' set the variable length
        Get f, , .ID ' read the variable string field
        
        Get f, , intSize ' read the size of the LastName field
        .LastName = String(intSize, " ") ' set the variable length
        Get f, , .LastName ' read the variable string field
        
        Get f, , intSize ' read the size of the FirstName field
        .FirstName = String(intSize, " ") ' set the variable length
        Get f, , .FirstName ' read the variable string field
        
        Get f, , .BirthDate ' read the BirtDate field (fixed length variable)
    End With
End Sub


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.

OPE | 2012-05-15 21:48:23 (GMT)

I'm no expert on how to index information and keeping this indexing information updated, so I can't give you much advice about this.

What I do know is that if the contents of your huge file is sorted in ascending order you can probably use the binary search algorithm approach to quickly search your file and retrieve information. Using binary search is normally significantly faster than linear search when you have a big data source to search. Linear search would sometimes be faster when the items you are looking for is at the start of your file.
Wikipedia has more information about the binary search algorithm.

The only problem I can see is that your file is probably a normal text file with pipe delimited data, in other words a text file containing character separated values where each line contains one record. This type of file is normally just suitable for sequential file access (reading line by line).
To be able to use the binary search approach your data should be stored in a format where the contents can be accessed in random order. To achieve this you probably have to make some procedure that converts your sequential text file into a random access format file where you can access the file content in random order and go directly to a specific record instead of reading the file content line by line.

David Powell | 2012-05-15 20:23:55 (GMT)

is it possible to Index a field using this method or even possible ?
I have possibly 3mil records in a Pipe delimited text file...4 columns. and then load the data into a listbox on a useform after applying a filter criterio...Example I'm looking for 2x2434 in Column1 of text file ?

Good information !