Best way to import text file into existing table on a daily basis

[Guest]

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 spreadsheet and
import that?

Thanks for your help

-bluesky

 

[Ken Snell \(MVP\)]

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 spreadsheet, ACCESS/Jet will try to
fill the receiving table's fields from "left to right" (when viewing in
datasheet mode). So, if the table has more fields than the text file or
spreadsheet, 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.

 

[Guest]

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\)]

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.

 

[Guest]

Thanks Ken,

It's wonderful of you to help with your expertise! I really appreicate it!

I'll give it a shot and let you know how it works!

-bluesky

 

[Guest]

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

 

[Guest]

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

 

[Ken Snell \(MVP\)]

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.

< chuckle >

 

[Guest]

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\)]

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).

 

[Guest]

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\)]

Comments inline..

--

Ken Snell
<MS ACCESS MVP>

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.

ACCESS' built-in text file import process can handle one header row, but not
two. The header row must contain field names for each "column" in the text
file's records, and should match the field names in the table that is
receiving the imported data. If you have two header rows, you'll either need
to manually edit the text file to delete the first row, or you'll need to
write a VBA program that will open the text file and walk through the file's
records and do the import process one record at a time.

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

Not sure what you mean by extra rows, but if these extra rows contain
"extraneous" information that does not conform to the "fields" / "columns"
that otherwise are in the file's records, then, yes, you'll either need to
delete them manually or use VBA programming to do the import (see above).

 

[Guest]

Hi Ken,

I'm still working on this, though almost done!!

When I transfer the text file into my temporary table, it seems to sort on
the first field, which is medical record number. I would not like it to do
this, any suggestions?

When I get the text file reports, new patients are just appended on, and I
would only like to transfer the new patients into my temporary file, is there
a way to do this, do I need to write VBA code? Also, of the new patients, I
only want to transfer those with Yes for a certain field, do I do this in VBA
also?

Thanks a million!

Bluesky

 

[Ken Snell \(MVP\)]

The sorting that you see in the table (when viewing in Datasheet mode) is
unimportant. It has no effect whatsoever on your data. The only sorting that
has an effect (and just on display, not on the data) is the ORDER BY clause
in a query, or the Sorting & Grouping settings in a report.

(Of course, if you're saying that the order of the records in the text file
is important for identifying pertinent new data, then we may need to
completely change the approach to what you want to do. Once you import data,
you have no "sort" or "order" any more that may have existed in the text
file, unless there is some field/value in the text file's records that you
can use to impose sorting on the data after they're in the temporary table
and that would mimic the order that was in the text file.)

How can you differentiate between a new patient and an existing patient when
you view the data from the text file?
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Hi Ken,

After thinking it over, the order of the data doesn't matter!

Re: new patients and existing patients, I figured out a way around that. I
can run the report myself and when it asks for how many days in the past, I
could put whatever time frame I want, so it will be since I last ran it!! I
don't know why I didn't think of that before. Actually, I didn't realize I
could change the "default" value of -20 days.

Ok, so, I whould be ok, but I still have some questions, re: formatting, I
hope you don't mind... I wonder if you realize how many jobs you have saved
by helping us newbies out???

So, I have three files:
Text file
Temporary transfer table
Existing table


My question is, does the field names and data type (long interger, text,
etc) have to be the same in all three files, as well as field size and other
formatting.

I know I can change the field name and data type in the import
specification.
But I'm running into other problems with some fields, for instance in my
temporary and existing table, I have an input mask for the telephone number,
and other fields, but that doesn't seem to work now.

For example, "sex" in the text file is M or F, but my temporary table and my
existing table have it set as numberic, 1=f, 2=m, but it seems that I need to
keep it all text for it to work properly. I thought I needed "sex" numberic
for data analysis, but maybe I can change it at that point?

Thanks again,

 

[Ken Snell \(MVP\)]

You're welcome!

The formats do not need to be the same in all three data locations (text
file, temp table, and permanent table). I usually find that it's best if the
temp table has mostly Text data types because that will prevent "import
errors" when Jet/ACCESS think the incoming data are "wrong".

If you have a text field in the temp table containing "1" or "2" text string
value, it will be changed to the numeric 1 or 2 value in the permanent
table's field when you run an append query that appends that data item into
the permanent table's numeric field. So long as the data in the temp table
can be converted to the data type in the permanent table, all is well.
Sometimes, I will cast the data using a C... function (e.g., CLng, CInt,
CStr, CDate, etc.) just to be sure, but usually this approach is not needed.

Input Masks will not be "applied" to data being added via an append query or
via an import process -- masks get applied only when you manually enter
data. If you need the input mask's format applied to the data for the
permanent table, you'll need to modify the append query so that it "changes"
the data to the desired format. Not knowing your specific needs here, take a
look at Format function and/or concatenating other characters into the data
as part of the append query.