Hey, Scripting Guy! Question

Hey, Scripting Guy! I need to read in data from a text file and store each line as a separate item in an array. However, the data in each line must also be stored as an array; in other words, I need an array made up of other arrays. I tried creating a multidimensional array, but I can’t get it to work. Can you help?
-- PA

SpacerHey, Scripting Guy! AnswerScript Center

Hey, PA. As you probably know, on the planet Vulcan there’s an old saying, “One does not thank logic.” What does that have to do with creating a multidimensional array? Well, now that we think about it, we have no idea; in fact, we’re having problems figuring out what that has to do with anything. But, then again, as the Scrolls of Saruk so eloquently state, “Those who hate and fight must stop, otherwise, it is not stopped.”

In other words, if you don’t stop then you won’t stop. No wonder those Vulcans are considered so dang smart.

Interesting Vulcan Note: Although humans often admire and respect Vulcans, Vulcans aren’t necessarily impressed by humans. However, Vulcans are somewhat perplexed by humans: “We don't know what to do about Humans. Of all the species we've made contact with, yours is the only one we can't define. You have the arrogance of Andorians, the stubborn pride of Tellarites. One moment you're as driven by your emotions as Klingons, and the next, you confound us by suddenly embracing logic."

The arrogance of Andorians! As far as the Scripting Guy who writes this column is concerned, those are fighting words!

Of course, as far as the Scripting Guy who writes this column’s manager is concerned, the Scripting Guy who writes this column should actually try writing this column. You know what? That’s the problem with managers at Microsoft: they’re always making impossible demands of their employees.

