Limits to Access?

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

Mark

Are there limits as to the number of rows access can
import?

I have a CSV file that contains over a million rows of
data. Excell barfs at 65,500 rows. I do not own access,
and do not want to purchase it without the assurance it
will be able to take in all my data.

Any thoughts?

Mark
 
Access can probably handle it.
Restrictions include a total space of 2GB and 255 fields per row.

I think the amount of data per row is also limited to either 2Kb or 4KB.
I don't recall OTTOMH.

You should do a test import on a friend's system and see if it works.

I have heard of many cases where Access can store a few million rows.
But that is pushing the upper limits.
Depending on what you plan to do (add more data?, analyze existing data?)
you may need to move to SQL Server.
 
Hi Mark,

Supplementing what Joe has said: I've imported CSV files with more than
two million records. The following precautions seem wise:

-Create the table in Access first with the correct field types. Make
sure that there is no primary key and no indexes: if Access is indexing
the data as it imports it, the process will be much slower and there is
more chance of exceeding the 2GB limit and of things going wrong
generally. After importing, compact the database and then create the
indexes you need.

-Have both the text file and the database on a local drive; doing this
across a network makes it take much longer and increases the chance of
problems.

Remember that most problems with importing text files are caused by
irregularities in the file or the data (e.g. incorrect line breaks, a
missing or superfluous quote mark...). I've often had to write Perl or
VBScript scripts to scan a textfile to find glitches like this so they
can be corrected.

Finally, it sometimes helps to pre-process the file at the text file
stage before importing to Access (e.g. to dump records or fields you
don't want, or to split it into manageable chunks). Tools for this
include Perl, VBScript, and the Gnu textutils (Windows versions
downloadable from http://unxutils.sourceforge.net/)
 
Back
Top