Read Entire CSV to Memory and Parse

Submitted by Suat Mehmet Ozgur of Istanbul, Turkey. Suat develops applications in Excel, Access, and Visual Basic for and

This sample takes a different approach to reading a text file. Rather than read one record at a time, the macro loads the entire text file into memory in a single string variable. The macro then parses the string into individual records. The advantage of this method is that you access the file on disk only once. All subsequent processing occurs in memory and is very fast.

Sub ReadTxtLines()

'No need to install Scripting Runtime library since we used late binding

Dim sht As Worksheet

Dim fso As Object

Dim fil As Object

Dim txt As Object

Dim strtxt As String

Dim tmpLoc As Long

'Working on active sheet Set sht = ActiveSheet 'Clear data in the sheet sht.UsedRange.ClearContents

'File system object that we need to manage files

Set fso = CreateObject("Scripting.FileSystemObject")

'File that we like to open and read

'Opening file as a TextStream

Set txt = fil.OpenAsTextStream(l)

'Reading file include into a string variable at once strtxt = txt.ReadAll

'Close textstream and free the file. We don't need it anymore. txt.Close

'Find the first placement of new line char tmpLoc = InStr(1, strtxt, vbCrLf) 'Loop until no more new line Do Until tmpLoc = 0

'Use A column and next empty cell to write the text file line sht.Cells(65536, 1).End(xlUp).Offset(1).Value = _ Left(strtxt, tmpLoc - 1)

0 0

Post a comment