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.

 

[MarkHenri]

Click on Help and type in limits and you'll see that an Excel spreadsheet can only have a maximum of 65,536 rows and 256 columns.

You need a database my friend. Try Access instead.

 

[wiskaz]

Thanks for the reply,

How would you link excel to the access file?

 

[Jim North]

wiskaz,

What is your purpose for bringing it into Excel? (This will affect the response.)

 

[wiskaz]

Hi Jim,

The purpose of bringing it into excel is to put the data in an array and run it through a VBA program to get some results.

Do you know if there is a way to bypass the array and just read it from access?

Thanks.

 

[Ian Mac]

On 2002-09-10 11:34, wiskaz wrote:
Hi Jim,

The purpose of bringing it into excel is to put the data in an array and run it through a VBA program to get some results.

Do you know if there is a way to bypass the array and just read it from access?

Thanks.

What results do you need that aren't available in Excel? There are lots I'm just wondering if it can be done that way also.

If you could tell us what you want to achieve as an end result you could get a different appoach you hadn't thought of.

Also you could split the data over a couple of pages by setting Criteria in MSQuery and then running it again using different ones.
i.e. ID > 1 < 65000
then ID > 65000 < etc.

Another thing you may be able to do is pass the entire array to Excel, you won't be able to place it on the sheet but you could run code round that. (this maybe fantasy as I'm not great at VBA, but in theory it could be done.)

If you need more info ask.

Cheers,

 

[Jim North]

I know very little about access... but I know you can use VBA in it. Is there anyway you can bypass Excel and still get what you want?

Excel has a worksheet limit of 65,536 rows (as you have already been told). There are two approaches for work arounds that I can think of. 1. Fill the first worksheet, then go to the next worksheet. 2. cut the data up into multiple columms.

 

[Ian Mac]

[No message]

 

[Anil]

what a coincidence, I just posted a similar question :

--------------------------------------------------------------------------------
hi all,

maybe you can give me a hand with the following.

I used the following examples http://www.cpearson.com/excel/imptext.htm to import a textfile into a worksheet, do some calculations with it and export the result into another textfile.

Now I get into trouble when the original file is too large (over 65000 lines), since excel will not be able to handle that.

What I would like to do is import the first bunch of lines (lets say 50.000), do the calculations and export the result to result.txt. Then import the next set of 50.000 lines, do the calculations and append the result to result.txt, etc. until I have processed the complete input file.

Any tips/solutions ?

Many thanks.

 

[Ian Mac]

On 2002-09-10 12:54, Anil wrote:
what a coincidence, I just posted a similar question :

--------------------------------------------------------------------------------
hi all,

maybe you can give me a hand with the following.

I used the following examples http://www.cpearson.com/excel/imptext.htm to import a textfile into a worksheet, do some calculations with it and export the result into another textfile.

Now I get into trouble when the original file is too large (over 65000 lines), since excel will not be able to handle that.

What I would like to do is import the first bunch of lines (lets say 50.000), do the calculations and export the result to result.txt. Then import the next set of 50.000 lines, do the calculations and append the result to result.txt, etc. until I have processed the complete input file.

Any tips/solutions ?

Many thanks.

Hi,

Have you ever used MSQuery?
It's under Data>Get External Data>Create New Query.

Second, does your data have a URN or some way of querying the data into chunks? i.e. Date etc.


_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-09-10 23:23