Specify target columns on import (CSV to Access)

Question

I've found out how to import a CSV into my Access database via another question. But in the answer there is no information on how to target specific columns upon import. How can I be sure that the correct columns from the CSV get placed into the correct columns in my database?

I need to be able to edit/add-to the column values as they get imported using classic ASP. Examples:

• The 4th column in the CSV is a userid, I need to be able to add "@domain.com" to the end as it enters the database for an email column.
• The 6th column is a classification: if it's value is "Teacher" then, as it gets imported, I need to change it to "Classroom".

I'm working with some old code & DB's that I didn't create. Believe me, I'd be using something else if I could, so no pot-shots for using MS Access, please.

I guess this whole thing boils down to this:

• if the previous question's answer really allows me to treat the CSV the same as a db table, then how can I select the columns as in a real db table?

// Update:

I've set everything up how I believe it should be, but I'm receiving an error:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/datazone_bkup/Rollover/importTeachers.asp, line 12

line 12: CSV.open "SELECT * FROM teachers.csv", conn

My full code is as follows:

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &_
             Server.MapPath("\path\to\file") & ";Extended Properties='text;HDR=no;FMT=Delimited';"

Set connCSV = Server.CreateObject("ADODB.Connection")
connCSV.Open strConn

Set CSV = Server.CreateObject("ADODB.recordset")
    CSV.open "SELECT * FROM teachers.csv", conn

    do until CSV.eof
        UserName = CSV.Fields(4)
        LastName = CSV.Fields(1)
        FirstName = CSV.Fields(2)
        MiddleName = CSV.Fields(3)
        School = CSV.Fields(5)
        if CSV.Fields(6) = "Teacher" then
            SecLevel = "Classroom"
        end if
        Active = "Yes"
        TeacherNumber = rsCSV.Fields(0)
        rsCSV.movenext

        sql = "INSERT INTO tblTeacher (UserName, LastName, FirstName, MiddleName, School, SecLevel, Active, TeacherNumber) " &_
                "VALUES (" &_
                    "'" & UserName & "','" & LastName & "','" & FirstName & "','" & MiddleName & "'," &_
                    "'" & School & "','" & SecLevel & "','" & Active & "','" & TeacherNumber & "'" &_
                ")"

        on error resume next
        conn.execute(sql)
    loop

@HansUp, when I tried using the ! as in rsDB!UserName, I received a Syntax Error. So I went back to setting variables for the CSV column values.

Again, thanks for any help.

Answer 1

As in the previous question you linked, let's assume you have opened an ADO recordset with the CSV contents. You can open a second ADO recordset for the Access destination table.

If the CSV file includes column names, you can loop through its recorset rows and insert its values into the destination recordset. If the CSV recordset is rsCSV and the recorset for the destination table is rsDB ...

Do While Not rsCSV.EOF
    rsDB.AddNew
    rsDB!userid = rsCSV!userid & "@domain.com"
    If rsCSV!classification = "Teacher" Then
        rsDB!classification = "Classroom"
    Else
        rsDB!classification = rsCSV!classification
    End If
    rsDB.Update
    rsCSV.MoveNext
Loop

If the CSV doesn't include column names, you can still do it but would need to use the field's ordinal position in the fields collection. Those position numbers are zero-based, so 3 for the 4th column, and so forth.

Do While Not rsCSV.EOF
    rsDB.AddNew
    rsDB!userid = rsCSV.Fields(3) & "@domain.com"
    If rsCSV.Fields(5) = "Teacher" Then
        rsDB!classification = "Classroom"
    Else
        rsDB!classification = rsCSV.Fields(5)
    End If
    rsDB.Update
    rsCSV.MoveNext
Loop