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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,405
Messages
5,511,164
Members
408,828
Latest member
Csmnvld

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top