Hey, Scripting Guy! I have a comma-separated values file that I need to import into Excel. However, before I do that I need to do some arithmetic and then add an additional field to the CSV file. On top of that, when I import this file I need to format this new field as a date-time value. How can I do that?
Hey, R. Well, by now most of you have probably heard that Microsoft is no longer interested in buying Yahoo! Although we offered Yahoo! a deal worth around $50 billion, that proposal was rejected; Yahoo! wanted another $5 billion or so. (Hey, don’t we all want another $5 billion or so?) At any rate, Microsoft CEO Steve Ballmer decided that $55 billion was more than Yahoo! was worth, and he called the deal off.
For both Yahoo! and Microsoft life goes on; today is simply business as usual. For Yahoo! stockholders, however, it’s a different story: after all, no sooner did Microsoft announce that it had called off the deal then Yahoo! shareholders saw their stock value nosedive by 20 percent. The Scripting Guys hate to see anybody suffer a financial setback like that. Therefore, as a public service, the Scripting Guys would like to announce that we are not walking away from the deal that we proposed a couple weeks ago: in return for complete and total ownership of the company, we will give every Yahoo! stockholder their very own Dr. Scripto bobblehead doll. (Editor’s Note: Bobblehead dolls not included.) In fact, not only are we not walking away from that deal, but as a way to sweeten the offer a little, Scripting Guy Peter Costantini has agreed to divvy his salary up among each and every Yahoo! shareholder.
Note. Did Scripting Guy Peter Costantini really agree to divvy his salary up among each and every Yahoo! shareholder? Well, if you want to get picky about it, no, he didn’t. But that’s only because Peter is an extremely busy guy, and we didn’t want to bother him. And seeing as how he didn’t actually say “no,” well, that must mean that his answer is “yes.”
In case you’re wondering, we don’t know what Peter’s annual salary is. We know how much Peter is worth, but that’s a different story.
But wait; that’s not all. In addition to a bobblehead doll and a cut of Peter Costantini’s salary, each and every Yahoo! shareholder will also receive a script that can modify a CSV file, import that file into Microsoft Excel, and then apply a date-time format to one of the columns. That’s a script that, by itself, has an estimated street value of $62.3 billion, well above the Yahoo! asking price of $55 billion.
What’s that? You don’t think the Scripting Guys even have a script that can do all? Well, just feast your eyes on this baby:
Const ForReading = 1 Const ForWriting = 2 Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.csv", ForReading) Do Until objFile.AtEndOfStream strLine = objFile.ReadLine arrItems = Split(strLine, ",") intDateValue = arrItems(1) - 1530000 intItems = Ubound(arrItems) ReDim Preserve arrItems(intItems + 1) If intDateValue > 0 Then arrItems(intItems + 1) = intDateValue Else arrItems(intItems + 1) = "" End If strNewLine = Join (arrItems, ",") strNewText = strNewText & strNewLine & vbCrLf Loop objFile.Close Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.csv", ForWriting) objFile.Write strNewText objFile.Close Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.csv") Set objRange = objExcel.Cells(1, 6) Set objRange = objRange.EntireColumn objRange.NumberFormat = "m/d/yyyy hh:mm:ss AM/PM"
There appears to be a question in the back: can we explain how this script works? Not really; after all, we’re already losing a lot of money as it is by giving the code away. But what the heck; if it will help cinch the deal and get us ownership of Yahoo!, well, then we’ll do it.
As you can see, the script starts out the same way most $62 billion scripts start out: by defining a pair of constants (ForReading and ForWriting), constants we’ll use when we go to open our .CSV file. In fact, we’re going to use one of those constants (ForReading) right now; after we create an instance of the Scripting.FileSystemObject we use this line of code to open the file C:\Scripts\Test.csv for reading:
Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.csv", ForReading)
Ah, good question: what does Test.csv look like? Well, as it turns out, it looks something like this:
200Printer1,-2000000,3600,text,tree 201kmille5lt1,1570587,1200,text,leaf 201Printer1,1570761,3600,text,leaf 201SShrimpWS,1570587,1200,text,leaf 202Check1,1570454,3600,text,fruit 202jgrovelt,-2000000,1200,text,fruit 202LWtextLKE1WS,1570588,1200,text,fruit 202Printer0,1570732,3600,text,fruit 202Printer1,1570871,3600,text,fruit
But it’s not going to look like this for long; before we import this file into Excel we need to extract the second field in each line (in line 1, that’s the value -2000000) and then subtract 1530000 from that number. Believe it or not, if that number is positive (greater than 0) then it can be converted to a date-time value in Excel. That means that, if the number is positive, then we’ll tack that value onto the end of the line in the text file; if the value is negative then we’ll add an empty string (“”) to the end of the line.
So how exactly will we do all that? Well, to begin with, we set up a Do Until loop that runs until the file’s AtEndOfStream property is True; in other words, we’ll keep reading until there’s nothing left to read. Inside that loop we use the ReadLine method to read the first line in the text file, then use VBScript’s Split function to split that value (splitting on the comma) into a mini-array named arrItems:
arrItems = Split(strLine, ",")
For those of you scoring at home, that makes arrItems equal to this:
200Printer1 -2000000 3600 Text tree
Why do we bother splitting our line of text into an array? Well, here’s one reason: that makes it very easy to grab the value of the second field in the line (arrItems(1)) and then subtract 1530000 from it, something we do with this line of code:
intDateValue = arrItems(1) - 1530000
In turn, that brings us to these two lines of code:
intItems = Ubound(arrItems) ReDim Preserve arrItems(intItems + 1)
As you know, we just grabbed the first line in our text file and converted it to an array. We now need to add an additional field to the end of that line; in effect, that means we need to add a new item to the array arrItems. How are we going to do that? Well, first we need to determine how many items are currently in the array; that’s what the Ubound function tells us. We then use the ReDim Preserve statement to redimension the array, setting the new size to the number of items currently in the array (intItems) plus 1.
Now we’re ready to add a new item to the array:
If intDateValue > 0 Then arrItems(intItems + 1) = intDateValue Else arrItems(intItems + 1) = "" End If
All we’re doing here is checking to see if the value of the variable intDateValue is greater than 0. If it is, we then set the value of our new array item (arrItems(intItems + 1)) to, well, the value of intDateValue. If intDateValue isn’t greater than 0 then we set the value of this new array items to an empty string.
And you’re right: this isn’t particularly sophisticated. But what do expect for a measly $62 billion?
At this point we’re ready to start reversing our steps, sort of. Having started things off by splitting a line of text into an array, we’re now going to use the Join method to combine all the items in the array into a single, comma-separated string value:
strNewLine = Join (arrItems, ",")
Note. Like we said, though, we aren’t really going backwards and undoing what we just did. After all, strNewLine, our new line of text, does include the additional array item that we just added.
After creating the new string value we then add this value (plus a carriage return-linefeed, the VBScript constant vbCrLf) to a variable named strNewText:
strNewText = strNewText & strNewLine & vbCrLf
And then it’s back to the top of the loop, where we repeat the process with the next line in the text file.
After we’ve read and processed each line in the text file we use the Close method to close Test.csv, then immediately reopen the file, this time for writing:
Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.csv", ForWriting)
Note. Yes, we know. But the FileSystemObject won’t let you simultaneously open a file for both reading and writing; it has to be one or the other.
With Test.csv reopened we use the Write method to replace the existing contents with the value of the variable strNewText. And then we once more close Test.csv.
Now it’s time to open – and format – this file in Excel. To do that, we first create an instance of the Excel.Application object, then set the value of the Visible property to True; that gives us a running instance of Excel that we can see on screen. We then use the following line of code, and the Open method, to open our .CSV file:
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.csv")
The pre-formatted version of this spreadsheet is going to look like this:
What do we do next? Well, first we execute these two lines of code:
Set objRange = objExcel.Cells(1, 6) Set objRange = objRange.EntireColumn
In line 1 we’re simply creating an instance of the Excel Range object, a range that encompasses cell F1 (row 1, column 6). In line 2 when then use the EntireColumn method to extend that range to take in all of column F.
And once that’s done we can reformat all the cells in the range using the following line of code:
objRange.NumberFormat = "m/d/yyyy hh:mm:ss AM/PM"
Note. For more information on changing the format of a cell see this classic Hey, Scripting Guy! column.
Good point; the word “classic” is a bit redundant, isn’t it?
What’s our spreadsheet going to look like now? It’s going to look like this:
Not only did we get the spreadsheet we wanted, but we saved ourselves $62 billion in the process. All in all, not a bad day’s work.
Incidentally, a lot of you might be thinking, “Gee, $55 billion is a lot of money; I wonder what Yahoo! makes in order to be worth that much money.” If that’s what you’re thinking, well, you simply don’t understand how the new economy works. No one actually makes anything any more; that’s the old-fashioned way of earning a living. Today businesses exist for the sole purpose of selling ads to other businesses, often times ads that encourage you to visit this other business’ Web site. And what can you buy when you visit these other Web sites? Nothing; those sites also exists solely for the purpose of selling advertisements. That’s the beauty of the new economy.
What’s that? How’s the new economy doing these days? Well, OK, we aren’t saying that it hasn’t hit a rough spot or two. But those will get smoothed over as soon as we sell a few more ads.
As for all you Yahoo! shareholders, well, you know how to get hold of us. We’ll be waiting to hear from you.