But, then again, as Surak said, “Things are only impossible until they are not.” (Is it just us, or is anyone else beginning to wonder if Yogi Berra is actually a Vulcan?” So is it impossible to find a solution to PA’s question? Well, you know what they say: You can’t find out until you find out.

Note. Actually, we made that last saying up ourselves. Not bad, eh?

Of course, before we can do that we need to explain PA’s scenario. (As he noted in his email, “I know this is confusing; at least it was typing it out.”) PA has a text file with data similar to this:

1.      192.168.10.101  00188B22BE77    SAO002331       Ken Myer
2.      192.168.10.104  0019D2C841D7    SAO002339       Ken Myer (Laptop)
3.      192.168.10.113  00121B44CW77    SAO002361       Pilar Ackerman
4.      192.168.10.116  001CE5C92108    SAO002385       Pilar Ackerman (Laptop)

What PA needs to do is read in this data, storing each line as an item in an array; in other words, the first item in the array will correspond to the first item in the text file:

1.      192.168.10.101  00188B22BE77    SAO002331       Ken Myer

That’s easy. Except there’s a catch: PA would actually like the information extracted from line 1 to also be stored as an array. In other words, he’d like the first item in the array to be another array, an array containing these items:

1.

192.168.10.101

00188B22BE77

SAO002331

Ken Myer

And that’s a problem. As PA noted, he tried creating a multidimensional array but immediately ran into problems. That doesn’t surprise us; with the possible exception of Mr. Spock, no being in the universe truly understands – or can make full use of – multidimensional arrays. And yes, that includes the Scripting Guys. (While it’s true that the Scripting Guy who writes this column always wears a baseball hat, that isn’t because he’s trying to hide his Vulcan ears. That’s just because he has absolutely no fashion sense whatsoever.) To be perfectly honest, the Scripting Guys find multidimensional arrays to be more trouble than they’re worth; because of that we have no desire to even try to come up with a solution that relies on multidimensional arrays.

Star Trek Trivia Note: According to StarTrek.com Mr. Spock’s actual Vulcan name is “unpronounceable.” Other sources, however, suggest that his Vulcan family name is Xtmprsqzntwlfd. That’s a bit of a mouthful, but, to be honest, it doesn’t seem any more difficult to pronounce than Tsaltas or Costantini.

By the way, if you do try to pronounce Tsaltas remember that the Ts is pronounced like Ch; if you try to pronounce Xtmprsqzntwlfd remember that the q is silent. As for Costantini, don’t worry too much about it. As long as you’re handing out free food Peter is guaranteed to show up no matter what you call him.

So, PA, does this mean that we aren’t going to answer your question? Let’s put it this way: this is going to be by far the least interesting Hey, Scripting Guy! column ever written.

Nevertheless, we are going to answer your question:

Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt")

i = 0

Do Until objFile.AtEndOfStream
    ReDim Preserve arrLines(i)

    strLine = objFile.ReadLine
    strItem1 = Trim(Mid(strLine, 1, 8))
    strItem2 = Trim(Mid(strLine, 9, 16))
    strItem3 = Trim(Mid(strLine, 25, 16))
    strItem4 = Trim(Mid(strLine, 41, 15))
    strItem5 = Trim(Mid(strLine, 57, 21))

    arrLines(i) = Array(strItem1, strItem2, strItem3, strItem4, strItem5)
    i = i + 1
Loop

objFile.Close

intLine = InputBox("Please enter a line number:")
intLine = intLine - 1

arrInfo = arrLines(intLine)
Wscript.Echo "IP address: " & arrInfo(1)
Wscript.Echo "MAC address: " & arrInfo(2)

Let’s see if we can explain how this script works. As you can see, we start things off by defining a constant named ForReading and setting the value to 1; we’ll use this constant when we open the text file C:\Scripts\Test.txt. After defining the constant we create an instance of the Scripting.FileSystemObject object, then use this line of code to open Test.txt for reading:

Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt")

As soon as the file is open we assign the value 0 to a counter variable named i. And once we’ve done that we’re ready to get down to business.

Note. Actually, we’re ready to relate another bit of Vulcan trivia: every 7 years Vulcans face an overpowering mating drive known as pon farr; during this time Vulcans must have … contact … with someone lest they go insane or even die.

And no, we have no comment on that. Like Fox News, we report; you decide.

Now, back to the script. After we configure out counter variable we set up a Do Until loop designed to loop until our text file’s AtEndOfStream property is True; that simply means that we want to keep reading from the file until there’s nothing left to read. Inside this loop, the first thing we do is use the ReDim Preserve command to redimension our array to the value of the counter variable i:

ReDim Preserve arrLines(i)

The first time through the loop i is equal to 0; that means we’re creating an array of size 0. That, of course, means that we’re creating an array capable of containing 1 item. Arrays always hold 1 more item than their size. Which, in addition to our arrogance, is yet another reason Vulcans find humans so perplexing.

After we redimension our array our next step is to use the ReadLine method to read the first line in the text file, storing that information in a variable named strLine:

strLine = objFile.ReadLine

Of course, as we noted earlier, we’d prefer that this line be stored as an array; in other words, we’d like to chop the value into its constituent pieces and then store those pieces as separate items. That’s what this block of code is for:

strItem1 = Trim(Mid(strLine, 1, 8))
strItem2 = Trim(Mid(strLine, 9, 16))
strItem3 = Trim(Mid(strLine, 25, 16))
strItem4 = Trim(Mid(strLine, 41, 15))
strItem5 = Trim(Mid(strLine, 57, 21))

What’s going on here? Well, PA has a fixed-width text file; that is, the separate fields in his text file take up a specified amount of space. For example, take a look at the beginning of line 1:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

1

.

 

 

 

 

 

 

1

.

9

2

.

1

6

8

As you can see, the first field (the line number) starts in character position 1; the second field (the IP address) doesn’t start until character position 9. That means that the first field starts at character position 1 and continues through character position 8, even though some (in this case, most) of those character spots end up as blank spaces. The command Mid(strLine, 1, 8) simply tells the script to take the value of strLine, start with character 1, and grab the next 8 characters. That’s going to make strItem1 equal to this, with the dashes representing blank spaces:

1.------

Needless to say, we don’t really need the blank spaces on the end of this value. Therefore, we also use the Trim function to delete any blank spaces from the beginning and end of the string. That makes the final value of strLine equal to this:

1.

See how that works? We then repeat the process 4 times, methodically grabbing the values for each field in the text file. It’s a little confusing, but it works.

So what do we do with these field values once we’ve gotten hold of them? This:

arrLines(i) = Array(strItem1, strItem2, strItem3, strItem4, strItem5)

As you can see, there’s nothing fancy here. We’re simply assigning an array to item i in the “master” array arrLines. That means that the first item in arrLines will be another array. And that’s fine; if you want to store an array inside an array, well, who is VBScript to question that? After all, he that walks with logic shall be logical.

Note. Based on the recent weather, if he walks in Seattle he’ll also be soaked unless he walks both with logic and an umbrella.

From there we increment the value of i by 1, then return to the top of the loop and repeat the process with the next line in the text file. When we’re all done, the first item in the array arrLines will be another array, an array containing these elements:

1.

192.168.10.101

00188B22BE77

SAO002331

Ken Myer

The second item in the array arrLines will be yet another array, this one containing these elements:

2.

192.168.10.104

0019D2C841D7

SAO002339

Ken Myer (Laptop)

Etc., etc.

After closing the text file we then – per PA’s needs – prompt the user to enter a line number:

intLine = InputBox("Please enter a line number:")

As soon as we get the line number we immediately subtract 1 from it:

intLine = intLine - 1

What’s the point of that? Well, we assume that the user is going to enter one of the actual line numbers shown in the text file (e.g., line 3). That’s fine, except for one thing: because the first item in an array is item 0 that means that line 3 will actually be array item 2. If the user wants to work with line 3 that means they want to work with item 2 in the array. Hence we need to subtract 1 from the line number.

If that hurts your brain, well, join the crowd. But that’s just the way arrays work.

That brings us to our final block of code:

arrInfo = arrLines(intLine)
Wscript.Echo "IP address: " & arrInfo(1)
Wscript.Echo "MAC address: " & arrInfo(2)

All we’re doing here is grabbing the requested item from the array arrLines and assigning it to yet another array.

Note. Is this a world record for the most arrays ever used in a single script? We hope not. At the moment the Scripting Guys hold the world’s record for fewest world records held. If we accidentally set a new world’s record then we’ll lose the only world’s record we actually hold.

After assigning a value to arrInfo we can then – again, per PA’s needs – report back the IP address (item 1 in the array arrInfo) and the MAC address (item 2 in the array arrInfo). If the user really did request the information for line 3 then the output should look like this:

IP address: 192.168.10.113
MAC address: 00121B44CW77

And there you have it, PA; live long and prosper. Or, as Surak himself would say, "Once you have thrown away your pach-te, you have found the true center, the golden river, the lifeblood of the world."

So true, so very, very true.