How to read the second last line in a text file

Question

I would like to read a large file in VBA and saw this code online:

Dim MyChar As String, Pointer As Long, LastLine As String
Open "MyTextFile.Txt" For Binary As #1
Pointer = LOF(1) - 2
MyChar = Chr$(32)
Do
    Get #1, Pointer, MyChar
    If MyChar = vbCr Or MyChar = vbLf Then
        Exit Do
    Else: Pointer = Pointer - 1
        LastLine = MyChar & LastLine
    End If
Loop
MsgBox "Last Line is " & LastLine

How do I change this code to get the second last line? Need some help on this.

Thought of this:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
    "MyTextFile.Txt", 1)
objTextFile.ReadAll
MsgBox objTextFile.Line

But I can't get to the line-2.

score 1 · Answer 1 · answered Sep 28 '15 at 09:37

The code you're providing works as follows:

It sets a pointer to the last character of the file
It then reads that file backwards until it finds a linebreak
It returns all it has read as last line.

To modify this for your needs, I have added a Boolean secondRun, which lets the code run step 2 again, thus recording the 2nd last line:

Dim MyChar As String, Pointer As Long, LastLine As String
Open "MyTextFile.Txt" For Binary As #1
Pointer = LOF(1) - 2
MyChar = Chr$(32)
Dim secondRun As Boolean
Do
    ' Read character at position "Pointer" into variable "MyChar"
    Get #1, Pointer, MyChar
    If MyChar = vbCr Or MyChar = vbLf Then  ' Linebreak = line read completely
        If Not secondRun Then
            ' Run again if we've read only one line so far
            secondRun = True
            LastLine = ""
            Pointer = Pointer - 2
        Else
            Exit Do
        End If
    Else: Pointer = Pointer - 1
        ' Add character to result String
        LastLine = MyChar & LastLine
    End If
Loop
MsgBox " 2nd last line is " & LastLine

score 1 · Answer 2 · edited May 23 '17 at 12:19

Depends on your approach. But if the files are really that big then you probably don't want Excel to load the entire file. So, you'll probably open the files and read line by line without knowing how big the file is and how many rows it has. In that case it's probably easiest do just store two lines at a time in two separate string variables. As soon as you hit the last row you can exit your loop - as shown above in your code - and output not only the last row (as is already done in your code) but also the content of the second last row in that file.

Public Sub GetSecondLastRow()
Dim strSecondLastLine As String
Dim strFileToImport As String
Dim strLastLine As String
Dim intPointer As Integer
Dim lngCounter As Long

