Hey, Scripting Guy! We have tons of text files in which each line in the file represents a record or an event of some kind. How can I use a script to retrieve records or events that occurred on a specified date?

— TW

Hey, TW. You know, in your email you sort of apologized for using text files, noting that “I know, I know, we should probably use a database rather than text files.” Take it from us, TW: you have nothing to apologize for. If these text files do what you need them to do, then you should keep using them. Admittedly, there are times when a database might be the most appropriate tool; then again, based on the number of questions we get regarding text files it’s obvious that lots of people still find the old-fashioned log file to be extremely useful. As the saying goes, if it ain’t broke, don’t fix it.

The truth is, people often-times worry about things they shouldn’t worry about. We get lots of letters from system administrators saying, “I have a batch file that works great, but it is just a batch file. Should I convert this to a script?” Well, you can if you want to, but if it works (and works great) why bother? If you’re like the Scripting Guys, you have enough to do fixing things that really do need to be fixed; there’s no sense going around fixing things that don’t need to be fixed.

Good point: not only is that a long, philosophical discussion, but it doesn’t mention baseball even once. Wait a minute: this is supposed to be the Hey, Scripting Guy! column; somehow we’ve wandered into Dr. Phil territory!

On the other hand, Dr. Phil never (well, hardly ever) dispenses code like this:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
strPathtoTextFile = “C:\Scripts”
objConnection.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _
    “Data Source=” & strPathtoTextFile & “;” & _
        “Extended Properties=””text;HDR=YES;FMT=Delimited”””
strHireDate = #3/1/2006#
strFile = “Employees.txt”
objRecordset.Open “Select * FROM ” & strFile & ” where HireDate = #” & strHireDate & “#”, _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item(“LastName”)
    Wscript.Echo objRecordset.Fields.Item(“FirstName”)   
    Wscript.Echo objRecordset.Fields.Item(“Department”)
    Wscript.Echo objRecordset.Fields.Item(“HireDate”)   
    objRecordset.MoveNext
Loop

Before we start we should point out that TW has a series of comma-separated values (CSV) files, where the first line in each file is a header row indicating the fields in the file. For example, TW has a file similar to this (note that, in order to make the example a bit easier to follow, we’ve removed several fields):

LastName,FirstName,Department,HireDate
Myer,Ken,Finance,3/1/2006
Ackerman,Pilar,Finance,3/1/2006
Smith,Joe,Resarch,4/1/2006

Is it important that your text files be formatted like this? It’s not just important, it’s crucial. We’re going to use database techniques to retrieve information from the file, and to do that the file needs to be delimited in some way (in this case, using the comma as the delimiter). Ideally, your files should include a header row as well. As long as your text file looks like this you’re in business.

We should note two things here. First, we aren’t going to explain each line of code in-depth; that’s because we’ve already done that in the MSDN column. Second, don’t be scared away by the cryptic nature of some of the code found in the script. As you’re about to see, much of that code is boilerplate that you neither have to worry about nor modify. It’s just code that needs to be there.

The script itself starts out by defining three constants: adOpenStatic, adLockOptimistic, and adCmdText. These constants are used to configure our “database” (text file) query. After defining the constants we create instances of the ADODB.Connection and ADODB.Recordset objects. That brings us to this block of code:

strPathtoTextFile = “C:\Scripts”

objConnection.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _
    “Data Source=” & strPathtoTextFile & “;” & _
        “Extended Properties=””text;HDR=YES;FMT=Delimited”””

As you can see, we’re doing two things here. First, we assign a path to the variable strPathToTextFile. Note that this path represents the folder in which the text file can be found. It’s not the path to the actual text file itself; that would be akin to C:\Scripts\Employees.txt. It’s just the path to the folder.

Second, we call the Open method to make an ADO (ActiveX Data Objects) connection to the folder C:\Scripts. This is a perfect example of the boilerplate nature of this script: assuming your text file is properly formatted and assuming you’ve assigned the folder path to strPathToTextFile, then you can leave this code exactly as-is; you don’t need to change a thing.

If only everything in life was that easy, huh?

After we make a connection we then assign values to a pair of variables:

strHireDate = #3/1/2006#
strFile = “Employees.txt”

In this script we want to retrieve information about all the employees who were hired on March 1, 2006; consequently, we assign that date to the variable strHireDate. Why did we enclose the date in #’s? That’s easy: to ensure that VBScript treats the value as a date, and not as, say, an arithmetic problem (i.e., 3 divided by 1 divided by 2006). Meanwhile, we assign the name of the text file (Employees.txt) to a variable named strFile.

What does all that mean? It means we’re ready to issue our query:

objRecordset.Open “Select * FROM ” & strFile & ” where HireDate = #” & strHireDate & “#”, _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

The query is actually pretty straightforward. One of the fields in our text file is named HireDate. How do we know that? Because that’s one of the field names listed in the file’s header row:

LastName,FirstName,Department,HireDate

When we issue our query we simply request all the records (that is, all the lines in the text file) where the HireDate field is equal to #3/1/2006#. Notice that we use the variable strFile (which contains the name of the text file) at the point in the query where we would typically insert a database table name:

“Select * FROM ” & strFile & ”

And that’s basically that. Through the magic of ADO you’ll get back a recordset consisting of all the records (all the lines in the text file) where the hire date is equal to 3/1/2006. At that point you simply walk through the recordset and echo back the values:

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item(“LastName”)
    Wscript.Echo objRecordset.Fields.Item(“FirstName”)   
    Wscript.Echo objRecordset.Fields.Item(“Department”)
    Wscript.Echo objRecordset.Fields.Item(“HireDate”)   
    objRecordset.MoveNext
Loop

Not only do you get the same results as you would if your data was stored in a database, but you use pretty much the same scripting code as well. If this works for you, TW, then we recommend that you hang on to your text files. For obvious reasons, the Scripting Guys would never suggest that you should get rid of someone – er, something just because it isn’t cool. (Not that the Scripting Guys aren’t cool, mind you, but, still ….)