import pipe delimited text file into table automatically at specified times

[Brian K. Sheperd]

I am getting a pipe delimited text file several times throughout the day
that is generated from Unix. Presently, I linked the text file to a table,
which works well since the text file is being updated periodically.
However, the queries that I am running in Excel are slow. I tried to import
the data to a table and created a PK on the productnumber. The excel
results were great; however, I am left with a predicament with the updated
text file.



In SQL, I could create a DTS package that drops the table, and performs a
bulk update at specified times, but can this be done with access? So far, I
can only think of creating a VB application to perform this and use the
windows task scheduler to run the program at specified times. Is there
another way?



After writing this, I guess that I could use SQL to use the source text file
and send it to an access database destination with DTS. I was trying to
keep this somewhat stand-alone. Any ideas?



Thanks,

Brian

 

[Nikos Yannacopoulos]

Brian,

Assuming the file is always named the same, as I understand the case is,
I would set up an import process in Access, in one of three options:
* link the text file and use an Append query
* Import through code/macro with TransferText
* Import through code by reading the text file line by line and adding
records through a recordset operation

Now, on running the import process, I see two options again:
* Windows scheduled task running a database macro, like you said, or,
* leaving the database always open and using a timer process in code
which checks the file timestamp periodically (every ten minutes?) and
runs the import process every time a new file is detected; this assumes
either a timestamp field in the destination table, or a separate table
(preferably) like an import log, with a new record added on every
import, logging the imported file's timestamp (and, possibly, the number
of records imported, if required).

Note all of the code / automation could be put in a separate database,
if desirable, in which the target table in the main database is linked.

HTH,
Nikos

 

[Brian K. Sheperd]

Thanks Nikos,

For now, I created a SQL DTS package to perform the task at a scheduled
time.

This leaves me with some breathing room to try out some of your suggestions.
I believe that I am going to have to try the recordset method for dealing
with the data. As for the scheduling, I am still not certain. The database
and text file are located on a server. The server, itself, does not have MS
Access. I was trying to keep everything on the server side instead of
adding in an addition connection for a workstation. So far, I guess, that I
would either have to put Access on the server, or create a VB application
with recordsets to perform the database tasks.

Thanks again,
Brian