Re: Pipe Delimited files with headers
- From: John Nurick <j.mapSoN.nurick@xxxxxxxxxxxxxx>
- Date: Thu, 07 Sep 2006 21:36:33 +0100
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
On 6 Sep 2006 11:52:42 -0700, normalit@xxxxxxxxx wrote:
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.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
- References:
- Pipe Delimited files with headers
- From: normalit
- Pipe Delimited files with headers
- Prev by Date: Re: Duplicating lines
- Next by Date: Re: Import Fixed Width with Header rows
- Previous by thread: Re: Pipe Delimited files with headers
- Next by thread: Importing data didn't populate related tables
- Index(es):
Relevant Pages
|
