importing large files into excel

wiskaz

New Member
Joined
Sep 8, 2002
Messages
4
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

MarkHenri

Board Regular
Joined
Aug 2, 2002
Messages
106
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.
 

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
wiskaz,

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

wiskaz

New Member
Joined
Sep 8, 2002
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
791

ADVERTISEMENT

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.
 

Anil

New Member
Joined
Aug 23, 2002
Messages
3
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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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
 

Forum statistics

Threads
1,144,117
Messages
5,722,581
Members
422,447
Latest member
knopp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top