VBScript to import from .CSF into Access 2003

[goldfishhh]

I'm using VBScript to automatically import some data from a CSV file into Access. For some odd reason, when I import if there are any characters in the "TERMCODE" field, the import fails resulting in a blank field (by design - On Error Resume Next).

The big question is why does this import fail? I have tried to force the data type to "cstr()" and a bunch of other things. Im at a loss. Here is the kicker, if I go into Access and "File-get external data - import" the .CSV data, it works like a champ.

Help computer!

Option Explicit

Dim adoCSVConnection, adoCSVRecordSet, strPathToTextfile
Dim strCSVFile, adoJetConnection, adoJetCommand, strDBPath
Dim objScript, strPathToMDB, strTempDB

Const adCmdText1 = &H0001

' Specify path to CSV file.
strPathToTextFile = "d:\batch\ "

' Specify CSV file name.
strCSVFile = "prodabenddata.csv"

' Specify Access database file.
strDBPath = "D:\InetPub\WWWRoot\batchquality\backup.mdb"

' Open connection to the CSV file.
Set adoCSVConnection = CreateObject("ADODB.Connection")
Set adoCSVRecordSet = CreateObject("ADODB.Recordset")

' Open CSV file with header line.
adoCSVConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strPathtoTextFile & ";" & _
    "Extended Properties=""text;HDR=YES;FMT=Delimited(,)"""

adoCSVRecordset.Open "SELECT * FROM " & strCSVFile, adoCSVConnection

' Open connection to MS Access database.
Set adoJetConnection = CreateObject("ADODB.Connection")
adoJetConnection.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);" _
    & "FIL=MS Access;DriverId=25;DBQ=" & strDBPath & ";"
adoJetConnection.Open

' ADO command object to insert rows into Access database.
'Set adoJetCommand = New ADODB.Command
Set adoJetCommand = CreateObject("ADODB.Command")
Set adoJetCommand.ActiveConnection = adoJetConnection
adoJetCommand.CommandType = adCmdText1

' Read the CSV file.
Do Until adoCSVRecordset.EOF
    ' Insert a row into the Access database.
    adoJetCommand.CommandText = "INSERT INTO ProdJobABEND " _
        & "([DATE], [TIME], SYSID, JOB, PROGRAM, STEPNAME, APPLICATION, PGMPSNAM, TERMCODE) " _
        & "VALUES (" _
            & "'" & adoCSVRecordset.Fields("DATE").Value & "', " _
            & "'" & adoCSVRecordset.Fields("TIME").Value & "', " _
            & "'" & adoCSVRecordset.Fields("SYSID").Value & "', " _
            & "'" & adoCSVRecordset.Fields("JOB").Value & "', " _
            & "'" & adoCSVRecordset.Fields("PROGRAM").Value & "', " _
            & "'" & adoCSVRecordset.Fields("STEPNAME").Value & "', " _
            & "'" & adoCSVRecordset.Fields("APPLICATION").Value & "', " _
            & "'" & adoCSVRecordset.Fields("PGMPSNAM").Value & "', " _
            & "'" & adoCSVRecordset.Fields("TERMCODE").Value & "')"
    adoJetCommand.Execute
    On Error Resume Next
    adoCSVRecordset.MoveNext
Loop

' Clean up.

adoJetConnection.Close
adoCSVRecordset.Close
adoCSVConnection.Close

      ' Always remember to clean up after yourself
     Set objScript = Nothing

The first 2 work, the second 2 don't work. Also, the datatype for TERMCODE is 'text'

DATE,TIME,SYSID,JOB,PROGRAM,STEPNAME,APPLICATION,PGMPSNAM,TERMCODE
07/28/2010 ,6:00:25 ,G ,APW25AWE ,OPRABEND ,ABEND2A ,ACCOUNTS PAYABLE ,APW25A ,1234
07/29/2010 ,10:21:52 ,G ,A2A01 ,OPND ,ABD02F ,ACCOUNTS ,AP2A ,1234
07/29/2010 ,16:21:14 ,G ,A2A03 ,OPND ,ABD02F ,ACCOUNTS ,AP2A ,1234
07/30/2010 ,13:10:44 ,G ,A2A02 ,OPND ,ABD02D ,ACCOUNTS ,AP2A ,B22
07/30/2010 ,13:17:06 ,G ,A2A02 ,OPND ,ABD02D ,ACCOUNTS ,AP2A ,B22

 

[goldfishhh]

VBScript to import from .CSV into Access 2003

Slight typeo in the subject....

 

[tweedle]

Re: VBScript to import from .CSV into Access 2003

The script looks good.
Being that you are getting at least 1 successful result, that points me to the csv file itself.
It looks like maybe the csv is being appended to by multiple processes?
If that's a correct assumption, possibly those processes are not appending EOL/EOR characters consistently.
It might be worthwhile checking the csv in an advanced ascii editor like textpad and seeing what hidden markers are at the ends of the successful vs failed entries.

 

[goldfishhh]

Re: VBScript to import from .CSV into Access 2003

After MANY googles, I found that there is an optional configuration file for the .TXT/.CSV driver. Simply creating a file in thesource directory and calling it "schema.ini", then placing in the right flags within fixes the problems.

http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

The script looks good.
Being that you are getting at least 1 successful result, that points me to the csv file itself.
It looks like maybe the csv is being appended to by multiple processes?
If that's a correct assumption, possibly those processes are not appending EOL/EOR characters consistently.
It might be worthwhile checking the csv in an advanced ascii editor like textpad and seeing what hidden markers are at the ends of the successful vs failed entries.

 

[tweedle]

Re: VBScript to import from .CSV into Access 2003

tks for sharing that tidbit