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

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

  • Comments 4
  • Likes

Hey, Scripting Guy! Question

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

-- CS

SpacerHey, Scripting Guy! AnswerScript Center

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.

Note. Of course, maybe that’s a good thing: after all, when the day comes that the team most afraid to take a chance is guaranteed to win then our very own Seattle Seahawks will finally be Super Bowl champions.

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.

Note. For some reason, prior to Super Bowl LXI any time the Scripting Guy who writes this column thought about American football the words “Cirque de Soleil” never once popped into his head.

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:

EmployeeID

EmployeeName

Department

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:

1989

Ken Myer

Finance

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.

Interesting side note 2. These same commentators all consider Brett Favre of the Green Bay Packers a great quarterback, maybe the best quarterback of all time. So, of course, that means that Brett Favre won at least two Super Bowls, right? Uh, probably. We’ll have to get back to you on that ….

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • The new location for the old scripting guys articles must break the formatted code. Making it for the most part useless considering code is lost off the page.  I guess your trying to get everyone over to powershell. Fine, but I'm already using C# and vb.net and sometimes I come here because I remember something I need. Well, in this case I found it, but the code is not formatted correctly and much of it is lost :( ... I know- whatever

  • What if you have a comma in the data?

  • Couldn't you use transfertext to accomplish the same thing?

    eg:    DoCmd.TransferText acImportDelim, , "Table1", strInputFileName, Yes

    This will transfer the csv file to a table (called 'table1') from location strInputFileName... You can even have an OpenFile dialog come up as part of an OnClick event.

  • thanks