Import CSV into MS Access database

[Jody Greene]

I would like to programically import a CSV into an existing MS Access
database. is there an easy way to do this?

[Marilyn]

Several ways, depending on how often and how consistent the source files
are. I do a lot of it, with source files of extremely variable quality and
consistency. The method we've settled on as least work over all is to go via
Excel. Add Excel as a reference to your VB app, open the source file as a
workbook, and import the rows one at a time as records. You can make Excel
do all the work of validation (all rows have the same number of columns? all
numeric values really are numbers, etc?) without having to do too much work
at the VB end.

But it's a fairly heavy-handed approach if your source files are clean and
consistent.

"Jody Greene" <jody....@attbi.com> wrote in message
news:epINnTJdCHA.2532@tkmsftngp09...

[Paul Clement]

On Tue, 15 Oct 2002 15:55:43 -0600, "Jody Greene" <jody....@attbi.com> wrote:

¤ I would like to programically import a CSV into an existing MS Access

¤ database. is there an easy way to do this?

¤

If it's a simple comma-delimited file this is relatively easy:

Sub ImportTextToAccessADO()

Dim cnn As New ADODB.Connection
Dim sqlString As String

cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\My Documents\DB1.mdb;" & _
"Jet OLEDB:Engine Type=4;"

'New table
sqlString = "SELECT * INTO [tblOrder] FROM [Text;DATABASE=d:\My
Documents\TextFiles;HDR=No].[Order.csv]"
'Existing table
'sqlString = "INSERT INTO [tblOrder] SELECT * FROM [Text;DATABASE=d:\My
Documents\TextFiles].[Order.csv]"

cnn.Execute sqlString

End Sub

If your text file has fixed length fields or uses a custom delimiter you may need to use a
schema.ini file:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Paul ~~~ pcle...@ameritech.net
Microsoft MVP (Visual Basic)