Access Data Project Importing CSV File In VBA

Question

Hi

Every now and then I come across a problem with an old system one of my colleagues has developed. They tend to have thousands of lines of code to do a simple thing like importing a csv file.

Currently the vba process is:

• open excel application
• create new worksheet
• populate the csv file
• into excel add the header names to the file
• save the worksheet as a new excel file
• imports the file into the access data project sql table.
• Process the data

What I want to do with it is:

• import the csv to the table (Like the get external data function)
• process the data

I have had a quick search and cannot see any easy methods of just sucking the file into the table.

Any help would be appreciated.

Thanks

Paul

Answer 1

There is an easier way to import a CSV! You can use the Microsoft Text Odbc Driver.

Sub Import()
   Dim conn as new ADODB.Connection
   Dim rs as new ADODB.Recordset
   Dim f as ADODB.field

   conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\temp;"
   rs.Open "SELECT * FROM [test.txt]", conn, adOpenStatic, adLockReadOnly, adCmdText

   While Not rs.EOF
      For Each f In rs.Fields
         Debug.Print f.name & "=" & f.Value
      Next
   Wend
End Sub

You change from a Select to an Insert into combined with a select and there you are.

There are some settings you can do in the registry, in the key \\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text:

Format: TabDelimited, CSVDelimited, Delimited(X) where X=some char

FirstRowHasNames: 0,1

CharacterSet: OEM, ANSI

Answer 2

In many cases, the easiest way to import CSV is with the TransferText method.

Refer to this MSDN link for details: TransferText Method [Access 2003 VBA Language Reference]

Here is an example TransferText command to import C:\SomeFolder\DataFile.csv into a table named tblImport. The last parameter, HasFieldNames, is False to indicate the CSV file doesn't include field names.

DoCmd.TransferText acImportDelim, "YourCustomSpecificationName", _
    "tblImport", "C:\SomeFolder\DataFile.csv", False

The SpecificationName parameter is optional. However, you can often get better results by creating your own import specification and including its name in the TransferText command. The specification allows you to identify which table fields to load the data into, adjust data types, and a whole host of other options. You can create your own import specification when you manually import your data file ... select your custom import options, and save those choices as a named specification. (Look for an "Advanced" button on the Import Wizard dialog.)