Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I look at a sequence of numbers in a text file and identify which numbers are missing from the sequence?

-- TP

SpacerHey, Scripting Guy! AnswerScript Center

Hey, TP. Don’t you know that it’s Friday? This sounds like a tricky question to answer, and the Scripting Guys have an agreement with Microsoft that we don’t have to think very hard on Fridays. OK, granted, Microsoft isn’t aware that we have such an agreement. But let’s not nitpick over details. The point is that there’s absolutely no way we’re going to answer this question, at least not today. Sorry, but that’s just the way it goes.

Oh, come on, TP; don’t look at us with those sad, puppy dog eyes – OK, fine, we’ll make a deal with you. We’ll give you an answer to your question; however, this answer will be based on the assumption that the numbers in your text file are already in numerical order (that is, 1-2-3-4 and not 2-1-4-3). If the numbers are not in numerical order you’ll need to sort them before the script we’re about to show you will work. (How can you sort the contents of a text files? This Hey Scripting Guy! column offers one suggestion.)

With that in mind, let’s assume we have a text file containing the following sequence of numbers:

2
4
8
9
10

How are we going to determine which numbers (1, 3, 5, 6, and 7) are missing from the sequence? Like this:

Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("c:\scripts\test.txt")

i = 1

Do Until objFile.AtEndOfStream
    intLine = CInt(objFile.ReadLine)

    Do Until x = 1
        If intLine = i Then
            Exit Do
        End If
        Wscript.Echo i
        i = i + 1
    Loop
    i = i + 1
Loop

objFile.Close

What’s that? Explain how this script works? Come on, TP; didn’t we mention that it was Friday?

Oh, all right. We start off by defining a constant named ForReading and setting the value to 1; we’ll need this constant in order to open and read our text file. Next we create an instance of the Scripting.FileSystemObject, and then use the OpenTextFile method to open the file C:\Scripts\Test.txt.

That brings us to this line of code:

i = 1

Sure, that looks like a meaningless line of code. In reality, though, it’s the key to this entire script. We’re going to use the counter variable i as a way to keep track of which number we should be on in our sequence. We set i to 1 because the first number in our sequence (and thus the first number in the text file) should be 1. If the first number in the text file is 1 and the value of i (the first number in the sequence) is 1 that means that the number 1 is present and accounted for. If the two items don’t match then the number 1 must be missing.

Don’t worry; that will make more sense in a minute or so.

After assigning a value to the counter variable we set up a Do Until loop that runs until the entire file has been read (or, if you want to impress your friends and neighbors, until the AtEndOfStream property is True). Inside that loop the first thing we do is read in the initial line from the text file and assign that value to a variable named intLine:

intLine = CInt(objFile.ReadLine)

Note. What’s the CInt for? That’s a VBScript function that converts character data to an integer value. We’re doing that to make sure VBScript treats the line of text as a number and not as a string.

We’re now ready to see if the first number in the text file (which, in our example, happens to be 2) is equal to the first number in our sequence (1). To do that we set up a second Do Until loop, one that’s designed to run until x is equal to 1. (Technically, x will never be equal to 1; this is just a little trick designed to keep the loop running until we explicitly tell it to stop.) Inside this second loop we use the following line of code to determine whether or not the value read from the text file (intLine) is equal to the first number in the sequence (the counter variable i):

If intLine = i Then

As you probably know, 2 is not equal to 1. (No, really: we wouldn’t kid you about that.) Therefore, we do these two things:

We echo the value of i. Why? Because the value of i (which, the first time through the loop, is equal to 1) is not in the text file.

After reporting that the value 1 was not found we then increment the value of i by 1. That makes our counter variable equal to 2 (1 + 1), which is, of course, the next value in the sequence.

We then loop around and check to see if the value read from the text file (we’re still working with the first line in the text file, so the value of intLine is still 2) is equal to the new value assigned to the counter variable (2). In this case the two numbers are equal; therefore, we don’t want to echo the value because the value (2) does exist in the text file. Instead we call the Exit Do statement to exit the secondary loop. Having found what we were looking for (the value 2) we’re ready to move on.

The Exit Do statement pops us back to the main loop; from there we first increment i (which now gives i a value of 3), read the second line in the text file and repeat the process all over again. The second time around the value read from the text file (4) will not be equal the value of the counter variable (which, as you recall, is now set to 3). Therefore, we echo 3, increment the counter (to 4) and check that against the value from the text file (also a 4).

Etc., etc.

When all is said and done we should get back a “report” that looks like this:

1
3
5
6
7

Hope that helps, TP. And, now seeing as how it is Friday we – what’s that? No, we are not going to wash your car for you. No, sorry; we have to draw the line somewhere. And don’t look at us with those sad puppy dog eyes, don’t – fine; we’ll go get a bucket and the hose.

So much for TGIF.