Hey, Scripting Guy! How can I copy selected columns from a CSV file to an Excel file?

— MdP

Hey, MdP. One thing that people always worry about (and rightly so) is that one of these days the Scripting Guys will do something that tears a hole in the space-time continuum. Some of you might be concerned that this has finally happened: after all, this is the column for Monday, June 4^th, and yet you might have actually read it on Sunday, June 3^rd! Has it finally happened? Have the Scripting Guys irrevocably warped time and space?

No, we haven’t. (Well, OK, we might have dented it a little, but it’s nothing that can’t be pounded out.) We published the June 4^th column on June 3^rd for one simple reason: on June 3^rd the Scripting Guys set sail for Orlando and TechEd 2007. Scripting Guy Jean Ross left early on Sunday, June 3^rd, but we couldn’t be sure that she would be able to connect to the Microsoft network from her hotel room; therefore, we couldn’t rely on her to publish this column Monday morning. (We also couldn’t be sure that she wouldn’t fall down and crack her head open. But that’s another story.)

Meanwhile, Scripting Guy Greg Stemp couldn’t publish the column on Monday because he opted to watch his son’s baseball game Sunday evening and then take the red-eye special, leaving Seattle around 11:00 PM. His itinerary gets him to Orlando just in time to head to the convention center, and no time to publish Monday’s Hey, Scripting Guy! (And no time to sleep, either, which is a bit of a problem seeing as how he has real trouble sleeping on a plane.)

At any rate, in addition to today’s Hey, Scripting Guy! we had several other important things we wanted to publish, including the first installments of the Windows PowerShell Owner’s Manual as well as the TechEd Challenge, an opportunity for those of you who didn’t get to go to TechEd to win a Dr. Scripto bobblehead doll. And so we published all of this on Sunday. If the discrepancy between the date on the column and the date the column was actually published has driven you insane and you will now be forced to spend the rest of your life in a mental institution, well, we do apologize.

In the meantime, we have a question to answer:

Const ForReading = 1

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
objExcel.Workbooks.Add
i = 1
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForReading)
Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    arrLine = Split(strLine, “,”)
    objExcel.Cells(i, 1).Value = arrLine(0)
    objExcel.Cells(i, 2).Value = arrLine(2)
    i = i + 1
Loop
objFile.Close

Dum-dee-dum-dum …. Sorry; we were hoping that we really had warped the space-time continuum and that, by doing so, we’d have already written the explanation for this script. Unfortunately, though, that didn’t work. Therefore, we’ll just have to figure this script out on our own.

To begin with, we should note that we’re cheating a little bit: we’re not directly importing the text file into Excel. (Well, we’ll double-check, but, no, we don’t believe we can be arrested for that.) It looks like it might be possible to use Excel’s OpenText method to open a text file and specify which columns should be imported and which ones shouldn’t. However, that approach looked a little complicated, especially in light of how easy it is to create a script that opens the text file and then just writes the specified columns to an Excel spreadsheet. And so, as always, the Scripting Guys took the easy way out.

So what exactly do we do here? Well, we begin by defining a constant named ForReading and setting the value to 1; we’ll need this constant when we open our text file. We then use these two lines of code to create an instance of the Excel.Application object and make that instance visible onscreen:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

We then use the Add method to add a new, blank workbook to our instance of Excel; while we’re at it, we also assign the value 1 to a counter variable named i. In a few minutes, we’ll use this counter variable to keep track of the current row in the spreadsheet.

Now it’s time to turn our attention to the text file. For our sample script, we’re assuming we have a comma-separated values file that looks like this:

Ken Myer,Finance,Senior Accountant,New York
Pilar Ackerman,Research and Development,Consultant,Des Moines
Jonathan Haas,Research and Development,Scientist II,Des Moines

As you can see, this sample file has four “columns” (fields):

In our spreadsheet, we want to important just the first and third columns: Name and Job Title. And here’s how we do that.

For starters, we create an instance of the Scripting.FileSystemObject, then use the OpenTextFile method to open the file C:\Scripts\Test.txt for reading:

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForReading)

We then set up a Do Until loop that runs until the file’s AtEndOfStream property is True; that’s just a fancy way of saying that we want the loop to keep going until we’ve read the entire file. Inside the loop, the first thing we do is use the ReadLine method to read line number 1 in the file and store that value in the variable strLine:

strLine = objFile.ReadLine

The first time through the loop that means that strLine will be equal to this:

Ken Myer,Finance,Senior Accountant,New York

Admittedly, that doesn’t do us all that much good, at least not in its current format. Therefore, our next step is to use the Split function to turn this value into an array of individual values. By splitting strLine on the comma we end up with an array (named arrLines). That array has the following elements:

Ken Myer
Finance
Senior Accountant
New York

Believe it or not, this does help us, immensely. We want to write the Name and Job Title to our spreadsheet. And what do you know: the Name just happens to be item 0 in our array. (Remember, the first item in a VBScript array is always item 0.) As if that wasn’t exciting enough, the Job Title happens to be item 2 in the array. (If the first item is item 0, then the second item is item 1, and the third item – Job Title – is item 2.) Cool; all we have to do now is write the values of array items 0 and 2 to the appropriate spots in the spreadsheet:

objExcel.Cells(i, 1).Value = arrLine(0)
objExcel.Cells(i, 2).Value = arrLine(2)

In the first line we’re setting the value of cell row 1, column 1 (i.e., cell A1) to Ken Myer, the user Name. How do we know that this is cell 1, 1? That’s easy: because we assigned the value 1 to the counter variable i. In the following line, we then set the value of cell row 1, column 2 (B1) to Senior Accountant. From there we increment the value of i by 1 (so that, on the next iteration, we’ll be writing data to cells in row 2), then loop around and repeat the process with the next line in the text file. It might not be fancy, but it works:

At any rate, if you happen to be at TechEd please stop by the CMP Media booth (booth number 1301 in the Partner Expo Hall) and say hi. And while you’re there, don’t forget to pick up your copy of Dr. Scripto’s Fun Book. And to enter the drawing for a Dr. Scripto bobblehead doll.

Incidentally, if you have problems finding the CMP Media booth just listen for the sound of someone snoring; that will likely be Scripting Guy Greg Stemp. And no, he won’t be snoring because he had problems sleeping on the plane; he’ll be snoring because Scripting Guy Jean Ross is telling yet another story about her Scripting Dog. Either way, though, they should be easy to find. (Look for the giant Dr. Scripto; that’s usually a good sign that there are Scripting Guys nearby.)