importing large text file

[Amy]

I am importing a large text file and not all of the records are being
imported. The fixed width file has about 170 columns and over 1 million
rows. Most of the columns are short text columns, so it appears to be small
enough for the table specifications. I have tried three different ways

1. On a computer with 512 megs of ram and Access 2003 I am able to import
597,298 rows.
2. On a computer with 1 gig of ram and an older version of Access I was able
to import 1,194,596 rows, which is exactly twice as many as above! Don't
know the connection? I thought it was ram related until...
3. A colleague tried to import it using SQL server on a machine with 1 gig
of ram and got 597,298 rows.

How can I be sure I'm getting all of the records into this file?

Thank you

 

[Arvin Meyer]

Starting with Access 2000, the data structure uses Unicode, which consumes 2
bytes instead of 1 for each character. SQL-Server also uses Unicode. That
may have something to do with your problem. Check the size of the database
after the import process. Access 97 and earlier had a 1 GB limit. Access
2000 and later have a 2 GB limit. Each row can have a maximum of 2K of data
+ data in BLOB fields (like pictures and memo fields). You may have reached
the limits.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Amy]

Thank you Arvin. All indications are that you are correct. However, I can
not figure out which limit I have exceeded and what I need to remove to get
below the limit

2 GB limit: My whole database is less than 0.3 GB.
BLOB fields: I have none. Mostly short text fields and a few long integers.
Row 2K limit: Each row is only about 0.5 K based on dividing the size of
the database by the number of rows.

I get exactly the same number of rows imported, regardless of whether I skip
half of the columns or import them all.

This is mystery. Any ideas?

Thanks,
Amy

 

[Jerry Porter]

It's pretty interesting that you get _exactly_ 1/2 the rows in Access
2003 and SQL Server. I have a few questions:
1. Is 1,194,596 the actual number of rows, or are some still missing?
2. Are you using TransferText to import the text file?
3. Are the resulting 597,298 rows the first 597,298 in the file?
4. You tried skipping half the fields. How about doing that into a
table with the skipped fields removed?
5. Are there any unique indexes or other rules that might be preventing
records from being imported (I'd expect an error and and Import Errors
table in this case).

Jerry