Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I read the first line of every file in a folder and then move each file if the line contains thisdomain.com or thatdomain.com?

-- AZ

SpacerHey, Scripting Guy! AnswerScript Center

Hey, AZ. We’ll get to your question in a moment. However, before we do that we have something important to say: flight of cheeses.

OK, we can go on now. Thanks for waiting.

What’s that? Flight of cheeses? What are you talking about; that doesn’t even make any sense, it – oh, right; we were the ones who brought that up, weren’t we? Well, as it turns out, Mary G., the lovely and talented technical writer who single-handedly turned the WMI SDK into one of the most useful pieces of documentation Microsoft has ever produced, is leaving the SDK to take a new position at Microsoft. Good news for Mary, bad news for those of us who rely on the WMI SDK to get us through each and every day of the week.

Yes, even Sunday. Especially Sunday.

Note. So if she’s so good at what she does then how come Mary G. (affectionately known as Mary Queen of Scripts) isn’t a Scripting Guy? Apparently we didn’t make ourselves clear: Mary is good at what she does. That doesn’t sounds like a Scripting Guy, at least not to us.

The truth is, we couldn’t add a female Scripting Guy until we found someone as incompetent as the rest of us. Glad to have you aboard, Jean Ross!

Anyway, at Mary’s farewell lunch the team went to a restaurant in which the Scripting Guy who writes this column was totally out of his league. (Which, come to think it, is true of most places he goes to.) To whit: buttered pasta rags (or, if you prefer, pasta rags with red kurri squash). Guinea hen confit with cracked green olive sauce. Your choice of rhubarb, lavender or lemongrass soda – sorry, dry soda. (Targeted, we assume, to people who feel that they’ve been getting too much liquid in their soda.)

And, of course, a “Flight of 3 artisan cheeses.” The equally lovely and talented June B. (PowerShell writer to the stars) took one look at the menu and said, “Hey, Scripting Guy! If you can find a way to work flight of cheeses into your next column I’ll buy you a latte.” Well, just in case you missed it, June, here it is again, this time in boldface:

Flight of cheeses.

A vanilla latte will be fine. Thanks, June.

Now, back to business:

Const ForReading = 1

strComputer = "."

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

Set colItems = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Scripts'} Where " _
        & "ResultClass = CIM_DataFile")

Set objFSO = CreateObject("Scripting.FileSystemObject")

For Each objItem In colItems
    Set objFile = objFSO.OpenTextFile(objItem.Name, ForReading)
    strLine = objFile.ReadLine
    strLine = LCase(strLine)
    objFile.Close

    If InStr(strLine, "fabrikam.com") or InStr(strLine, "contoso.com") Then
        objFSO.MoveFile objItem.Name, "C:\Archive\"
    End If 
Next   

What we have here is a script that opens all the files in the folder C:\Scripts, reads the first line of each file, then determines whether the string fabrikam.com or the string contoso.com can be found in that initial line. If either value is found the script then moves that file to the folder C:\Archive. How does the script manage to do all that? Let’s see if we can figure it out.

We start out by creating a constant named ForReading and assigning it the value 1; we’ll need to use this constant when we open each of the files in C:\Scripts. Of course, before we can open each of those files we need to retrieve those files. That’s what this block of code is for:

strComputer = "."

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

Set colItems = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Scripts'} Where " _
        & "ResultClass = CIM_DataFile") 

What we’re doing here is connecting to the WMI service on the local computer, then using an Associators Of query to retrieve a collection of all the files (that is, all instances of the CIM_DataFile class) found in the folder C:\Scripts. Admittedly, there are other ways that we could get a collection of all the files in the folder C:\Scripts; we chose to use WMI because this same block of code can retrieve this information from a remote computer. (All you need to do is assign the name of that remote computer to the variable strComputer.)

Note. Of course, just because WMI can retrieve the files from a remote machine that doesn’t mean you can use the rest of the script as-is to actually read those files. Why not? Because the FileSystemObject, the technology used for reading from and writing to text files, doesn’t really let you do that. Is there a way to work around this issue? Maybe; take a look at this Hey, Scripting Guy! column for a suggested approach.

Speaking of the FileSystemObject, that’s the next step in the process: we create an instance of the Scripting.FileSystemObject. Once that’s done we set up a For Each loop to loop through each of the items (files) found in the folder C:\Scripts:

For Each objItem In colItems

Good question: what are we going to do inside that loop? Well, for starters, we’re going to use this line of code to open the first file in the collection; this code works because the WMI property Name actually refers to the complete file path (e.g., C:\Scripts\File1.txt):

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

As soon as the file is open we use the ReadLine method to read the first line, then use the LCase function to convert that initial line to all lowercase letters (just to make sure we don’t run into any problems with mixed case). That’s what these two lines of code are for:

strLine = objFile.ReadLinestrLine = LCase(strLine)

We then immediately close the file. Why? That’s easy: all we’re interested in is the first line (which we’ve already grabbed) so we don’t need the file any more. Therefore, we close it. That brings us to this line of code:

If InStr(strLine, "fabrikam.com") or InStr(strLine, "contoso.com") Then

What we’re doing here is using the InStr function to look at the value of strLine and see if that value contains either the string fabrikam.com or the string contoso.com. If true (that is, if either of those strings appear in strLine) we then use this line of code to move the file to the folder C:\Archive:

objFSO.MoveFile objItem.Name, "C:\Archive\"

What if neither string appears in strValue? No problem; we just loop around and repeat the process with the next file in the collection.

That should do it, AZ. And in case you’re worried, the Scripting Guy who writes this column (who is far more likely to frequent restaurants where you order meals by number) did manage to find something on the menu that he recognized and was willing to eat: a pulled pork sandwich. Granted, the coleslaw was brown (a somewhat disconcerting color for coleslaw) and it tasted brown; however, the sandwich was pretty good.

And, needless to say, that free latte will be even better.