Pipe-delimited files into access db

[normalit]

I am trying to import a 140-field, pipe delimited file into an access
database (obviously of the same size). When I run the script, noted
below, I get an error (noted below the script):

I have tried different scripts I have found within Google groups, but
not sure which would be the best, this is the latest I have tried:

******* Start of script, assumes Ticket_Data table is created, which it
always will be for me *****

Option Explicit

Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim blTExists 'As Boolean
Dim strSQL 'As String

Const DB_NAME = "C:\Documents and
Settings\normalit\Desktop\db\test.mdb"
Const TBL_NAME = "Ticket_Data"
Const DATA_SOURCE = "[Text;HDR=Yes;Database=C:\Documents and
Settings\normalit\Desktop\db;].20060406_019012#csv"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

strSQL = "INSERT INTO " & TBL_NAME _
& " SELECT * FROM " & DATA_SOURCE & ";"

oDB.Execute strSQL

oDB.Close

***** End of script, ERROR Received: *****

C:\>cscript "C:\Documents and Settings\normalit\Desktop\test.vbs"
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

C:\Documents and Settings\normalit\Desktop\test.vbs(27, 1)
DAO.Database: The INSE
RT INTO statement contains the following unknown field name:
'ticket_number|peop
le_state_ckbox|police_checkbox|dcn|town'. Make sure you have typed the
n
ame correctly, and try the operation again.

****** End of code & error *****

Can anyone please explain what I need to do so it will accept each pipe
delimited field and properly import the text file into the database?

Thank you very much in advance...

 

[normalit]

Also, I should note another problem I am having. This file (along with
the other 2000 files of same format) has the first line as the field
headers/names, and the second line as the data. What can I use to skip
the first line and only get the data not the headers? I've tried
obj.skipline, but I get an error on skipline.

Thank you again.

 

[Albert D. Kallal]

Is there any reason why you *have* to using windows scripting here?

If you launch ms-access, you need to then do a import of data (file->get
external data).

When the wizard launches, you can setup the type of import etc. And, you
then SAVE this import spec

Then, for future imports of the data, you can have some code in ms-access,
and use transfer text command (and that above import spec which tells
ms-access all kinds of things, like first row is field names, and the
delimiter used)..

Once you get the above working, then you can use a windows script to launch
ms-access, and have it run that import code that you tested. This could
would be a subroutine *inside* of ms-access.

With your example, I see nothing that tells ms-access the field names, and
nothing that specifies that this is fixed length, or variable length file
with the | as a delimiter. I also see nothing that tells ms-access that the
first does (or does not) contain the field names. There is a LOT of things
missing here.

So, in place of a windows script, I would suggest using VBA in ms-access.
(the whole import code should only be 2-4 lines of code...