Hey, Scripting Guy! How Can I Modify a .CSV File and Then Import That File into Microsoft Excel?

ScriptingGuy1

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?
— R

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.

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, ",")

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)

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"

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.

ScriptingGuy1

Follow