How to read the last line in a large textfile

Hello all,

I have a large textfile (up to 20 000 000 rows), how can I read the last line fastest?

KalleK

The suggestion in your other thread (ADO) is probably the easiest, but if the file contains fixed length records (All lines exactly the same size) then there is another possibility... Is it?

Hello cytop, no it is not fixed length. It is csv file with number and date and time. How do I use ADO to read?

The following should return the details from the last record in a CSV/TXT file. Minor changes are needed if another delimiter is used and it assumes the first line contains headings (although that won't really matter as you only want the last line.

It requires a reference to Microsoft ActiveX Data Objects (Tested with 6.1)

Option Explicit

Sub GetTextFileData(strSQL As String, strFolder As String)
    ' example: GetTextFileData "SELECT * FROM filename.txt", _
              "C:\FolderName", Range("A3")
    ' example: GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _
              "C:\FolderName", Range("A3")
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer

    Set cn = New ADODB.Connection
    
    On Error Resume Next
    cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=" & strFolder & ";" & _
        "Extensions=asc,csv,tab,txt;"
    On Error GoTo 0
    If cn.State <> adStateOpen Then Exit Sub
    
    Set rs = New ADODB.Recordset
    On Error Resume Next
    rs.Open strSQL, cn, adOpenKeyset, adLockReadOnly, adCmdText
    On Error GoTo 0
    If rs.State <> adStateOpen Then
        cn.Close
        Set cn = Nothing
        Exit Sub
    End If
       
   '// This may take a few seconds to execute - or more than a 'few' for 20000000 recs
   rs.MoveLast
   MsgBox "The First 3 fields of the last record are:" & vbCrLf & vbCrLf & rs.Fields(0).Value & vbCrLf & rs.Fields(1).Value & vbCrLf & rs.Fields(2).Value, vbInformation
   
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    
End Sub


Sub TestGetText()
    
    '// Note the [ & ]
    GetTextFileData "SELECT * FROM [FileName.csv]", "c:\full\path\to\file"
    '// GetTextFileData "SELECT * FROM [uk500.csv]", "C:\Users\Fred\Documents"

End Sub

Thanks! I will test and compare the times. Is is posibble with ADO to start to read from a line? Say line 40000 of a file with 2 000 000 lines

You can use the MOVE method, or one of it's variants.

rs.MoveLast  '// Fairly obvious 
rs.MoveFirst
rs.Move 50    '// To skip 50 records.
rs.Move -50  '// To skip back 50 records.

In all cases I am ignoring the fact you might try to move before the first or after the last record - this will raise an error so you need to include error handling.

IF you only want to pick particular records - for example where a 'NAME' column contains 'Smith', you can specify that in the initial selection:

    GetTextFileData "SELECT * FROM [FileName.csv] Where [NAME] = " & Chr(34) & "Smith" & Chr(34)", "c:\full\path\to\file"

You can then move between those records matching 'SMITH' but it's beginning to get a little complicated now.

Thanks cytop! I get it to work and see if it is faster then my curret solution to find the end of the file. Thanks again for the help!