Use DAO to copy data from a CSV file into an Access database

[nandagopal]

This example uses the following INI file to define the CSV file's structure for the database engine. It indicates that the file People.csv is a CSV (comma separated value) file with no column headers, ANSI character set, and columns named First Name and Last Name.

Code Select Expand


[People.csv]
ColNameHeader=False
Format=CSVDelimited
CharacterSet=ANSI
Col1=First Name
Col2=Last Name


When you click the Go button, the program sets the database engine's IniPath property to the INI file. It then opens the current directory as a "database," setting the "table" to the People.csv file.

The program then executes a SELECT INTO statement. It places the data in the PeopleTable table in the database. It gets the data from the People.csv "table."


Private Sub cmdGo_Click()
Const TABLE_NAME As String = "People.csv"
Dim ini_file As String
Dim db_file As String
Dim db As DAO.Database
Dim query As String

    ini_file = txtIniFile.Text
    db_file = txtDatabase.Text

    ' Tell the DB engine where to find table definitions.
    DBEngine.IniPath = ini_file

    ' Open the database.
    Set db = OpenDatabase(App.Path, False, False, _
        "Text;Database=" & App.Path & ";table=" & _
            TABLE_NAME)

    ' Copy the data from the CSV file into the database.
    query = "SELECT * INTO PeopleTable IN '" & _
        db_file & "' FROM " & TABLE_NAME
    db.Execute query

    db.Close
    MsgBox "Done"
End Sub