CODEWITHSUNDEEP
vbams-access
Alex Gordon
Alex Gordon

Reputation: 60882

access: read CSV file into table

is it possible to programmatically have access open a certain csv file and read it into a datasheet and set parameters like what time of delimiter, TEXT QUALIFIER etc, including all the steps that one takes to manually import a csv file into a table?

Upvotes: 1

Views: 3505

Answers (2)

user3103059
user3103059

Reputation:

It greatly depends on whether the Access table is already created or if you want to create it on the fly. Let's assume (for the sake of simplicity) that the table already exists (you can always go back and create the table). As suggested before you need some scripting tools:

Dim FSO As FileSystemObject, Fo As TextStream, line As String
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Fo = FSO.OpenTextFile("csvfile.csv")

This will allow you to read your csv file which is a text file. You control here which delimiter you use and which date format will be employed ect. You need also to put in play the database engine:

Dim db As Database
Set db = DBEngine.OpenDatabase("mydatabase.accdb")

And this is basically all what you need. You read your csv file line by line:

While Not Fo.AtEndOfStream
 line = Fo.ReadLine

Now you need to be able to format each field adequately for the table, meaning: text fields must be surrounded by quote marks ("), date fields must be surrounded by #, ect. Otherwise the database engine will noisily complain. But again, you are in charge here and you can do whatever cosmetic surgery you need to your input line. Another assumption I am making (for the sake of simplicity) is that you are comfortable programming in VBA. So let's get to the crux of the problem:

 db.Execute ("INSERT INTO myTable VALUES (" & line & ")")
Wend

At this moment, line has been changed into something edible for the database engine like, for example, if the original read line was

33,04/27/2019,1,1,0,9,23.1,10,72.3,77,85,96,95,98,10,5.4,5.5,5.1,Cashew,0

you changed it into

33,#04/27/2019#,1,1,0,9,23.1,10,72.3,77,85,96,95,98,10,5.4,5.5,5.1,"Cashew",0

One last note: It is important that each field of the csv file matches the one on the table. This includes at least, but not necessarily last, being in the right order. You must ensure that in you preprocessing stage. Hope this would put you on the right track.

Upvotes: 0

bob-the-destroyer
bob-the-destroyer

Reputation: 3154

You can create a Scripting.FileSystemObject, then stream in the file line by line, separating fields by your delimiter, and adding each parsed record to the underlying recordset or another table.

You can also experiment with DoCmd.TransferText, as that seems the most promising built-in method.

Edit: for the more complete (and complex and arguably less efficient) solution, which would give you more control over schema and and treat any csv file like any other datasource, look at ODBC: Microsoft Text Driver - http://support.microsoft.com/kb/187670 for script examples. I believe you can already just link to any cvs files via the standard MS Access front-end, thereby allowing you to do just about any table read/copy operation on it. Otherwise, just set up a ODBC file dns entry to plug in your csv file (via Start->Program Files->Administrative Tools->Data Sources), and then you can select/link to it via your Access file.

Upvotes: 2

Related Questions