Databases
StackOverflow2013
Postsdbo
POWhat is a superfast way to read large files line-by-line in VBA?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat is a superfast way to read large files line-by-line in VBA?
    text
    copied!<p>I believe I have come up with a very efficient way to read very, very large files line-by-line. Please tell me if you know of a better/faster way or see room for improvement. I am trying to get better at coding, so any sort of advice you have would be nice. Hopefully this is something that other people might find useful, too.</p> <p>It appears to be something like 8 times faster than using Line Input from my tests.</p> <pre><code>'This function reads a file into a string. ' 'I found this in the book Programming Excel with VBA and .NET. ' Public Function QuickRead(FName As String) As String Dim I As Integer Dim res As String Dim l As Long I = FreeFile l = FileLen(FName) res = Space(l) Open FName For Binary Access Read As #I Get #I, , res Close I QuickRead = res End Function 'This function works like the Line Input statement' Public Sub QRLineInput( _ ByRef strFileData As String, _ ByRef lngFilePosition As Long, _ ByRef strOutputString, _ ByRef blnEOF As Boolean _ ) On Error GoTo LastLine strOutputString = Mid$(strFileData, lngFilePosition, _ InStr(lngFilePosition, strFileData, vbNewLine) - lngFilePosition) lngFilePosition = InStr(lngFilePosition, strFileData, vbNewLine) + 2 Exit Sub LastLine: blnEOF = True End Sub Sub Test() Dim strFilePathName As String: strFilePathName = "C:\Fld\File.txt" Dim strFile As String Dim lngPos As Long Dim blnEOF As Boolean Dim strFileLine As String strFile = QuickRead(strFilePathName) &amp; vbNewLine lngPos = 1 Do Until blnEOF Call QRLineInput(strFile, lngPos, strFileLine, blnEOF) Loop End Sub </code></pre> <p>Thanks for the advice!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload