Importing large text file ERROR

[J@Y]

I am importing a large text file into Excel using MS's macro (ie: creating
mulitple worksheets)
After about 1 million lines, I get this error message saying I don't have
enough resources to complete the task. I have about 2 gigs for physiscal
memory and 3 gigs of virtual memory. What exactly is the resources refering
to?

 

[meh2030]

I am importing a large text file into Excel using MS's macro (ie: creating
mulitple worksheets)
After about 1 million lines, I get this error message saying I don't have
enough resources to complete the task. I have about 2 gigs for physiscal
memory and 3 gigs of virtual memory. What exactly is the resources refering
to?

You already posted this message not even 30 minutes ago. Be patient
and try not to add more posts than necessary.

I'm not sure how you are interacting with your text file, but keep in
mind that Excel has a row and column limitation to it (depending on
the version of Excel you have). Also, the more data you have (i.e.
lots of row, column data or lots of worksheets), the more resources
Excel needs to keep track of where all of the data is. If you are
adding hundreds of worksheets than you can expect Excel to get bogged
down.

Matt

 

[Jim Thomlinson]

XL's memory is limited and depends on which version of XL you are using...
Check out this link... There is also some good info on calculation. If you
intend to do any calcs against the million records it probably will not work
out for you...

http://www.decisionmodels.com/memlimits.htm
http://www.decisionmodels.com/calcsecrets.htm

 

[J@Y]

First of all, this is a different post from the last. If you were reading
correctly, this is refering to Excel memory issue, not HOW to import large
files.

Second, I said specifically at 1 million lines, thats at number 16 on the
worksheets, that is no where near excessive in terms of worksheets.

However, I have just read something about a maximum of 1 million lines
storable per workbook for excel 2003. Can someone confirm that? And is there
a way around it?

 

[Jim Thomlinson]

The limit is around a million rows. The only way around it is to use a pivot
table which uses a completely different calculation engine and then you can
exceed teh million and still get relatively good performance. To do this you
need to load directly from the data source into the pivot table or to create
a cube...

Is there any chance that you could do what you are wanting to do in Access
or such. XL is really not the right tool for this job (IMO)... It doesn't
matter how hard you hit the nail with the screwdriver, it is still not the
right tool for the job.

 

[J@Y]

Thanks Jim. I'm not familiar with Access but will it be able to do Vlookup
like functions to the data after I have imported it from a textfile?

 

[Jim Thomlinson]

It is a database so you just need to create a table with the values you wnat
to look for and the join that table to the one that you created when you
imported... It is relatively easy but some knowledge of Access would be
mighty handy to have...

 

[NickHK]

I think you should have got the message by now that Excel is not the right
tool for your requirements.
Use a (proper) database approach.

NickHK