Hey, Scripting Guy! How can I import a fixed-width data file into Microsoft Excel?
Hey, RS. This column is being written on a Friday morning, and so far it’s been a very weird morning at that. For one thing, it’s not raining and – if you look closely – you can even see the sun. (It’s been awhile, but we’d recognize the sun anywhere.) Even weirder, the temperature is supposed to climb up into the 60s by this afternoon, and could even hit 70 degrees Fahrenheit on Saturday. 60-degree weather in April? In Seattle? We must be dreaming.
Note. Actually, 60-degree weather in April isn’t all that unusual for Seattle. Or at least it didn’t used to be: on April 11, 2004, for example, the temperature actually reached 80 degrees. On the other hand, in 1911 the high temperature for April 11th was a mere 41 degrees. Although the Scripting Editor insists that it felt much warmer than that.
We’d give you more details on the current weather conditions if it weren’t for the fact that the Scripting Guy who writes this column doesn’t have a window office; that means that he can only keep tabs on the weather by getting up to go to the bathroom and then glancing out someone else’s window. Admittedly, this would usually be the point where the Scripting Guy who writes this column would make some smart remark about window offices. He’s not going to do that, however; needless to say, window offices are nothing to joke about at Microsoft. People will kill– and for all we know people have killed – just so they could get an office with a window.
Three interesting notes about window offices:
Getting a window office is like being crowned king. When you become king, you become king for life; when you get a window office you get a window office for life. No one ever has a window office taken away from them, ever. Well, no one except the Scripting Guy who writes this column, who had a window office for several months before they decided that office should go to someone who actually has some value around the company.
People will kill – and for all we know people have killed – just so they could get an office with a window. Once they do get a window office approximately 87% of those people keep the blinds pulled at all times because they don’t like the glare.
At the risk of sounding like a sore loser, the Scripting Guy who writes this column believes that window offices are only given to brown-nosers, toadies, and sycophants. Does the Scripting Editor have a window office? Take a wild guess.
On the bright side, not having a window to stare out of does give the Scripting Guy who writes this column plenty of time to write this column. With that in mind, let’s take a look at the kind of fixed-width text file that RS would like to open in Excel:
Region Subnet Country -------------------------------------------------- -------------------------------------------------- -------------- AMIS 192.168.0.0 American Samoa AMIS 192.168.1.0 American Samoa AMIS 192.168.10.0 American Samoa AMIS 192.168.11.0 American Samoa AMIS 192.168.12.0 American Samoa AMIS 192.168.13.0 American Samoa AMIS 192.168.14.0 American Samoa AMIS 192.168.15.0 American Samoa AMIS 192.168.2.0 American Samoa AMIS 192.168.3.0 American Samoa
How can we open a file like that in Excel? Well, here’s one way:
Const xlFixedWidth = 2 Const xlTextFormat = 2 Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.Workbooks.OpenText "C:\Scripts\Test.txt",,3,xlFixedWidth,,,,,,,,, _ Array(Array(0, xlTextFormat),Array(51, xlTextFormat),Array(102, xlTextFormat))
Yes, we know: it is hard to believe that this script will correctly open a fixed-width file, isn’t it? But maybe things will clear up when we explain how it works.
No, check that: things will definitely clear up when we explain how it works.
As you can see, we start out by defining a pair of constants, setting the value of each of these constants to 2. The constant xlFixedWidth tells the script that we’re going to open a fixed-width text file; alternatively, we could create a constant named xlDelimited (with a value of 1) and use that to open a delimited text file (e.g., a comma- or tab-separated values file). The constant xlTextFormat tells the script the data type for each of the fields in our text file. Because all three of our fields are text fields we need to define – and use – only this one constant. However, there are other data types and there are other constants you might need to use. For more information, look for the xlColumnDataType enumeration in the Microsoft Excel VBA Language Reference.
After we define the constants we create an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Microsoft Excel that we can see on screen. And that – cue the ominous music – brings us to the following line of code:
objExcel.Workbooks.OpenText "C:\Scripts\Test.txt",,3,xlFixedWidth,,,,,,,,, _ Array(Array(0, xlTextFormat),Array(51, xlTextFormat),Array(102, xlTextFormat))
So is that the craziest line of code that the Scripting Guy who writes this column has ever written? No, but it’s probably in the top 10. As you can see, we’re using the OpenText method (a method that belongs to the Workbooks collection) to open the file C:\Scripts\Test.txt. As for all the other craziness crammed into that one line of code, well, any time you see a blank parameter (that is, a comma followed by absolutely nothing) that means that this is a parameter we don’t need in order to open our text file. For example, you might note that one of those blank parameters comes immediately after the file path:
As it turns out, this second, optional parameter lets you specify the “origin” of the text file; that is, whether this file came from a Windows computer, an MS-DOS computer, or a Macintosh. (Oh, OK: a Macintosh computer.) Because the default is to assume that the file came from a Windows machine, we can simply leave this parameter blank. Just remember, though, that the parameter must be included even though it has no value. You might think that you could take a shortcut by eliminating all the blank parameters:
objExcel.Workbooks.OpenText "C:\Scripts\Test.txt",3,xlFixedWidth, _ Array(Array(0, xlTextFormat),Array(51, xlTextFormat),Array(102, xlTextFormat))
That’s actually a pretty handy little shortcut … unless you want your script to work. If you want the script to work then leave in all the blank parameters. When you call the OpenText method the numerical position of the parameters is of paramount importance. The constant xlFixedWidth must be the fourth parameter in the method call. If xlFixedWidth appears anywhere else your script is likely to blow up.
And yes, we mean that literally: last year, in the state of Washington alone, nearly 300 people were horribly disfigured when their Excel scripts blew up on them.
Note. Well, OK, so maybe that is a complete and total fabrication. We just thought people might listen to us if we scared them a little.
As for the other parameters, the 3 tells the script that we want to start with line 3 in the text file; that enables us to skip over the two header lines. If you don’t want to skip over the two headers lines then leave this parameter blank.
Note. Can you tell the script to import line 1 – the actual header line – skip line 2, and then start importing again on line 3? No. That’d be cool, but you can’t do it. If you want to get rid of all those dashes you’ll need to open the script and replace line 2 with nothing. We won’t bother explaining how that script might work, at least not today. However, this column should give you a hint or two.
You already know what the constant xlFixedWidth is for; that tells the script that we’re working with a fixed-width file. That leaves us with this:
Array(Array(0, xlTextFormat),Array(51, xlTextFormat),Array(102, xlTextFormat))
Believe it or not, this is where we define each field in the text file. To begin with, because we can (and usually will) have multiple fields in a text file all this information must be passed as an array; that’s why we end up with a bunch of little arrays embedded inside one big, giant array. As for those little arrays, let’s take a look at the code that defines the first field in the text file:
As you can see, we’re creating an array that contains two elements. The first item, the 0, represents the starting character position for this field. (The first character position in a file is position 0; that’s why we have a 0 here rather than a 1.) The second item is the constant xlTextFormat, which defines the data type for this field.
Now, what about field 2? Well, the second field begins at character 51; hence we use 51 for the first item in the array, and the constant xlTextFormat as the second item in the array:
So will that actually import a fixed-width text file into Excel? Well, it did for us; give it a try and see what happens for you.
In the meantime, we’re sure that many of you are thinking, “You know, the Scripting Guy who writes that column is always complaining about how bad the weather is. I wonder when the last time was that they had a nice day in Seattle?” Funny you should ask that: we looked it up, and the last time we actually had a nice day in Seattle was on June 19, 1937. Which was nice; that meant the Scripting Editor had nice weather for her 16th birthday party. (Editor’s Note: Well, that’s pretty close, with the exception of the month, day, and year. It is true, however, that I had a 16th birthday.)