Copy bottom rows from large text file in Excel VBA

Question

I have scoured the interwebs looking for a VBA solution to the problem below to no avail. My text files range in the 3-5 million lines (this varies from text file to text file) of data but I only need to extract the last 12 lines of actual data and put into an Excel sheet, preferably delimited by spaces and tabs. Obviously, I cannot load the text file straight into Excel with a million or so row limit and using DataTypes.Add does not favor any better.

Is there a way to import these last few lines of data to Excel in a delimited fashion? My data looks something like this

From text file:

Blah blah blah stuff I don't need for 5 million lines

Line5550203:

Line5550204: This is the title of this section of data

Line5550205:

Line5550206: Title of data 1 Title of Data 2 Title of data 3

Line5550207:

Line5550208: Units of data 1 Units of Data 2 Units of data 3

Line5550209:

Line5550210: Data1 Data2 Data 3

Line5550211:

Line5550212: Title of next section of data

Line5550213:

Line5550214: Title of Data 1a Title of Data 2a Title of Data 3a

Line5550215:

Line5550216: Units of Data 1a Units of Data 2a Units of Data 3a

Line5550217:

Line5550218: Data 1a Data 2a Data 3a

End of file

Obviously, I don't want to import the empty lines, just the data. The titles would be optional and I don't need the units. Any help or pointing me in the right direction would be appreciated. Thank you in advance!

And would it be ok that the lines are in 12 different cells? — snenson, Feb 26 '21 at 06:17
Can u uploud one of these files? It should be very easy to get them, but the filesize could be a problem. — snenson, Feb 26 '21 at 06:21
I can upload a dummy of it yes. The real data is proprietary. Give me a few minutes. — jackofallvba, Feb 26 '21 at 06:32
https://stackoverflow.com/questions/187587/a-windows-equivalent-of-the-unix-tail-command — Tim Williams, Feb 26 '21 at 06:49
Thank you al for the quick answers! I will try them out tomorrow and let you know what works best! — jackofallvba, Feb 26 '21 at 07:24
Thank you all! I don't care what anyone else says about you, y'all are alright. FunThomas won out for my uses but I appreciate everyone's responses. — jackofallvba, Feb 26 '21 at 16:46

score 2 · Answer 1 · answered Feb 26 '21 at 07:14

Here's one approach:

Sub LastFewLines()
    
    Const BUFFER As Long = 15 'last how many lines
    
    Dim fso As Object, t, n As Long, ln, arr(0 To BUFFER), i As Long
    Set fso = CreateObject("scripting.filesystemobject")
    
    'create the dummy text file
'    Set t = fso.createtextfile("C:\Temp\dummy.txt")
'    For n = 1 To 4000000#
'        t.writeline "This is line " & n
'    Next n
'    t.Close

    Set t = fso.opentextfile("C:\Temp\dummy.txt", 1)
    
    n = 0
    Do While Not t.atendofstream
        ln = t.readline
        arr(n Mod BUFFER) = ln 'store lines, overwriting as we go
        n = n + 1
    Loop
    t.Close

    'read out last lines in correct order
    For i = (n - BUFFER) To n - 1
        Debug.Print arr(i Mod BUFFER)
    Next i
    
End Sub

FunThomas · Accepted Answer · 2021-02-26T07:46:18.823

For speed reasons, we want to avoid reading the whole file only to finally find the last rows.

You can use the seek-function to read from any position of a file. However, as the lines can have different lengths, we don't know the exact position. The following code assumes a maximum line length and calculate how much bytes we need to read at max. Those bytes are read into a buffer and the buffer is split into lines. Now we probably will have to many lines, but it's easy to get only the last n of them:

Sub readfileTail(filename As String, Optional maxLines As Long = 12)

    Const maxLineLen = 256
    
    Dim f As Integer
    f = FreeFile
    Open filename For Binary As #f
    
    Dim fileLen As Long, filePos As Long
    fileLen = LOF(f)        ' Get size of file in bytes.
    filePos = fileLen - (maxLines * maxLineLen)
    
    Dim buffer As String
    Seek #f, filePos
    buffer = Input((maxLines * maxLineLen), #f) ' Read character.
    
    Dim lines() As String, i As Long, startline As Long
    lines = Split(buffer, vbCrLf)
        
    startline = UBound(lines) - maxLines + 1
    If startline < 0 Then startline = 0
    
    For i = startline To UBound(lines)
        Debug.Print i, lines(i)
    Next i
    Close #f
End Sub

Copy bottom rows from large text file in Excel VBA

From text file:

End of file

2 Answers2