Hey, Scripting Guy! How can I make the name of the file the first line of each and every text file in a folder?

— DJ

Hey, DJ. And, by the way, we hope your day is going better than ours. Are we having a bad day? You might say that. For example, this morning the Scripting Guy who writes this column zoomed right past the grocery store where he buys doughnuts every day. A Scripting Guy forgetting about doughnuts?!? If that’s not a bad omen, well, we don’t know what is.

Believe it or not, things seemed to get progressively worse as the day went on. As it was, the only thing keeping him going was the fact that this column was written on Friday. That meant that – as the Scripting Guy wrote this column – the next day was a Saturday. And Saturday’s are always good days.

Well, except when the Washington Huskies (who have given up 170 points in their past four games alone) have a football game, that is. Which they do.

Oh, well, maybe Sunday will be a better day. After all the forecast for Sunday calls for – you know what? Never mind. We’ll just sit here and feel sorry for ourselves.

Hmmm …. Turns out that sitting around and feeling sorry for yourself isn’t as much fun as we thought it would be. (So many people do that here at Microsoft that we assumed it must be fun.) OK, then, let’s see if there’s something else we can do to kill a little time. Tell you what, why don’t we see if we can figure out an answer to today’s question.

And, sure, what the heck: we’ll even see if we can figure out a correct answer to today’s question.

Before we do that, let’s briefly explain the setting for today’s column. According to DJ’s email, he has a series of text files containing temperature data. Those text files look something like this:

45.544 34.544 65.433 56.783
32.451 65.432 22.435. 44.564

What DJ would like is to have a script that could run through all the files in that folder and, for each one, insert the file name as the first line in the text file; that way these files can be imported into another software program. (What software program? Oh, no you don’t; we still haven’t forgotten what happened the last time we mentioned a third-party software program.) In other words, DJ would like to end up with a series of text files that look something like this:

apr_avg_temp
45.544 34.544 65.433 56.783
32.451 65.432 22.435. 44.564

But you know what the Rolling Stones said, don’t you, DJ? You can’t always get what you want.

Fortunately, though, in this case you can get what you want. Here’s a script that can make the file name the first line in the text file, and do that for every file in a folder:

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
strComputer = “.”
Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set colFiles = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\TemperatureData’} Where ” _
        & “ResultClass = CIM_DataFile”)
For Each objFile in colFiles
    strFileName = objFile.FileName
    strFilePath = objFile.Name
    Set objFile = objFSo.OpenTextFile(strFilePath, ForReading)
    strContents = objFile.ReadAll
    objFile.Close
    Set objFile = objFSo.OpenTextFile(strFilePath, ForWriting)
    strContents = strFileName & vbCrLf & strContents
    objFile.Write strContents
    objFile.Close
Next

How does this script work? We’ll tell you how. The script starts out by defining a pair of constants, ForReading and ForWriting; we’ll need to use these two constants when we read from and write to each text file. After defining the two constants we then create an instance of the Scripting.FileSystemObject, the object that actually enables us to read from and write to text files.

Our next task is to connect to the WMI service on the local computer. Now, usually when we write about WMI our next sentence is something along the lines of this:

“Of course, you can also run this script against a remote computer.”

That’s not going to be our next sentence this time, however. Instead, this is going to be our next sentence:

“And before you ask, no, you can’t run this script against a remote computer, at least not without making major changes to the code.”

So why can’t we run this script against a remote computer? Can’t WMI retrieve a collection of files from a remote machine?

Well, yes, WMI can retrieve a collection of files from a remote machine. However, in this case WMI isn’t the culprit; instead, the problem lies with the FileSystemObject, an object designed to work only with local files on the local computer. That doesn’t mean that it’s impossible to perform this task on a remote computer; however, like we said, to do so would involve making major changes to the code. If there’s enough interest in running this script against remote machines, well, we’ll see what we can do about that. In the meantime, you might want to take a look at thisHey, Scripting Guy! column, which talks about working with text files on a remote machines.

After we connect to the WMI service we use this line of code (and one of those awkward-looking Associators Of queries) to return a collection of all the files found in the folder C:\TemperatureData:

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

And now, at long last, we’re ready to do some real work.

At this point, the first thing we need to do is set up a For Each loop to loop through the collection of files found in C:\TemperatureData. And what do we do inside that loop? For starters, this:

strFileName = objFile.FileName
strFilePath = objFile.Name

In the first line we’re assigning the value of the FileName property to a variable named strFileName. In WMI, the FileName property is simply the name of the file, not including the file extension. For example, suppose we have the file C:\TemperatureData\Apr_avg_temp.txt. The value of the FileName property for this file? Apr_avg_temp.

In line 2, we assign the value of the Name property to a variable named strFilePath. The name of this property (Name) is actually a bit misleading; in WMI, the file’s Name is equivalent to the file path. That means that the file C:\TemperatureData\Apr_avg_temp.txt has a Name equal to, well, C:\TemperatureData\Apr_avg_temp.txt.

As soon as we’ve assigned values to our two variables we call the OpenTextFile method to open the first file in the collection; that’s what we do with this line of code:

Set objFile = objFSo.OpenTextFile(strFilePath, ForReading)

After the file is open we use the ReadAll method to read in the entire contents of the file and store those contents in a variable cleverly-named strContents. (Why do we read the entire contents of the file into a variable? We’ll explain that in a moment.) We then call the Close method to close the file.

Yet another good question: why do we close the file? After all, don’t we still need to make the file name the first line in the file?

Well, yes, we do still need to do that. However, we initially opened our file for reading, something we had to do in order to read from the file. With the FileSystemObject you can open a file for reading or for writing, but you can’t open a file for reading and for writing; instead, we have to open the file for reading, read from it, and then close the file. After that, we can then reopen the file, this time for writing.

Which, coincidentally enough, is what we do with the very next line of code:

Set objFile = objFSo.OpenTextFile(strFilePath, ForWriting)

All that brings us to this line of code:

strContents = strFileName & vbCrLf & strContents

As we’ve seen, the FileSystemObject is a bit temperamental. For one thing, it won’t let you open a file for reading and writing; on top of that, it won’t let you selectively modify a file, either. Instead, the only way to make changes to a file is to read the contents into memory, make the changes in memory, and then rewrite the entire file. That, by the way, explains why we read the entire contents of the file into memory: we have to make all our changes in memory.

That also explains the line of code we just showed you; in that line we’re constructing our new file contents by combining:

Glommed together, that makes strContents equal to this:

apr_avg_temp
45.544 34.544 65.433 56.783
32.451 65.432 22.435. 44.564

Which, now that you mention it, is exactly what we want this file to look like, isn’t it? And that’s good, because that means that all we have to do now is call the Write method to replace the existing contents of the first file with the newly-modified contents currently in memory:

objFile.Write strContents

And then we simply close the file, return to the top of the loop, and repeat the process with the next file in the collection.

That should do it, DJ. As for the Scripting Guy who writes this column, well, don’t feel too bad for him; things are bound to start looking up sooner or later. Why, if nothing else, on Thursday morning the Scripting Guys head for Barcelona and the TechEd IT Forum. Let’s see, dealing with the security lines at the airport; eating airline food; taking a 4 ½ hour flight to Atlanta; sitting around the Atlanta airport for 3 hours; taking a 9-hour flight across the Atlantic – you know what, that will be better than the day we had today, way better. See you all tomorrow.