Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here





I am trying to import a CSV file into an Access database using the following:


Private Sub AppendIMBDataToExistingAccessTable(ByVal PathToMDBAndTXT As String, ByVal MDB As String, _
ByVal MDBPW As String, ByVal TXTFile As String, ByVal Headers As Boolean, ByVal TableName As String, ByVal WhereClause As String)

    ' Create schema file for csv file with current text file name
    Open App.Path & "\schema.ini" For Output As #1
    Print #1, "[" & TXTFile & "]"
    Print #1, "Format=CSVDelimited"
    Print #1, "ColNameHeader = False"
    Print #1, "MaxScanRows = 0"
    Print #1, "CharacterSet = ANSI"
    Print #1, "Col1=F1 Text"
    Print #1, "Col2=F2 Text"
    Print #1, "Col3=F3 DateTime"
    Print #1, "Col4=F4 Text"
    Print #1, "Col5=F5 Text"
    Close #1
    Dim cnMDB As ADODB.Connection
    Set cnMDB = New ADODB.Connection
    With cnMDB
        .Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=" & MDBPW & ";Data Source='" & PathToMDBAndTXT & "\" & MDB & "'"
        .Execute "INSERT INTO [" & TableName & "] ([Facility_ID], [Operation_Code], [Scan_Date_Time], [Routing_Code], " & _
        "[IMB_Tracking_Code], [Scan_Date]) IN '" & PathToMDBAndTXT & "\" & MDB & _
        "' SELECT [F1], [F2], [F3], [F4], [F5], LEFT([F3],10) FROM " & _
        "[Text;Database=" & App.Path & ";HDR=" & IIf(Headers, "YES", "NO") & "].[" & TXTFile & "]" & WhereClause, NumOfRecsImp, adCmdText Or adExecuteNoRecords
    End With
End Sub 

I keep getting a type mismatch error. If I switch the F3 field to text type is works just fine so I know this is the issue. Below is a sampling of the CSV data.

20790,093,09/16/2014 13:57:10,22314232522,00270901118381549726

Any help would be greatly appreciated.



RE: Schema.ini

Ok, I will give that a shot. Thanks.


RE: Schema.ini

I'd rework that with the correct date format though, your data seems to be in m/d/y not d/m/y format.

RE: Schema.ini

Yep, thanksl


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close