Schema.ini 2

[Swi]

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
    .Close
    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.

Thanks.

Swi

 

[fredericofonseca]

try adding DateTimeFormat=DD/MM/YYYY HH:NN:SS
to your schema.ini.

One other possible issue is that one row has blank or invalid data on that field

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Swi]

Ok, I will give that a shot. Thanks.

Swi

 

[dilettante]

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

 

[Swi]

Yep, thanksl

Swi