Importing Text File into Access

[Guest]

I need help importing a 50MB file into an Access database. There is pattern
to the data. Each record starts with "STMTS" with a carriage return in front
of it. The carriage return could also be the end of the previous record.
There are 16 fields for each record. I think this can be done by writing code
using VBA, but I cannot get the code right. Please Help! Here is a sample of
the data layout in the txt file.

Thanks!
Marianne

STMTS 9999999-01 Timmy County Horses palomino horses for daffy duck
01-JAN-2004 31-MAR-2004 08-APR-2004 daffy
ducky 999999999 daffy A ducky
1111 American Highway street
AnywhereUSA KY 12345 STMTS 9999998-01
Timmy Country Horse palomino horse for daffys duck
01-JAN-2004 31-MAR-2004 08-APR-2004 daffy duck
888888888 daffy J ducky
123 Halfacre parkway
LEBANON MN 12345 STMTS 9999997-01 Timmy County
Horses palomino horses for daffy ducks 01-JAN-2004
31-MAR-2004 08-APR-2004 goofy smithyys 777777777
mickeys ands minne 5555
sMORES CREEK plat finely
NJ 12345 STMTS 5555555-01 timmy County horses
palomino horses for daffy duck 01-JAN-2004 31-MAR-2004
08-APR-2004 Basin MERRY 444444444 Merry G Basin
222 RTe 666
pagosa springs FL 12345
STMTS .....

 

[Ken Snell [MVP]]

What is the delimiter between each field's data? What is the table structure
into which you want to import these data? How do you know when one field's
data ends and another one's begins?

What have you tried to use so far?

 

[Marshall Barton]

I need help importing a 50MB file into an Access database. There is pattern
to the data. Each record starts with "STMTS" with a carriage return in front
of it. The carriage return could also be the end of the previous record.
There are 16 fields for each record. I think this can be done by writing code
using VBA, but I cannot get the code right.

You could use this to do the job or, at least, see a major
example of how to use File I/O.
http://www.mvps.org/access/modules/mdl0057.htm

 

[Guest]

The fields are fixed length. Below is the Access Table Structure. I have
tried using the Import Wizard in Access. When I select the text file and
click on "Import" button in the Import dialog box Access locks up. No Errors
are given.


Name Type Size
ID Number 4
D Type Text 9
P Number Text 14
P Name Text 71
1stDate Date/Time 8
2nd Date Date/Time 8
3rd Date Date/Time 8
Last Name Text 21
First Name Text 16
2nd ID Text 10
Mailing Name 1 Text 36
Mailing Name 2 Text 6
Address1 Text 36
Address2 Text 36
City Text 21
ST Text 3
Zip Text 15

 

[Guest]

Marianne,

If the import spec wizard is locking up then you have a problem with your
Access or the environment you are running it in. Have you got all the proper
updates installed? Try disabling your Antivirus program temporarily as
another way to try and troubleshoot this problem. If you can get through the
locking up, you are on the way to importing your data. You've got the right
idea.

Good Luck!

 

[Ken Snell [MVP]]

As PSI noted, if the import wizard is locking up, then the problem is with
the database or the wizard.

The file may be corrupted. Have you done a compact and repair on it?

Or the wizard may be having problems. Have you tried a repair of Office
installation?

 

[John Spencer (MVP)]

Also, what is the text file structure/source.

Is the end of each record delimited by BOTH a carriage return and a line feed?
If not, the Access wizard will probably lock as it tries to load ALL the text
into one "record"

IF the text file is being created from a Macintosh, it very will may only have a
carriage return as an end of line marker. Also, I've seen only the carriage
return from files I've gotten from UNIX based systems.