Hey, Scripting Guy! How Can I Prevent Office Excel From Turning My Imported Numbers Into Dates?

ScriptingGuy1

Hey, Scripting Guy! I have a CSV file, and every time I open that file in Office Excel my numbers get converted to dates. How can I import numbers as numbers?

— JR

Hey, JR. You know, just yesterday the Scripting Guy who writes this column learned about Yelo, an interesting little company based in New York City. So what does Yelo do? Well, primarily what they do is offer rooms (“Yelo cabs”) where people go and take a nap. That’s right: if you live in New York City you can pop into the Yelo offices, write them a check for $15, and they’ll let you take a nap in one of their rooms. (You can also get a 40-minute nap for $28.) Needless to say, this has to be the greatest business model in history: people pay you for the opportunity to sleep. Revolutionary.

Well, OK, maybe not that revolutionary; after all, people has been paying money for years to sleep through one of Dennis Miller’s standup routines. But we don’t think he intended for them to sleep through those routines.

In all fairness, we should point out that you get more than just a nap when you visit Yelo; instead, you get a power nap. What’s the difference between a nap and a power nap? About $15 for every 20 minutes, as near as we can tell.

OK, so we’re being a little mean; we have nothing against naps or against Yelo. In fact, we’re jealous. After all, the Scripting Guy who writes this column has been lulling people to sleep for years, and has never gotten a penny for it.

Speaking of which, looks like it’s time to address today’s question. JR has a CSV (comma-separated values) file that looks like this:

1-2
2-3
3-4
4-3
That seems innocuous enough, but each time he opens this file Microsoft Excel … helpfully … converts these values to dates:
2-Jan
3-Feb
4-Mar
6-May
And yes, that is very nice of Excel, isn’t it? However, that isn’t what JR wants to do; instead, he wants Excel to leave those values exactly as they are. How can he do that? Why, by running the following script, of course:
Const xlTextFormat = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

objExcel.Workbooks.OpenText "C:\Scripts\Test.txt",,,,,,,,,,,, _
    Array(Array(1, xlTextFormat))
As you can see, this isn’t a particularly long script (although that last line is a bit of a doozy, isn’t it?). We start things out by defining a constant named xlTextFormat and setting the value to 2; we’ll use this constant to tell Excel that we want to format our imported values as text rather than dates. Why text? Shouldn’t we be formatting these values as numbers?
Believe it or not, the answer to that is no, we shouldn’t. Why not? Because 1-2 and 2-3 aren’t actually numbers. (If you don’t believe us, trying counting to 2-3. It’s going to be awhile before you get there.) If you give Excel something that looks like a number then it will try to convert that something into a number. But as far as Excel is concerned, 1-2 doesn’t look like a number; instead, it looks like a date. That’s why Excel keeps transforming JR’s data into date-time values; Excel thinks those are date-time values.
Incidentally, this has nothing to do with the fact that we’re using a script to import these values. Open up Excel, type the following in cell A1, and then press ENTER:
1-2
On the Scripting Guys’ test machine (running US English) Excel immediately converts 1-2 to the following:
2-Jan
Hmmm …. OK, what if we apply a numeric format to cell A1. Do that, and our value will change to this:
39449.00
Where did that come from? Well, as far as Excel is concerned, history began on January 1, 1900; Excel has marked that as Day 1. That means that January 2, 1900 was Day 2; January 3, 1900 was Day 3; and, by extension, January 2, 2008 was Day 39449.





Note. To test this, type 1/1/1900 in cell A1; Excel should then display the value 1.00. We next tried typing in the Scripting Editor’s birth date, but all we got back was a negative number. Wonder what that means ….





As it turns out, the only way to display the value 1-2 is to format the cell as text. But we can’t apply that formatting after the fact; that is, we can’t import the values and then format the cells as text. If we try that, Excel will simply take the value 39449.00 (the number equivalent of January 2, 2008) and convert that to a string value:
39449
Not really what we had in mind. Instead, we have to explicitly format those values as text up front. And that – to make a long story just a tiny bit longer – is why we need to define the constant xlTextFormat.
Don’t you like it when the explanation for the first line of code is 150 times longer than the entire script?
Fortunately, very little explanation is required for the next two lines of code: in those lines we simply create an instance of the Excel.Application object, then set the Visible property to True. That simply gives us a running instance of Excel that we can see on screen.
That also brings us to our last line of code
objExcel.Workbooks.OpenText "C:\Scripts\Test.txt",,,,,,,,,,,, _
    Array(Array(1, xlTextFormat))
All we’re doing here is using the OpenText method to open our CSV file (C:\Scripts\Test.txt). OK, but then what are all those commas following the file path for? Well, as we noted a minute ago, we need to specifically import our file as text. Excel has a parameter (FieldInfo) that enables us to specify a data type when opening a text file. The FieldInfo parameter must be the 13th parameter passed to the OpenText method; the first parameter is the file path, the second, optional parameter is the file origin; etc. (For a complete list of parameters, see Excel’s VBA Language Reference). Other than the first (the file path) and the 13th (FieldInfo) parameters we don’t need to use any special values; for parameters 2 through 12 we can just go with the default values. However, we still need to use a placeholder (a blank space) to represent those parameters. For example, what if our last line of code looked like this:
objExcel.Workbooks.OpenText "C:\Scripts\Test.txt", Array(Array(1, xlTextFormat))
It’s a cleaner-looking line of code, but it’s also doomed to fail: that’s because we made FieldInfo the second parameter, which means Excel will think this is the file origin. It’s not, and, as a result, our script is going to blow up. That’s why we put in all those commas: they represent parameters for which we’re simply using the default value.
Then what about our 13th parameter:
Array(Array(1, xlTextFormat))
As it turns out, field information must be passed as an array of arrays; that’s why we have the mini-array Array(1, xlTextFormat) enclosed in another call to the Array function. As you might expect, each mini-array represents a field in our CSV file. The first value in the array represents the field number; in this case we have a 1 because we’re dealing with the first (and only) field in the CSV file. The second value in the array represents the data type; we need to import this data as text, so we use the constant xlTextFormat.
And that’s all we have to do. What’s going to happen when you run this script? Give it a try and see for yourself.
Admittedly, that’s a pretty simple case; after all, our text file only has one field. But what if our CSV file has multiple fields? For example, what if our file looks like this:
1-2,A,5-6
2-3,B,7-8
3-4,C,9-10
4-3,D,11-12
In that case you need to do two things. First, you need to set the OpenText method’s ninth parameter to True; that tells Excel that we are using the comma as our field delimiter. (Again, see the VBA Language Reference for more information.) In addition, we need to include information for all the fields in our call to OpenText. Notice how our revised script includes mini-arrays for fields 1, 2, and 3:
Const xlTextFormat = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

objExcel.Workbooks.OpenText "C:\Scripts\Test.txt",,,,,,,,True,,,, _
    Array(Array(1, xlTextFormat), Array(2, xlTextFormat), Array(3, xlTextFormat))
Give that a try and see what happens.
That should do it, JR; if it doesn’t please let us know. In the meantime, the Scripting Guys have done a little investigating and discovered that the folks at Yelo might be on to something: there is research that suggests that people who take naps 3 times a week live longer, healthier lives. As a matter of fact, a number of companies are now making nap rooms available to their employees. Does that include Microsoft? Well, the Scripting Guys have been sleeping on the job for years now. But, again, we don’t think that was the intention.

        

                

        
		
        
        


            

            

                
                    ScriptingGuy1                
                
            
            


                
        

            Follow