[RESOLVED] Read A Specific Line From Big Text File

[Lauriux1]

How can I read specific line from big *.TXT file?
For example *.TXT file size is 200MB and I want read specific line from this file how can I do that ?

[RhinoBull]

This won't be most effcient way but it may work for you:

Private Sub Command1_Click()
Dim sText As String
Dim arLines() As String '<<< declare array at the form level if you need to keep it

    Open App.Path & "\sample.txt" For Input As #1
        sText = Input(LOF(1), #1)
    Close #1
    
    arLines() = Split(sText, vbNewLine)
    sText = ""
    
    Debug.Print arLines(5)
    
    'erase array if you don't need it
    '''Erase arLines()

End Sub

edit:
NOTE: if line number isn't available then you need to loop through file (or array) to get a match on specific text.

Private Sub Command1_Click()
Dim sText As String
Dim arLines() As String '<<< declare array at the form level if you need to keep it
Dim arResults() As String
Dim i As Long

    Open App.Path & "\sample.txt" For Input As #1
        sText = Input(LOF(1), #1)
    Close #1
    
    arLines() = Split(sText, vbNewLine)
    sText = ""
    
    arResults() = Filter(arLines(), "text to search", True, vbTextCompare)
    For i = 0 To UBound(arResults)
        Debug.Print arResults(i)
    Next i
    
    'erase both arrays if necessary
    '''Erase arLines()
    '''Erase arResults()

End Sub

[LaVolpe]

And where is this line, do you know what line number? Is the text file formatted in a specific way that each line is x nr of characters? More specific details may help. Otherwise, you may have to read line by line until you find it.

[westconn1]

thnks for that rhino, i didn't know about filtering arrays, always something to learn

[Milk]

This won't be most effcient way but it may work for you...

The Filter function is new to me too so nice one, but if the text file is 200MB won't the peak ram use be over 800MB! (arLines() = Split(sText, vbNewLine))

[RhinoBull]

...but if the text file is 200MB won't the peak ram use be over 800MB! (arLines() = Split(sText, vbNewLine))

If you have a better solution you're welcome to post it.

[LaVolpe]

If you have a better solution you're welcome to post it.

If memory is an issue, a Do Loop, using Line Input til EOF or line found would work too.

[RhinoBull]

thnks for that rhino, i didn't know about filtering arrays, always something to learn

You're welcome. There is a thread in FAQ I created some time ago - link is in my signature.

[RhinoBull]

If memory is an issue, a Do Loop, using Line Input til EOF or line found would work too.

But loop will eat up your cpu so I'm not sure what's better (or worst for that matter) and formula is quite simple:

VB6 + large files <> Efficiency

[Milk]

Okay so this is not so pretty, but it's very fast...
Edit: Just a minor tweak, the buffer size has been reduced to &H10000 (64KB), and is now passed as an optional argument. Testing on my PC showed that that size is optimal. LineNumber is passed Byref and returns the file position of the line. With a 50MB text file, searching for the 337'227th (last) line, the function was returning in around 250 milliseconds.

Private Function GetSpecificLine(filename As String, ByRef LineNumber As Long, Optional BufferSize As Long = &H10000) As String
Dim FF As Integer, Buf() As Byte, LnCt As Long, Pos As Long, i As Long, SLn As Long, ELn As Long
'Linenumber is passed Byref and returns the file position of the line

    If BufferSize < 1 Then Exit Function
    ReDim Buf(BufferSize - 1)
    LnCt = 1
    Pos = 1
    LineNumber = Abs(LineNumber) - 1
    If LineNumber < 1 Then SLn = 1
    
    On Error GoTo EH
    FF = FreeFile
        Open filename For Binary As #FF
            For Pos = 1 To LOF(FF) Step BufferSize
                Get #FF, Pos, Buf
                For i = 0 To BufferSize - 1
                    If Buf(i) = 13 Then
                        LnCt = LnCt + 1
                        If LnCt > LineNumber Then
                            If SLn Then
                                ELn = Pos + i - 1
                                Exit For
                            Else
                                SLn = Pos + i + 2
                            End If
                        End If
                    End If
                Next i
                If i <> BufferSize Then Exit For 'the line has been found
            Next Pos
            If SLn Then
                LineNumber = SLn
                If ELn = 0 Then ELn = LOF(FF)
                ReDim Buf(ELn - SLn)
                Get #FF, SLn, Buf
                GetSpecificLine = StrConv(Buf, vbUnicode)
            End If
        Close #FF
    Exit Function
EH:
 'tell user of file error here
End Function

[Lauriux1]

WOW! Thanks for all
Nice code Milk and realy work very fast!

But can you explain me more about "BufferSize As Long=65536"
([filename As String,LineNumber As Long,BufferSize As Long=65536])
What optimal BufferSize I have to choose?

[leinad31]

Another option is to import the data into a database.

[luca90]

Okay so this is not so pretty, but it's very fast...
Edit: Just a minor tweak, the buffer size has been reduced to &H10000 (64KB), and is now passed as an optional argument. Testing on my PC showed that that size is optimal. LineNumber is passed Byref and returns the file position of the line. With a 50MB text file, searching for the 337'227th (last) line, the function was returning in around 250 milliseconds.

Private Function GetSpecificLine(filename As String, ByRef LineNumber As Long, Optional BufferSize As Long = &H10000) As String
Dim FF As Integer, Buf() As Byte, LnCt As Long, Pos As Long, i As Long, SLn As Long, ELn As Long
'Linenumber is passed Byref and returns the file position of the line

    If BufferSize < 1 Then Exit Function
    ReDim Buf(BufferSize - 1)
    LnCt = 1
    Pos = 1
    LineNumber = Abs(LineNumber) - 1
    If LineNumber < 1 Then SLn = 1
    
    On Error GoTo EH
    FF = FreeFile
        Open filename For Binary As #FF
            For Pos = 1 To LOF(FF) Step BufferSize
                Get #FF, Pos, Buf
                For i = 0 To BufferSize - 1
                    If Buf(i) = 13 Then
                        LnCt = LnCt + 1
                        If LnCt > LineNumber Then
                            If SLn Then
                                ELn = Pos + i - 1
                                Exit For
                            Else
                                SLn = Pos + i + 2
                            End If
                        End If
                    End If
                Next i
                If i <> BufferSize Then Exit For 'the line has been found
            Next Pos
            If SLn Then
                LineNumber = SLn
                If ELn = 0 Then ELn = LOF(FF)
                ReDim Buf(ELn - SLn)
                Get #FF, SLn, Buf
                GetSpecificLine = StrConv(Buf, vbUnicode)
            End If
        Close #FF
    Exit Function
EH:
 'tell user of file error here
End Function

hI Milk ...
Sorry if i post on old question.
Admit my line is the number 457, how to loop the rest of txt file to the end, from this line?

note:
Tested the code, is a lightning and work perfect!

[Arnoutdv]

Very basic sample:

  Dim fID As Integer
  Dim sLine As String
  Dim lLineCnt As Long
  
  fID = FreeFile
  Open "PathToYourFile" For Input As #fID
  Do Until EOF(fID)
    Line Input #fID, sLine
    lLineCnt = lLineCnt + 1
    If lLineCnt >= 457 Then
      ' Process the data
    End If
  Loop
  Close #fID