Hey, Scripting Guy! I have a folder that has hundreds of text files in it. I’d like to take each of those files and add the contents (as a memo field) to an Access database. How can I do that?

— KB

Hey, KB. You know, a few years ago the Scripting Guy who writes this column gave a talk at a conference in Orlando. (And yes, he’ll be back in Orlando June 4-8 for TechEd 2007 … assuming, of course, that the city’s restraining order really has expired.) Because it’s no fun to go to Orlando by yourself, he did some negotiating, and ended up taking along his then 10-year-old son and one of his son’s friends. (Yes, he did take the two boys out of school for a week. But, in his view, at least, education is highly-overrated: after all, the Scripting Guy who writes this column has a master’s degree, and look where that got him.)

As it turned out, the trio happened to arrive in Florida during the closest thing Orlando has to an off-season. One evening they were at Disney World and, about an hour or so before the park was due to close, rode Splash Mountain. When the ride was over they realized that there was no one waiting in line; naturally, they walked around, got back on the ride, and rode again. When they got off, they noticed the same thing: pretty much everyone had already gone home for the day. And so they continued riding Splash Mountain, over and over again, until the folks at Disney finally told them it was time to go.

The moral of the story? There are some things that you just never get tired of; you’re willing to do them over and over and over again. For some people, that means riding rides at an amusement park; for most of us, though (including those who breathlessly followed the Hey, Scripting Guy! columns of May 17 and May 18) that means writing scripts that work with text files. In particular, that means writing scripts that work with all the text files in a folder:

Const ForReading = 1
Const adLockOptimistic = 3

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
objConnection.Open _
    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _
        “Data Source = C:\Scripts\Test.mdb” 
objRecordSet.Open “SELECT * FROM TextFiles” , _
    objConnection, adOpenStatic, adLockOptimistic
strComputer = “.”
Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set colFileList = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\Archive’} Where ” _
        & “ResultClass = CIM_DataFile”)
For Each objFile In colFileList
    Set objTextFile = objFSO.OpenTextFile(objFile.Name, ForReading)
    strContents = objTextFile.ReadAll
    objTextFile.Close
    objRecordSet.AddNew
    objRecordSet(“FileName”) = objFile.Name
    objRecordSet(“FileContents”) = strContents
    objRecordSet.Update
Next
objRecordSet.Close
objConnection.Close

We’ll give everyone a moment to catch their breath and then we’ll see if we can figure out how this script works. We start out by defining a pair of constants, ForReading and adLockOptimistic. ForReading is the constant we’ll use to specify the file mode (for reading) when we open all the text files; adLockOptimistic is the constant we’ll use to indicate how we want to lock our recordset when working with the database. (Optimistic locking simply means that we’ll only lock the recordset when calling the Update method.) After defining the two constants we then create a pair of database objects: ADODB.Connection and ADODB.Recordset. From there we go ahead and create an instance of the Scripting.FileSystemObject, the object used to read text files. We won’t need this object right away, but this seemed as good a time as any to create it. (Fortunately it won’t go stale if we don’t use it immediately.)

Unlike the FileSystemObject, we’re going to immediately put our two database objects to use. First, we use the Connection object to connect us to the database C:\Scripts\Test.mdb:

objConnection.Open _
    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _
        “Data Source = C:\Scripts\Test.mdb”

Once we’ve made that connection we use the Recordset object to retrieve a collection of all the records found in the TextFiles table:

objRecordSet.Open “SELECT * FROM TextFiles” , _
    objConnection, adOpenStatic, adLockOptimistic

As you’ve probably already figured out, TextFiles is the name of the table where we plan to add the new records. For today’s script we’re assuming that the TextFiles table already exists. However, you could modify this script so that it first creates the new table and then adds records to this brand-new table. For more information on programmatically creating database tables, see the webcast we mentioned a few minutes ago.

Now that we have a database connection our next task is to grab hold of all the text files. To do that we first use this block of code to connect to the WMI service on the local computer:

strComputer = “.”

Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)

And yes, due to limitations with the FileSystemObject, this script is designed to work only on the local computer. That doesn’t mean that you can’t ever, under any circumstances, read text files stored on a remote machine; it just means you’ll have to make some modifications to the script before you can do so. What kind of modifications? Why, the ones we discuss in this other column, of course.

You know, now that you mention it, there are a lot of links and see-alsos in today’s column, aren’t there? That couldn’t possibly mean that the Scripting Guy who writes this column is getting lazy?

Nah.

OK; what’s next? Well, next we use this bit of code to retrieve a collection of all the files found in the folder C:\Archive:

Set colFileList = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\Archive’} Where ” _
        & “ResultClass = CIM_DataFile”)

At this point, and at long last, we’re finally ready to roll: we’re ready to start reading text files and to start adding the contents of those files to our database.

Speaking of which, we should note that we’re using a very simple database table, one that includes just two fields:

Just something we thought you should know.

OK, now it’s time to start adding records to the database. Thanks to our WMI query we have a collection of all the files in the folder C:\Archive, a collection we stashed away in a variable named colFileList. With that in mind we next set up a For Each loop to loop through each and every one of these files:

For Each objFile In colFileList

The first thing we do inside this loop is use the OpenTextFile method to open file No. 1 in the collection:

Set objTextFile = objFSO.OpenTextFile(objFile.Name, ForReading)

Notice that we pass OpenTextFile two parameters: objFile.Name, a WMI property that returns the complete path to the file; and the constant ForReading (because we want to read from this file). We then use the ReadAll method to read the contents of the file into a variable named strContents:

strContents = objTextFile.ReadAll

And once we have the contents stashed safely away in memory we call the Close method and close the file.

That brings us to the following block of code:

objRecordSet.AddNew
objRecordSet(“FileName”) = objFile.Name
objRecordSet(“FileContents”) = strContents
objRecordSet.Update

This – in case you were wondering – is the exciting, thrill-ride portion of today’s column. (Yes, we know: today’s entire column does seem like a thrill ride, doesn’t? But this is the really good part.) As you can see, the first thing we do here is call the Recordset object’s AddNew method; that’s going to add a new, blank record to our database table. We then use these two lines of code to assign the file path (Name) to the FileName field, and the contents of the file (strContents) to the FileContents field:

objRecordSet(“FileName”) = objFile.Name
objRecordSet(“FileContents”) = strContents

And then we call the Update method to actually write this new record to the database. Don’t forget to call this method; if you do forget, none of your changes (that is, none of your new records) will be added to the database.

Which, of course, would sort of defeat the purpose of the script.

After calling the Update method for file No. 1 we loop around and repeat the process with the next file in the collection (that is, the next file in the folder). Sooner or later we’ll have opened and read all the files in the collection; when that happens we automatically exit the For Each loop, then use these two lines of code to sever our connection to the database:

objRecordSet.Close
objConnection.Close

At that point our script, like all good things, comes to an end.

We hope that helps, KB. We also hope that if you (or anyone else) happens to be in Orlando June 4-8 that you’ll stop by the Scripting Guys booth and say hi. And don’t worry: you won’t be intruding on any family time. This time around the Scripting Son and his friend won’t be going; instead, the Scripting Guy who writes this column has the … privilege … of attending the conference alongside Scripting Guy Jean Ross. They say that it’s impossible to go to Orlando and not have a good time. But, then again, they never met Scripting Guy Jean Ross, did they?