Read large file line-by-line with ADO Stream?

Question

I want to use ADO Stream to read lines from a local large text file with UTF-8 encoding so I try

Set objStream = CreateObject("ADODB.Stream")
objStream.Charset = "utf-8"
objStream.Type = 2
objStream.Open
objStream.LoadFromFile = strFile
objStream.LineSeparator = 10
Do Until objStream.EOS
    strLine = objStream.ReadText(-2)
Loop

However the result is that the script takes lots of RAM and CPU usages. So is there any way to tell the script not to load all the file contents into memory, but just open it and read until it encounters any line separator?

Answer 1

As you work with Stream object, I think it's obvious, however, .LoadFromFile fill current stream with the whole file content, and no any cutomize option to load parial data from file.

As for reading 1 line, you done this already with .ReadText(-2), (-2 = adReadLine).

Set objStream = CreateObject("ADODB.Stream")
objStream.Charset = "utf-8"
objStream.Type = 2
objStream.Open
'objStream.LoadFromFile = strFile ''I see a typo here
objStream.LoadFromFile strFile
objStream.LineSeparator = 10      ''that's Ok
'Do Until objStream.EOS           ''no need this
    strLine = objStream.ReadText(-2)
'Loop
objStream.Close ''add this though!

[EDIT] Well, for .LineSeparator you can use just 3 constants:

Constant Value Description

adCRLF   -1    Default. Carriage return line feed 
adLF     10    Line feed only 
adCR     13    Carriage return only 

If you need to break your Do..Loop at other letter, as .ReadText is the only choice for reading text stream, you may use it in conjunction with InStr function and Exit Do then you find your custom separator.

Const cSeparator = "_" 'your custom separator
Dim strLine, strTotal, index
Do Until objStream.EOS
    strLine = objStream.ReadText(-2)
    index = InStr(1, strLine, cSeparator)
    If index <> 0 Then
        strTotal = strTotal & Left(strLine, index-1)
        Exit Do
    Else
        strTotal = strTotal & strLine
    End If
Loop

Shortly, this is the whole optimization you can do (or at least as far as I know).