importing large files into excel

[wiskaz]

Hi,

I am trying to import a large file into excel. The file is approx 100,000 lines and is divided into 7 columns by commas. It is an *.asc file.

Is there any more efficient way of importing it besides using TextStream, importing about 10,000 lines and then using Text to Columns, putting the data in an array, then going to the next 10K? This way takes about 2min/100K lines.

I have also tried using Data -> Import External Data -> and then opening the file. Using this, you can set a row to start importing on but the problem is it only goes up to 32,000. My files have over 100K lines and the last rows are cut off this way.

Are there any other ways to import the data to excel?

Your help will be much appreciated.

Thanks.

 

[wiskaz]

Hi Ian,

I'm interested in using your method with excel/access. The Access idea was given to me in an earlier message. It was a solution to store/access more than 65K records (limit of excel).

I am currently using an array that takes its data from a text file using TextStream. This method is very inefficient and takes a long time.

The data is intraday stock prices and in the end I want to figure out P/L using a special formula.

Is there a way to directly access the data from Access in Excel? I know how to put the data in Access, just not how to access record-by-record from Excel.

Thanks for the help.

 

[notinashyway]

i used this >> http://www.cpearson.com/excel/ImportBigFiles.aspx

Thanks Chip!

Cheers
Richard
follow me on twitter > http://www.twitter.com/notinashyway