Read the large file line by line with the ADO stream?

Question

I want to use ADO Stream to read lines from a local large text file encoded in UTF-8, so I'm trying

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 consumes a lot of RAM and CPU. So, is there a way to tell the script not to load the entire contents of the file into memory, but simply open it and read it until it encounters some kind of line separator?

Answer 1

When you work with a Stream object, I think it is obvious that .LoadFromFile fills the current stream with the full contents of the file and does not have any cutomize option to load parial data from the file.

As for reading 1 line, you already did this 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 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 only 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 split Do..Loop into another letter, since .ReadText is the only choice for reading a text stream, you can use it in combination with the InStr and Exit Do functions, after which you will 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 

In short, this is all the optimization you can do (or at least as far as I know).

Answer 2

If you look at this fragment from the book JT Roff ADO , you will see that theoretically you can read from a file line by line (without fully loading into memory). I tried using the: protocoll file in the original parameter, but failed.

So try a different approach. To process the .txt file as an ADO database table encoded in UTF8 (single column), you need the schema.ini file in the source directory:

 [linesutf8.txt] ColNameHeader=False CharacterSet=65001 Format=TabDelimited Col1=SampleText CHAR WIDTH 100 

Then you can do:

  Dim sTDir : sTDir = "M:/lib/kurs0705/testdata" Dim sFName : sFName = "[linesutf8.txt]" Dim oDb : Set oDb = CreateObject("ADODB.Connection") Dim sCs : sCs = Join(Array( _ "Provider=MSDASQL" _ , "Driver={Microsoft Text Driver (*.txt; *.csv)}" _ , "DBQ=" + sTDir _ ), ";") oDb.open sCs WScript.Stdin.Readline Dim oRs : Set oRs = oDb.Execute("SELECT * FROM " & sFName) WScript.Stdin.Readline Do Until oRS.EOF WScript.Echo oRS.Fields(0).Value oRs.MoveNext Loop oRs.Close oDb.Close 

For some background look here .