• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 578
  • Last Modified:

Importing large text file to Access 2003 table

hi,

I am importing a large text file to a Access 2003 table. The file is 44,697kb and have probably 1.2 millions rows. It couldn't import succefully and it showed error message saying my computer is out of disk space. But the DB is on the D drive which has 61GB free space.

i really don't undersand. Please help.

thanks a lot.

kate
0
kate_y
Asked:
kate_y
  • 4
  • 3
  • 3
  • +1
5 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Access 2003 files have a size limitation of 2Gb.  With 1.2 million rows of 44,697kb, you may be hitting it.
0
 
rockiroadsCommented:
You may have to split the txt file up into smaller files and try

You may of hit some kind of limit on Access, not sure.

0
 
kate_yAuthor Commented:
but it said my computer is out space not the database. After i imported the partial (lost 18 records), the DB's size is 877,678kb.  It is not close to 2GB yet.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
rockiroadsCommented:
No, its nothing to do with how much disk space u have, its limitations to do with Microsoft Access
I think if u try splitting that file, say in 2 or 3 parts, then try it, see what happens

0
 
kate_yAuthor Commented:
ok. but how many rows can it store in one table? i read somewhere saying 100,000,000. is it tue
0
 
prosh0tCommented:
I've run into the same error when importing large text files.  But... it gives you the option to continue 'Your computer is out of disk space...  you won't be able to undo this command.  Do you want to continue anyway'  Just press 'yes' and you'll be ok and should be able to import the text file anyway.  I don't know why it does this.. some weird bug in access.  I think it's because it tries to store all the data in memory for the 'undo' command.
0
 
kate_yAuthor Commented:
yeah, you are right. i did the same thing. but when i run query to tables, it took forever to calculate the result.
0
 
prosh0tCommented:
Did you add indexes on the right rows?  If you didn't, go into the design of the table and for the columns that you often do queries on, for the 'indexed' field, select ('yes (duplicates ok)').  If the fields aren't indexed, the queries will take forever.  If they are, they'll be very quick.
0
 
prosh0tCommented:
Oh and by the way, after you add indexes, and re-import, the importing process will take a lot longer because of the added data it must organize and store.  But it's worth it because of the time it saves you when doing queries.

0
 
rockiroadsCommented:
good luck with it kate
0
 
kate_yAuthor Commented:
thanks a lot. I will have a lot questions on Access VBA lately....:-)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now