[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

Is there a better way to search for a particular record in a binary file?

Is there a better way to look for a particular record in a binary file aside from looping through each record?  If the # of records is very large and the particular record is near the end of the file, it wil take a lot of time doing the search.  I just need the record number so that  on the next access I can go to the record right away.

Example code below.

Option Explicit

Private Type Header
    Name            As String * 14
    NotUsed         As Integer
    NotUsed1        As Long
    Reserve         As String * 32
End Type

Private Type Record
    Date            As Integer
    Time            As Integer
    A               As Single
    B               As Single
    C               As Single
    D               As Single
    E               As Long
End Type

Private fileName As String

Private Sub Form_Load()
    fileName = "c:\someFile.txt"
End Sub

Private Sub Command1_Click()

    ' create a test file with header and five records
   
    Dim h As Header
    Dim r As Record
    Dim i As Integer
   
    h.Name = "Mike"
   
    Open fileName For Binary As #1
        Put #1, , h
       
        For i = 1 To 5
            r.Date = i
            r.A = i
            r.B = i
            r.C = i
            r.D = i
            r.E = i
            r.Time = i
            Put #1, , r
        Next i
    Close #1
End Sub

Private Sub Command2_Click()
    Dim h As Header
    Dim r1 As Record
    Dim i As Integer
   
    Open fileName For Binary As #1
    Get #1, , h
    i = 0

    Do While Not EOF(1)
        i = i + 1
        Get #1, , r1
        If r1.A = 4 Then
        Call MsgBox("A=4 is in record " & i)
        Exit Do
        End If
    Loop

    Close #1
 
End Sub
0
larrysy
Asked:
larrysy
2 Solutions
 
dbrckoviCommented:
Hi!

You have to open the file "For Random" access mode in both cases: when saving and when loading.
Then you can specify number of the Record which you need in    Get #1, RecNumber, Record      statement.
For example:

'---------------------------------------------------------------
Private Sub Command1_Click()

    ' create a test file with header and five records
   
    Dim h As Header
    Dim r As Record
    Dim i As Integer
   
    h.Name = "Mike"
   
    Open fileName For Random As #1
        Put #1, , h
       
        For i = 1 To 5
            r.Date = i
            r.A = i
            r.B = i
            r.C = i
            r.D = i
            r.E = i
            r.Time = i
            Put #1, , r
        Next i
    Close #1
End Sub

Private Sub Command2_Click()
    Dim h As Header
    Dim r1 As Record
    Dim i As Integer
   
    Open fileName For Random As #1
    Get #1, 1, h                                        'get first record
    Get #1, 5, r1                                       'get fifth record

    Print h.Name
    Print r1.A
       
    Close #1
End Sub
'----------------------------------------------------------------------
0
 
dbrckoviCommented:
Random file access mode is designed for working wirh records. While binary is designed for working with raw binary data.
File is saved differently and with more additional data when u use Random.
For example, file in your case is 172 bytes long when you use Binary, and 664 bytes when you use Random.

I'm not sure but I assume that in files created using Random access mode, application saves some kind of index or table of contents, so Get statement
can jump directly to specific record number, without looping through file.
0
 
sgayatriCommented:
1. Going to a record directly in a text/binary file is not possible.
2. It becomes possible only by maintaining data in databases.
3. Even if you have the record number, you cannot directly jump to the particular record if you are maintaining a text file .
4. I am not very sure about what dbrckovi has written. You have to loop thru the file till you get your particular record.

Try to post a sample/format of your binary file.
Write also, how and when you append your file
and how you read your file....
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dbrckoviCommented:
sgayatri:

>> I am not very sure about what dbrckovi has written. You have to loop thru the file till you get your particular record.

When you use Random access mode, you don't have to loop. You can read any record from any position at any time.

Try this example: It reads first, then 12564'th, then 1000'th second record without looping.
'-------------------------------------------------
Option Explicit

Private Type Header
    Name            As String * 14
    NotUsed         As Integer
    NotUsed1        As Long
    Reserve         As String * 32
End Type

Private Type Record
    Date            As Integer
    Time            As Integer
    A               As Single
    B               As Single
    C               As Single
    D               As Single
    E               As Long
End Type

Private Sub Command1_Click()

    ' create a test file with header and five records
   
    Dim h As Header
    Dim r As Record
    Dim i As Integer
   
    h.Name = "Mike"
   
    Open "c:\test.txt" For Random As #1
        Put #1, , h
       
        For i = 1 To 30000
            r.Date = i
            r.A = i
            r.B = i
            r.C = i
            r.D = i
            r.E = i
            r.Time = i
            Put #1, , r
        Next i
    Close #1
    MsgBox "Done!"
End Sub

Private Sub Command2_Click()
    Dim h As Header
    Dim r1 As Record
    Dim i As Integer
   
    Open "c:\test.txt" For Random As #1
    Get #1, 1, h                                        'get first record
   
    Get #1, 12564, r1                                       'get 12564'th record
    Print r1.A
    Get #1, 1000, r1                                       'get 1000'th record
    Print r1.A
       
    Close #1
End Sub
'-----------------------------------------------------------------
0
 
larrysyAuthor Commented:
I have to add another condition.  I have no control over the creation of the binary file.  The Command 1 is just to create an example for use in Command 2.  In actual use, I'll just be doing the Command 2 function to an existing file.  But I'll test if I can use the methods suggested.  Thanks.
0
 
dbrckoviCommented:
(1000'th second record without looping.        should be         1000'th record without looping)

Going directly to Record number using text/binary access mode is also possible, but then you have to specify exactly at which byte is the beginning of your record.
To know that, you would have to know length of each record (in bytes), calculate the position of the record which you need and use Get #1,PositionInBytes,r1.

But this is not required when you use Random Access mode becouse in this case all calculations and indexing is done automaticaly.
0
 
dbrckoviCommented:
larrysy:

I just want to make sure there is no confusion or missunderstanding.

Are you trying to open a record under specific number, or are you trying to find and open a record based on the value it contains?

In first case all you have to know is record number which you need and specify it in Get statement, but if you are trying to search through records
and open the one which contains some value, then you'll either have to loop through all of them or you'll have to use some kind of a database like
sgayatri suggested.

If you woud have some control on how is the file created, then you might create some Index record which would be saved at the end or the beginning of the.
It could contain values which you need and record number which contains them. This way you wouldn't have to loop through the whole file.
0
 
larrysyAuthor Commented:
dbrckovi

1. I have no control in the creation of the original file (so its a plain binary file in the format of the file created in Command 1)
2. The file contains dates in the first column
3. There are variable number of records for each date ( so cannot extrapolate from the first record)
4. I need to extract the records from a date range to another file, so I have to look for the record with the first instance of the start date and start processing

I can go to a specific record using the Seek function,  so I can test the record at 50% of the file length, then move to the next one in the center of the 50% range and so on until I come to within a few record of the start date.  However, can I specify the starting point of a Do Loop?    Havent tried this yet, but just came out of my head.

Thanks
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Assuming the Dates are already sorted in your file, that is exactly what you need to do.  This would be the equivalent of a binary search.  Don't forget to take into account the different size of the header when calculating the position of the record halfway through the file.

Calculate the byte position in the file and then seek to it.  If you omit the record number in the Get statement, the record at the current position is read:

    Get #1, , r1 ' read record at current position (which is wherever you just Seeked to)

You won't need to change your Do Loop.  Once you have found the right position to start reading using your Seek statements, just start reading records until you have found a date outside your range or the end of the file is reached:

    Do While Not EOF(1)
        Get #1, , r1
        If r1.Date > SomeDateValue Then
             Exit Do
        End If
    Loop

~IM
0
 
larrysyAuthor Commented:
Hi idle_mind,

Thanks again.  This is my solution based on your ideas (and your previous soultion).  I can get to the Target Value really fast.

Private Sub getStart()
Dim H As Header
Dim r1 As Record
Dim GotIt As Boolean
Dim i As Long
GotIt = False
TargetValue = some value

    factor = 2
    nextRecPos = Len(H) + 1 + ((Round(numRecs / factor, 0) - 1) * Len(r1))
 
    Open binfile For Binary As #1
 
    Get #1, , H

    Do
    i = i + 1
    Debug.Print i
   
    Seek #1, nextRecPos
    Get #1, , r1
   
    With r1

    Select Case .A
    Case Is < TargetValue
    factor = factor * 2
    nextRecPos = nextRecPos + ((Round(numRecs / factor - 1, 0) - 1) * Len(r1))
    Case Is > TargetValue
    factor = factor * 2
    nextRecPos = nextRecPos - ((Round(numRecs / factor - 1, 0) - 1) * Len(r1))
    Case Is = TargetValue
    GotIt = True
    End Select
    End With
   Loop Until GotIt = True
   Close #1
End Sub

Private Sub Process()

 Seek #1, nextRecPos
 Do until EOF(1)

Loop

End Sub

(I'll have to give some points to dbrckovi)
0
 
larrysyAuthor Commented:
Ooops, I got the split points wrong - the accepted answer should be to idle_mind.
0
 
larrysyAuthor Commented:
Thanks, it's now corrected.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now