Not able to import a 2 gig text file into new ACCESS table--error

[rickym]

I've successfully imported several text files into an ACCESS database,
creating new Tables for each of them. Each of these text files were under 2
gig. I'm trying to import a text file that is over 2.1 gig, but each time I
do that I get the following error message: the text file "does not contain
data". Yet, there is data in the text file (2.1 gig). Is there a limitation
to the size of an imported text file? Thanks...

 

[John Nurick]

Hi Ricky,

AFAIK the code in the text ISAM that the Jet database engine uses to import
or export text data has been around for many years, and the designers may
have assumed that no text file could ever be that big. Also, the maximum
possible size of an Access mdb file is 2GB, so if your 2.1GB text file
actually contains that much data there's no way you'll be able to import it.

Fortunately, most huge text files are fixed width and contain huge amounts
of padding and they also usually contain lots of redundant non-normalised
data, so the thing to do is to use text-file tools to reduce your behemoth
to something that Access can handle. Post back here with some information on
the record structure and I'll suggest how to go about it.

 

[rickym]

Hi John--

It's a pipe delimited file, containing over 8 million records. We've also
standardized the data using First Logic. So, it appears to be at it's
minimum in size. It is recommended to me to break the text file into 2 text
files and create 2 different databases. When querying the database, I'll
have to query both databases to get the complete results....Thanks....Ricky

 

[John Nurick]

Standardised, or normalised?

Hi John--

It's a pipe delimited file, containing over 8 million records. We've also
standardized the data using First Logic. So, it appears to be at it's
minimum in size. It is recommended to me to break the text file into 2
text
files and create 2 different databases. When querying the database, I'll
have to query both databases to get the complete
results....Thanks....Ricky

 

[rickym]

standardized--for example, street, strt., str, st all standardized to st.

 

[John Nurick]

You still haven't said a word about the structure. Is it capable of
being normalised into two or more smaller tables?

Also, do you need to import all the fields, or all the records? There
are text file tools that can filter only the stuff you actually want
into a smaller file that Access can import.

 

[Douglas J. Steele]

Sorry to interrupt, but what are the odds that a 2 gig text file will fit in
a 2 gig MDB file? Everytime I've imported text files, the resultant MDB is
larger than the original text file/

 

[John Nurick]

Hi Doug,

In the case of a fixed-width file, pretty good because they are often well
over 50% padding.

Delimted files don't (or at least shouldn't) have padding, but IME big text
files are usually non-normalised and it's often possible to normalise them,
using text file tools, before import. But it all depends just what's in the
data.