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 SubPretty simple, ha? Included in the download is a demo program that demonstrates the functionality, as well as a sample tab delimited file that I used to test the class. To start the demo, download, start Project1.vbp, then press the Run button. Enjoy.
Download... (6 kb)