Reading large text file (250MB++) line by line in VBA

Question

Inspired by the post "Superfast way to read large files line-by-line in VBA. Please critique", I'm experimenting the three methods. But the "superfast" method using Get cannot handle files larger than 250MB, because the Space(myFileLen) is subject to the max length of String in VBA. Based on my test, the max length of a string in VBA is 263,749,613, which is roughly 251 MB. And the other two methods are really running slow. Currenly I guess I could only go for the Line Input method. So, dear friends, could you advise how to handle such a big file in VBA? Thanks!

Answer 1

In Access I usually attached a file as a linked table then use SQL to process the rows into another table.

What are you trying to do? How large is your file. Are you looking to load data into excel, access or what?

If you are using excel you might consider loading the file into the new PowerPivot data model as this is designed to compress data and give superfast aggregations as it is a columnar database. You can use DAX.

H

Answer 2

I risk being downvoted for this if it is wrong but wanted to throw it out there anyway.

I notice there doesn't seem to be much posted about using ADO for this, is it considered slow or bad practice?

Sub TestRead()
    MsgBox ReadTextFile("C:\Testdir\", "TEST.txt", 2)
End Sub

Function ReadTextFile(Path As String, MyFile As String, RowNum As Long)
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim adcomm As New ADODB.Command
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & ";Extended Properties='text;HDR=YES;FMT=Delimited'"
    rs.Open "Select * From " & MyFile, conn, adOpenStatic, adLockReadOnly, adCmdText
    rs.AbsolutePosition = RowNum
    If rs.EOF Then
        ReadTextFile = "Row " & RowNum & " is higher than the rowcount of the data"
    Else
        ReadTextFile = rs(0)
    End If
End Function

test.txt file:

My Header
This text file is for testing ADO reading
This is a rather long line of text in comparison to the other lines of text in this text file
Short line

Schema.ini file:

[TEST.txt]
Format=FixedLength

Col1=Column1 Text Width 999