strFileToImport = ThisWorkbook.Path & IIf(InStr(1, ThisWorkbook.Path, "\") > 0, "\", "/") & "MyTextFile.txt"

intPointer = FreeFile()
Open strFileToImport For Input Access Read Lock Read As #intPointer

lngCounter = 0
Do Until EOF(lngCounter)
    strSecondLastLine = strLastLine
    Line Input #intPointer, strLastLine
    lngCounter = lngCounter + 1
Loop

Close intPointer

Debug.Print "Content of the second last row:"
Debug.Print "---------------------------------------"
Debug.Print strSecondLastLine
Debug.Print "---------------------------------------"
Debug.Print "Content of the last row:"
Debug.Print "---------------------------------------"
Debug.Print strLastLine

End Sub

The alternative would be to first query the file for its row count and then get the second last record in that file using ADO. But I doubt that would be faster. The problem with ADO is that you get a huge recordset back containing the entire text file. This is due to the fact that you have no where restriction in the clause SELECT * from MyTextFile.txt. So, the entire text file goes into memory before you can do anything with it. Then - of course - you can check the RecordCount and go again through all records with a cursor fast forward until you hit the second last row. Unfortunately, ADO does not support

row_number() over (order by @@ROWCOUNT).

Otherwise, you could first get the row count with select count(1) from MyTextFile.txt and then afterwards only the applicable row.

So, in any case, I am almost certain (without having tested it) that ADO will perform below par and the first solution is the way to go if the text files are as big as you say. If you still prefer ADO then this is the code for that (based on the following SO question / answer: Copying text from .txt file in Excel using ADO ignores first row).

 Sub ImportTextFile()

'Imports text file into Excel workbook using ADO.
'If the number of records exceeds 65536 then it splits it over more than one sheet.
Dim strFilePath As String, strFilename As String, strFullPath As String
Dim lngCounter As Long
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim oFSObj As Object

'Get a text file name
strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select text file...")

If strFullPath = "False" Then Exit Sub  'User pressed Cancel on the open file dialog

'This gives us a full path name e.g. C:\temp\folder\file.txt
'We need to split this into path and file name
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")

strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path
strFilename = oFSObj.GetFile(strFullPath).Name

'Open an ADO connection to the folder specified
Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & strFilePath & ";" & _
               "Extended Properties=""text;HDR=No;FMT=Delimited"""

Set oRS = New ADODB.Recordset
'Now actually open the text file and import into Excel
oRS.Open "SELECT count(1) FROM [" & strFilename & "]", oConn, 3, 1, 1

Range("A1").CopyFromRecordset oRS

Set oRS = New ADODB.Recordset
'Now actually open the text file and import into Excel
oRS.Open "SELECT * FROM [" & strFilename & "]", oConn, 3, 1, 1

While Not oRS.EOF And Not oRS.BOF
    If oRS.AbsolutePosition = Range("A1").Value2 Then
        Range("A2").Value = oRS.Fields(0).Value
    End If
    oRS.MoveNext
Wend

oRS.Close
oConn.Close

End Sub

I prefer the second method. How do you get the file using ADO? can explain further? — lakshmen, Sep 28 '15 at 11:01
Just updated my answer to explain, why ADO is not really worth looking into. But if you still want to go down that route then simply copy from any of these: http://stackoverflow.com/questions/16898046/copying-text-from-txt-file-in-excel-using-ado-ignores-first-row http://stackoverflow.com/questions/22947425/using-ado-to-query-text-files-terrible-performance The steps are the same: (1) `SELECT * from MyTextFile.txt` and then save always two strings and cycle to the end of the `recordset`. Once you're at the end you can output the last two rows. — Ralph, Sep 28 '15 at 11:57

score 0 · Answer 3 · answered Sep 28 '15 at 09:44

0

You can try this:

Public Function GetSecondLastLine(sFileName As String, Optional sLineDelimiter As String = vbCrLf) As String

    Dim sContent    As String
    Dim aLines()    As String

    sContent = TextFromFile(sFileName)

    aLines = Split(sContent, sLineDelimiter)

    GetSecondLastLine = aLines(UBound(aLines) - 1)

End Function

Public Function TextFromFile(sFileName As String) As String

    Dim lFile As Long

    lFile = FreeFile
    Open sFileName For Input As #lFile
    TextFromFile = Input$(LOF(lFile), lFile)
    Close #lFile

End Function

If necessary, you can change the line delimiter (e.g. vbCR of vbLF)

answered Sep 28 '15 at 09:44

Bas Verlaat

842
6
8

the file is very big. so it gives me out of memory – lakshmen Sep 28 '15 at 10:24
In that case, you can make a buffer first by extracting the last e.g. 10000 characters as a string and use that as sContent in GetSecondLastLine – Bas Verlaat Sep 28 '15 at 10:44

Rsge · Answer 4 · 2023-03-09T07:17:24.077

"True to request" version

Improved on Verzweifler's answer, imho:

Linux compatible (Linebreaks with LF only instead of CR LF possible)
Accounting for multiple open files
Using an empty, fixed-length defined string as char (no assignment needed)

Public Function GetSecondLastLine(ByVal filePath As String) As String
    ' Variables
    Dim fileNumber As Long
    ' Use first unused file number.
    fileNumber = FreeFile
    Dim pointer As Long
    ' String of fixed length 1.
    Dim char As String * 1
    Dim secondLastLine As String
    Dim secondRun As Boolean
    
    ' Read last two lines of file.
    Open filePath For Binary As fileNumber
    ' Set pointer to last file position.
    pointer = LOF(fileNumber)
    Do
        ' Read char at position "pointer" into "char".
        Get fileNumber, pointer, char
        If char = vbCr Then
            ' Just skip CRs for Linux compat.
            pointer = pointer - 1
        ElseIf char = vbLf Then
            If Not secondRun Then
                secondRun = True
                secondLastLine = vbNullString
                pointer = pointer - 1
            Else
                Exit Do
            End If
        Else
            pointer = pointer - 1
            ' Add char to result String.
            secondLastLine = char & secondLastLine
        End If
    Loop
    Close fileNumber
    GetSecondToLastLine = secondLastLine
End Function

MsgBox " 2nd last line is " & GetSecondLastLine("MyTextFile.txt")

Extended version

Gets lineCount number of last lines

Public Function GetLastLines(ByVal filePath As String, Optional ByVal lineCount As Long = 1) As String()
    Dim fileNumber As Long
    ' Use first unused file number.
    fileNumber = FreeFile
    Dim pointer As Long
    ' String of fixed length 1.
    Dim char As String * 1
    Dim currentLineNumber As Long
    currentLineNumber = 0
    Dim lastLines() As String
    ReDim lastLines(0 To lineCount - 1)

    ' Open file.
    Open filePath For Binary As fileNumber
    ' Set pointer to last file position.
    pointer = LOF(fileNumber)
    Do
        ' Read char at position "pointer" into "char".
        Get fileNumber, pointer, char
        If char = vbCr Then
            ' Just skip CRs for Linux compat.
            pointer = pointer - 1
        ElseIf char = vbLf Then
            ' Read count last lines of file.
            If currentLineNumber < lineCount - 1 Then
                currentLineNumber = currentLineNumber + 1
                pointer = pointer - 1
            Else
                Exit Do
            End If
        Else
            pointer = pointer - 1
            ' Add char to result string.
            lastLines(currentLineNumber) = char & lastLines(currentLineNumber)
        End If
    Loop
    Close fileNumber
    GetLastLines = lastLines
End Function

Dim line As Variant
For Each line In GetLastLines("MyTextFile.txt", 2)
    Debug.Print line
Next

How to read the second last line in a text file

4 Answers4

"True to request" version

Extended version