Read and Manipulate a Text File

  • Thread starter Thread starter Ivan Grozney
  • Start date Start date
I

Ivan Grozney

I cannot figure out how to read in a text file, do some
processing and then populate a table.

We bought the USPS zip code file. I can mindless edit the
file and set it up for import to the database. However,
we get it once a month and I want (would like) to have a
button on a form that reads it in and then as I go through
the lines process the data (sorry I used to be a COBOL and
FORTRAN programmer...)and discard the records I don't need
and suck out the data I want from those I do need.

I cannot for the life of me figure out how to open up a
text file. Everything I try (open file, etc.) fails and
the items in help (OpenTextFile and OpenAsTextStream) I
cannot get to work.

Any help, suggestions or ideas are GREATLY appreciated...
IG
 
Easiest is to use the Open statement in VBA:

Open "C:\MyFolder\TextFileName.txt" For Input As #1

Then use the Input #1 statement to read each line.

Both are covered in Help files.
 
Hi Ivan,

I don't know what the file looks like, but in general the following
techniques work for this sort of problem:

1) Link to the textfile and use an Append or Update query that selects
just the records and fields you want and moves the data to your main
table.

2) If you're familiar with texfile manipulation using other tools,
pre-process the file before you import it so that the file you import
contains only the data you want.
 
Most ZIP files I've seen are fixed length and can be imported using File/Get
external data/ Import
Once done click the advanced tab and note the name of the routine so you can
use it in a DoCmd.TransferText.

Chances are that you get the whole thing each month, so just replace the old
with the new.
If not you will have set the ZIP as a key field, so an append will add all
the new codes.

I don't think ZIP codes chance so either of the above will work.
 
I thank all you for the ideas. I wanted to avoid
prepocessing if possible but if I must I must. Thanks
again.

Ivan
 
Back
Top