Selective Import w/Access 2002

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

Is there a means to import a text file into Access 2002
but to only select the data rows that contain a specific
value? I have a text file in excess of 40,000 lines, with
each line containing a field value of one of five
possibilities. Each of these possibilities reflect a
completely different layout format.

My workaround so far is to define all five import
speciciations individually, then import the entire file
using each specification (and getting all of the expected
errors), and finally running a query to select only the
data rows with the appropriate element value. Is there a
means to accomplish this selection at the import step
instead of this workaround?

Thanks,
 
Hi Sean,

The standard text import routine always try to import the whole file and
you can't make them import only certain lines. Alternatives include:

1) Set up an import specification that will accept all the variations.
Use this to link or import the textfile to a temporary table; then use a
series of Append queries to extract the various different kinds of
record into the tables where you want them. (If the different record
types are too different this won't be possible).

2) Write VBA code that reads the file line by line, parses each line,
and appends the corresponding record to the appropriate table. This is
the neatest approach if this is going to be a regular task that needs to
be done at the click of a button. It's also good if the file consists of
header and detail records and you need to include a key field from the
header record in the table where the detail records go.

3) Working at the textfile level, split the file into smaller ones, each
with all records structured the same. If it's just a question of
splitting out particular kinds of records, a command-line tool like
"grep" will do it. If you also need to shuffle fields around, it can be
done with a scripting tool such as VBScript or Perl.
 
i don't know what differences might exist between A2K and
A2002 in importing, but here's what i'd say for A2K:

if you're using import specifications, i assume you're
importing into an existing table (not creating a new table
with each import). and the records you want can be
identified by a specific value containing in a specific
field? if so...

open the destination table in design view. go to the field
described above. set a validation rule for that field. for
instance, if the records you want have a value of "X" in
that field, set the validation rule to "X". when you
import, any record that does not contain "X" in that field
will be discarded completely - not saved into the table.

hth
 
Back
Top