How do I import a huge text file into Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I attempt to import a text file, I get the error message: "File
'C:\Documents and Settings\Administrator\My Documents\SALESPROD2.TXT' does
not contain data." It is a large text file, 2.14 GB. Before you say the
file's too large, I did check the Access specifications and discovered the
max database size is 2 GB. However, I believe this file--when imported into
Access--will only be around 1.8 GB. I use as my example the SALESPROD1.TXT
file which was 1.10 GB yet, after importing, created just a 811 MB Access
database.

If the reason is in fact that the text file is just too darn big, then how
can I get around that? Is there a way I can split up the text file first with
some type of text editor that can handle huge files like that? (I don't want
to have to buy and learn another database program because the report I'm
working on is due in two days.)
 
Hi Bill,

I'm not certain, but previous experience suggests that Access's standard
import routines simply won't handle a file larger than 2 GB.

There are several ways of splitting up the file before importing, using
command-line utilities developed in the Unix world for managing data in
text files. In my experience, huge text files often contain a lot of
data that needn't be imported at all (e.g. you may only be interested in
some of the fields, or only in some of the records). They are also
usually not normalised, so can be broken down into separate related
files each of which is much smaller.

You can download Windows versions of the Unix text utilities from
http://unxutils.sourceforge.net/ . They include

- cut, which can extract specified fields from a file
- split, which splits large files into smaller ones (e.g. every 500,000
lines)
- grep and egrep, which can extract lines that meet certain criteria.
- sort and uniq, which together can get rid of duplicated lines.

For more information, go to Google advanced groups search and look for a
thread with the subject "Importing comma delimited text file" in
microsoft.public.access.externaldata on 18 Feb 2003.
 
Back
Top