Download... (6 kb)

One of the most common questions that newbie and some not so newbie programmers ask is how to read in a text delimited file, such as a comma separated file. Most of these programmers do the following:

Private Sub LoadFile()
    Dim handle As Integer
    Dim sCurrentLine As String
    Dim arrayTokens() As String
    
    handle = FreeFile
    Open "c:\file.txt" For Input As handle
    Do While Not EOF(handle)
        Line Input #handle, sCurrentLine
        arrayTokens = Split(sCurrentLine, vbTab)
        'process arrayTokens
    Loop
End Sub

For those who don't understand the above code: we open the file, read it line by line, then split each line into tokens and then process those tokens. This method is too slow, too prone to error and makes it too inconvinient to deal with data.

The solution is to open the text file into a Recordset. Once the file is in the recordset, you can anything: Filter various attributes in and out, search for various fields of data, enumerate fields, XML (used here as a verb) the data, whatever. This is, of course, not as simple as opening a recordset from an Access table, but definetely worth the trouble.

So, as a result, here I am providing a reusable class that can open any kind of text delimited or separated file, be it comma separated or tab delimited or whatever else. It can use the the first line for column names, if you have those, or not. The code is as simple as this:

Private Sub LoadFile()
    Dim oRs As ADODB.Recordset
    Dim oTextHandler As clsTextHandler
    
    Set oTextHandler = New clsTextHandler
    With oTextHandler
        .Delimiter = vbTab
        .UseFirstLineForColumnNames = True
        .FileName = "c:\file.txt"
        If .Execute(oRs) Then
            MsgBox "Recordset Loaded"
        Else
            MsgBox "Failed" & vbCrLf & vbCrLf & .LastError
        End If
    End With
	
    Set oTextHandler = Nothing
    Set oRs = Nothing
End Sub

Download... (6 kb)