Importing a giant file - need help!

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I have been trying for days to import a very large (450
MB) tab-delimited file into Access. I have downloaded the
file itself to my PC from my company's mainframe and need
to have the ability to read and sort the information. The
file itself originally has an extension of .prod (which is
not recognized by Access). But I have changed the
extension to .txt and I tried .csv to allow Access to
recognize it. The procedure that I'm using is as
follows: Opening a new Blank Database; Naming the
Database; File - Get External Data - Import; locating the
file and clicking on it; click on Import. Access is
continuously giving me an error that says one of my rows
has more than 65,000 records; and reading my file ad fixed
width. If I isolate some records from the file to test,
it works fine. If I try to import it into Excel, the
maximum number of records import fine. But Access will
not read the entire huge file as Tab Delimited and import
it. Am I doing something wrong???
 
Try verifying that your data is one well shaped matrix of
2D (rowXcolumn) by importing it into Excel - all rows and
columns have same number of elements and same format. If
your matrix has more than 255 columns and 65,000 rows,
then cut your matrix into smaller ones that can
eventually "fit" into Access. Do that by cutting and
pasting the extra rows into the next matrix. If you have
more than 255 columns, then (I don't know) get creative.
Use Excel to do a "save as ..." a .csv file.
Try importing the matrix (matrices) just made by Excel.
Access might ask for each columns' format, but don't throw
your monitor at me over that one.
 
Hi Mark,

There are several possible problems.

AFAIK you won't be able to import a file that has more than about 64k
bytes (or characters, I'm not sure which) in any one record.

Also, Access will probably be thrown if there are any linebreaks within
the data in a tab-delimited file, or if any records have more or fewer
fields than they should.

WIth recent version of Access, there can also be problems if the file
uses anything except Chr(13)&Chr(10) (CRLF) between records.

I use little Perl scripts to scan files to identify problems and locate
problems like these.

It's also possible, importing a file that size, for the the indexes and
scratch space and data to approach or exceed the limit for the size of
the mdb file (especially if there's a significant amount of data already
there). The lmiit is only 1 GB for Access 97, and 2 GB for later
versions - but most of this benefit is lost because the later versions
store text as Unicode.

Try removing all indexes from the table you're importing to. Also try
splitting the big file into shorter ones and importing them one at a
time. (I use the "split" utility from Gnu textutils: Windows versions
downloadable from http://unxutils.sourceforge.net/).

Since most big mainframe exports are non-normalised, you may be able to
reduce the amount of data you need to import using Perl scripts or/and
textutils such as "nl", "cut", "sort" and "uniq" normalise the big
textfile into several smaller related tables which can be imported
individually.

Post back if you would like more about any of these.





I have been trying for days to import a very large (450
MB) tab-delimited file into Access. I have downloaded the
file itself to my PC from my company's mainframe and need
to have the ability to read and sort the information. The
file itself originally has an extension of .prod (which is
not recognized by Access). But I have changed the
extension to .txt and I tried .csv to allow Access to
recognize it. The procedure that I'm using is as
follows: Opening a new Blank Database; Naming the
Database; File - Get External Data - Import; locating the
file and clicking on it; click on Import. Access is
continuously giving me an error that says one of my rows
has more than 65,000 records; and reading my file ad fixed
width. If I isolate some records from the file to test,
it works fine. If I try to import it into Excel, the
maximum number of records import fine. But Access will
not read the entire huge file as Tab Delimited and import
it. Am I doing something wrong???
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top