Hey, Scripting Guy! How Can I Import a .CSV File into an Office Access Database?

ScriptingGuy1

Hey, Scripting Guy! How can I import a .CSV file into an Office Access database?

— CS

Hey, CS. We’ll get to your question in just a minute. First, though, a few thoughts leftover from Super Bowl XLI:

Overall the Scripting Guy who writes this column thought that the game was OK, although it did buttress his belief that college football is far more entertaining than pro football. After all, the Colts completely dominated the game, yet could have easily lost, in large part to the fact that they always settled for field goals rather than going for it on, say, fourth-and-goal from the 2. At one point, the Colts even punted from the 36-yard line. The fact is, the NFL is just too conservative. The Scripting Guy who writes this column foresees the day when the Super Bowl will consist entirely of the two teams punting back and forth to one another until someone either runs the kick back for a touchdown or fumbles the ball away in the end zone.

Let’s see, what else? Here’s something: the Scripting Guy who writes this column has never quite understood the hype surrounding Super Bowl TV commercials, and this year’s batch of ads didn’t help matters any. In several cases he watched a commercial and then thought, “Why would that make me want to buy their product?” In at least one case he watched a commercial and thought, “What exactly is their product?” And at least one commercial that aired was a year or two old. But we suppose that when you spend $2.5 million to buy air time, you probably don’t have enough money left over to pay for a new commercial.

Interestingly enough, there was one commercial that made a big impact on the Scripting Guy who writes this column, although perhaps not the impact that was intended. In this ad a robot on a car assembly line drops a bolt and is immediately fired. He tries to get another job, but it’s no good: he builds cars, he doesn’t work at fast-food restaurants. At the end he’s about to jump off a bridge when, fortunately, he wakes up and realizes that it was all a dream.

Admittedly, this was a well-made tear-jerker: you couldn’t help but feel sorry for the little robot. But, considering the fact that American car companies have been struggling in recent years, this definitely sends an interesting message to US autoworkers: make one little mistake and you’re fired. And guess what? You build cars, which means that you’re not going to be able to find another job somewhere; the only thing you’ll be able to do is jump off a bridge and end it all.

That should boost employee morale, shouldn’t it?

Finally, no, the Scripting Guy who writes this column didn’t watch the halftime show. He did, however, see a minute or two of the pre-game show. But once he saw the fake referees riding around on fake ostriches he decided to go see if there were any clothes that needed washing. And, if there weren’t, to go roll around in the mud and make some clothes that needed washing.

Now, does any of this help you import a .CSV (comma-separated values) file into an Access database? Probably not. But this should:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const ForReading = 1

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = c:\scripts\test.mdb" 

objRecordSet.Open "SELECT * FROM Employees", _
    objConnection, adOpenStatic, adLockOptimistic

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt")

Do Until objFile.AtEndOfStream
    strEmployee = objFile.ReadLine
    arrEmployee = Split(strEmployee, ",")

    objRecordSet.AddNew
    objRecordSet("EmployeeID") = arrEmployee(0)
    objRecordSet("EmployeeName") = arrEmployee(1)
    objRecordSet("Department") = arrEmployee(2)
    objRecordSet.Update

Loop

objRecordSet.Close
objConnection.Close

To be honest we don’t know of a straightforward method for importing a comma-separated values file into an Access database. (We aren’t saying that there isn’t one; we just don’t know about it.) However, it’s pretty easy to use a script to open the .CSV file and then “manually” add new records to the database. Which is exactly what we’ve done here.

We’re assuming, CS, that you already have the Access database created; for our sample script we’re using a database named C:\Scripts\Test.mdb. Inside that database we have a table named Employees; this table consists of three fields:

As you might guess, our text file (C:\Scripts\Test.txt) also consists of three fields. Thus:

1989,Ken Myer,Finance
1990,Pilar Ackerman,Research
1991,Jonathan Hass,Human Resources

In this case, we have three new employees to add to the database. And here’s how we do that.

We start out by defining three constants: adOpenStatic and adLockOptimistic, which are used for working with the database; and ForReading, which is used for working with the text file. We then use the following block of code to open Test.mdb and to create a recordset consisting of all the items in the Employees table:

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)

objConnection.Open _
    “Provider = Microsoft.Jet.OLEDB.4.0; ” & _
        “Data Source = c:\scripts\test.mdb”

objRecordSet.Open “SELECT * FROM Employees”, _
    objConnection, adOpenStatic, adLockOptimistic

In case you’re wondering, almost all of the preceding code is boilerplate code that can be used as-is. About the only things you might need to change are the path to the database file and the name of the table.

After opening the database our next step is to open the text file. To do that we create an instance of the Scripting.FileSystemObject, then use the OpenTextFile method to open the file Test.txt for reading:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt")

Now we’re ready to start adding records to the database. To begin with, we set up a Do Until loop that runs until we reach the end of the text file (that is, until the AtEndOfStream property is True). Inside that loop we use the ReadLine method to read the first line from the text file and store that value in a variable named strEmployee:

strEmployee = objFile.ReadLine

What does that mean? That means that, at the moment, strEmployee is equal to this:

1989,Ken Myer,Finance

That’s nice, but it doesn’t do us a lot of good: we need to parse the individual field values from that string. Fortunately, we can do that by using the Split function and splitting the string on the comma:

arrEmployee = Split(strEmployee, ",")

What does that give us? That gives us an array (named arrEmployee) consisting of the following three items:

And guess what? Those three items correspond quite nicely to the three fields in our database.

In turn, that means we can now add Ken Myer to the database. To do that we call the AddNew method to add a new record to the Employees table. We then specify the field values using this code:

objRecordSet("EmployeeID") = arrEmployee(0)
objRecordSet("EmployeeName") = arrEmployee(1)
objRecordSet("Department") = arrEmployee(2)

There’s nothing too complicated here. In line 1 we’re simply setting the value of the EmployeeID field to the value of item 0 in our array. (Remember, in a VBScript array the first item always has an index number of 0.) In line 2, we set the value of the EmployeeName field to the value of item 1 in our array (the second item). And so on and so on.

After assigning all the field values we then write the new record to the database by calling the Update method:

objRecordSet.Update

From there we loop around and repeat the process with the next line in the text file. And so on and so on.

Now, where were we? Oh, right, the Super Bowl. The truth is, for the Scripting Guy who writes this column the best part of the Super Bowl is listening to all the … expert .. commentators. For example, for the past two weeks everyone has remarked that Indianapolis quarterback Peyton Manning could not be considered a great quarterback until he won a Super Bowl. (Interesting side note: All these same commentators consider Hall of Famers Dan Marino and Warren Moon great quarterbacks. Try to guess how many Super Bowls these great quarterbacks won.) Having finally won his Super Bowl what did the commentators have to say about Peyton Manning? “Winning one Super Bowl is nice. But to be considered a truly great quarterback you have to win at least two Super Bowls.”

Maybe Peyton Manning should just jump off a bridge and get it over with.

ScriptingGuy1

Follow