Importing a large amount of data from txt file

[jiangming.qian]

I'm trying to import a big txt file (size 20m) to an Access table, and
it told me lots of rows (about 110,000) were dropped due to worksheet
row limit. Does it mean it exceeds the upper limit of one Access
single table? and, how to deal with this problem? Thanks.

 

[Jeff Boyce]

MS Access doesn't have "worksheets".

MS Excel does.

Are you certain you are importing into Access?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I'm trying to import a big txt file (size 20m) to an Access table, and
it told me lots of rows (about 110,000) were dropped due to worksheet
row limit. Does it mean it exceeds the upper limit of one Access
single table? and, how to deal with this problem? Thanks.

Are you using TransferText or TransferSpreadsheet? I suspect the latter may be
wrong. As Jeff says, you shouln't be dealing with worksheets at all if you're
importing directly from text into Access - and there is no explicit row limit
in Access tables (just the 2GByte database limit).

 

[jiangming.qian]

Yes.

After I imported the data, it gave me a error table. The table has 3
columns: "Error", "Field" and "Row".
Under the "Error" column, it shows me "Rows dropped due to worksheet
row limit" for all the 114000 records.

Thanks.

 

[John W. Vinson]

Yes.

After I imported the data, it gave me a error table. The table has 3
columns: "Error", "Field" and "Row".
Under the "Error" column, it shows me "Rows dropped due to worksheet
row limit" for all the 114000 records.

Please post your code, or the steps you took to do the import. What version of
Access are you using?

 

[aaron.kempf]

If you were using SQL Server; then you could have a half-dozen ways to
do this.
a) BCP
b) DTS / SSIS
c) Access ADP

with Access MDB you have one way.. and it sucks. Access MDB just
isn't reliable enough-- with 'real databases' you can use a variety of
tools.

Access MDB is _NOT_ a real database, kid.

 

[jiangming.qian]

I imported the txt file as a "|" delimited file because the columns in
the text file are seperated by "|".
All the field types are "text". No primary key selected. Thanks.

 

[John W. Vinson]

I imported the txt file as a "|" delimited file because the columns in
the text file are seperated by "|".
All the field types are "text". No primary key selected. Thanks.

Fine. That does not answer the question and does not help with the problem,
though!

Again: Please post your code, or the steps you took to do the import. What
version of Access are you using?

 

[aaron.kempf]

I'm not so sure that he is using code.

Most imports can be run just once-- a migration of data from a
spreadsheet to a database.

This is typically true-- especially in the development environment.

It could be that he is literally right click and import.
If that doesn't work--

then I'd reccomend the SQL Server Developers Edition.

SQL Server can import and export data using a wizard-- and it is 100
times more resiliant than MS Access.

Once you have a package; you can extend it.
With Access; you have a black box-- where you cannot see the insides--
and if something doesn't work-- you do not have alternatives.

I can import data into SQL Server using Access ADP, BCP or DTS / SSIS.
When one route seems prohibitively expensive / bothersome-- it's easy
to hop to another direction.

Thats the thing that is nice about using a real database-- it gives
you choices.

SQL Server Developers Edition is $49. You'd be done already; and any
monkey can run the 'import / export' wizard in SQL 2000 or SQL 2005.

Thanks & Good Luck

-Aaron

PS - using the developers edition against SQL Server Express is
entirely free for production. It can support a single processor; but
it will support a quad-core for example. Access MDB won't.

 

[jiangming.qian]

I didn't use any codes... like Aaron said, I just right-clicked and
imported. Chose the txt file, and imported it as a "|" delimited file,
and made a table finally. Then, I got an error table accompany with
the imported table.

Thanks.

 

[John W. Vinson]

I didn't use any codes... like Aaron said, I just right-clicked and
imported. Chose the txt file, and imported it as a "|" delimited file,
and made a table finally. Then, I got an error table accompany with
the imported table.

Try creating the table with the appropriate field datatypes and sizes, and use
the Import Wizard Advanced mode to create in import specification. I don't
know how "worksheets" would be getting involved at all, unless you're
importing the CSV file using the Excel Spreadsheet option (so don't!)