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

Hi Ken,

Thanks for your quick reply. Actually the temp file has two header rows.
One is a title of the report and the second row are field names. I think the
"two" headed rows are an issues. I guess I could just delete the first
header row (report title) in the txt file. But when I append the transfer
file to the existing file, doesn't the field name have to match, or least a
primary key match? Sorry for all the extra questions!!

Also, the text files has some extra rows, like report date, etc. So I guess
I have to delete those as well!!

"Ken Snell (MVP)" wrote:

The temp table should have field names that match the header names in the
first row of the text file (if the text file has a header row). Otherwise,
you don't need to have the same field names in the temp table and the
permanent table -- although having them the same will allow the query design
grid to automatically "fill in" the field to which that field will be
appended when you're building the query (to me, this is not an issue that
would require the names to be the same).

--

Ken Snell
<MS ACCESS MVP>


"bluesky" <bluesky@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9981F809-F3DF-43E2-920C-EBBA406E382C@xxxxxxxxxxxxxxxx
Hi Ken,

I'm fine tuning the database now and working with "real" data and could
use
your advice on the field names.

Should the text file and the two tables (temporary and existing) all have
the same exact field names?

My text file from IT has two rows before the actual data. One row is the
report name and the second row is field names (which are different from my
temporary table). Or should the temporary table just have default names,
field1, field2, field3 etc.

It seems that I'm always running into trouble when the field names don't
match.


Any advice would be welcome.

Thanks again,

Bluesky



"Ken Snell (MVP)" wrote:

The temporary table just needs the fields that correspond to the text
file's
fields. You don't need the extra fields to be in the temporary table.

--

Ken Snell
<MS ACCESS MVP>


"bluesky" <bluesky@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A6397FD8-73E1-4BB2-AE7A-087B5C0D975D@xxxxxxxxxxxxxxxx
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










.