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

Hi Ken,

OK, I reread you email, and you did give instructions how to create an
Import Specification. I'm such a goof. sorry, now when I went back to the
macro, it was there!!! Ok, back to working on this.

your grateful access newbie,

-bluesky

"bluesky" wrote:

Hi Ken,

I'm working on my database and have a couple questions.

Re: transfer text action, I opened the Macro window, clicked on "new" and
the first line under "Actions" used the pull down menu and clicked on
"transfer text." At the bottom window the "transfer type" is import
delimited, which is what I want. I'm not sure what do for "Specification
Name," there is nothing under the pull down menu to choose. I guess I really
don't know how to use this action. Is there a default character to use for
"Code Page?"

This may sound naive, but why run the TransferText action, instead of using
"get external data" to import the text file? Is to make it easier, and
eventually less time consuming?

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




.