VBScript to import from .CSF into Access 2003

goldfishhh

New Member
Joined
Mar 15, 2003
Messages
45
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!

Code:
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'

Code:
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
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

New Member
Joined
Mar 15, 2003
Messages
45
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

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
Re: VBScript to import from .CSV into Access 2003

tks for sharing that tidbit
 

Watch MrExcel Video

Forum statistics

Threads
1,133,387
Messages
5,658,516
Members
418,450
Latest member
KarlP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top