Re: Best way to import text file into existing table on a daily ba

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Ken,

Thanks so much for your reply!! I will try it this weekend.

One quick question: Should the fields in my temporary table (20 fields) be
a copy of the first 20 fields of my existing table?

Thanks again,

Bluesky

"Ken Snell (MVP)" wrote:

Best bet is to use a macro or VBA code to run the TransferText action, which
will import a text file to a table. You can create an Import Specification
that defines the data types, field sizes, delimiters, text qualifiers, field
names, etc. by starting the import manually, set all the settings you want
for the file, then click the Advanced button at bottom left of wizard
window, and save the settings as an import specification (you name it
whatever you want). Click OK in that Advanced window, then cancel the import
in the main wizard window.

I find it's easier to have a temporary table to receive the data from the
text file. Use TransferText to import the data to this table (be sure to
empty it via a delete query first). Then use an append query to copy the
data into the permanent table. Then run the delete query again to empty the
temporary table.

When you import a text file or an EXCEL spread***, ACCESS/Jet will try to
fill the receiving table's fields from "left to right" (when viewing in
data*** mode). So, if the table has more fields than the text file or
spread***, those extra fields in the table are left empty. This feature is
how you can have an autonumber field as the primary key in the receiving
table and not worry about the import process trying to write data into that
autonumber field -- just make the autonumber field the "last" field in the
table, and the import process will not try to fill it when the data being
imported do not have a field that corresponds to the autonumber field.

--

Ken Snell
<MS ACCESS MVP>



"bluesky" <bluesky@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:67C0D437-8535-45E6-AC8A-BC10BFFC1506@xxxxxxxxxxxxxxxx
Hi -

What is the best way to import a text file into an existing table in
Access
2003 on a daily basis?

Brief scenario: This is for a research study. Daily I get text files
from
our IT people that contains information about new patients. I need to
import
that text file into an existing table that I have in Access. The text
file
has fewer fields than the existing table.

What is the best way to import this text file? Can I import a text file
into Access that has fewer fields than the existing table? Do I need to
run
an append query also?

Someone here suggested I save the text file as an excel spread*** and
import that?

Thanks for your help

-bluesky




.

Quantcast