Pipe Delimited files with headers

[normalit]

I have a number of files - 2600, which grows by at least 20 every day -
that have about 140 fields each. The first line of each csv file
contains the field names, pipe delimited. The second row of each file
contains the values for each field, also pipe delimited. I have a
vbscript that securely transfers the files from an external server. We
use the stdout to get the list of files, so that should not be a
problem.

The first of my questions is should the process of importing those
files into a table in an Access 2003 database continue on that same
script, or would it be easier to get the directory listing and import
the files using vba and link within database form?

Also, after searching google groups and the 'net, I am unable to find
what I am looking for - mostly because of wrong search terms, I'm sure.
I am not sure how to approach, using either language, the coding of the
process of importing the second line into the table.

thank you.

 

[Jeff Glatt]

http://www.springsys.com/netproducts/orchid.asp

OrchidGrid has an import wizard that could navigate importing delimited text
file.
You can specify the start row to parse/extract or whether to load the first
row of data as column names etc, very flexible.

 

[John Nurick]

It's a matter of taste, but if you're happy with scripting and text
files I'd suggest doing it that way rather than automating Access.
Here's a sample VBScript that shifts data from a text file into a table
in an mdb file and shows what's involved.

On a note of caution: Remember that the maximum size of an mdb file is
2GB. If you're importing thousands of files that limit comes into sight
quite easily (indexes take up space,of course); and remember to compact
the database regularly.

'Sample VBScript to import data from a textfile into
'a table in an MDB database without opening Access

'Modify DB_NAME, TBL_NAME, DATA_SOURCE as required
'and the code that builds strSQL as necessary.

'If TBL_NAME exists, appends to it; otherwise creates it.

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:\Temp\Test 2003.mdb"
Const TBL_NAME = "My_Table"
Const DATA_SOURCE = "[Text;HDR=Yes;Database=C:\Temp\;].B1#txt"

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

For Each oTDef In oDB.TableDefs
If oTDef.Name = TBL_NAME Then
blTExists = True
Exit For
End If
Next

If blTExists Then
strSQL = "INSERT INTO " & TBL_NAME _
& " SELECT * FROM " & DATA_SOURCE & ";"
Else
strSQL = "SELECT * INTO " & TBL_NAME _
& " FROM " & DATA_SOURCE & ";"
End If

oDB.Execute strSQL

oDB.Close