i need to import data from a .asc file into excel. the problem is that i need
to import just over 80 000 lines & excel only allows the first 65 500 lines
to import. how do i get the remaining 15 000-odd lines onto the next
spreadsheet?

Possible alternative?

If you intend to ultimately cull/summarize the data, would you consider
importing the file into MS Access? Then you could us MS Query from Excel to
extract only the data you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"paulp" wrote:

i need to import data from a .asc file into excel. the problem is that i need
to import just over 80 000 lines & excel only allows the first 65 500 lines
to import. how do i get the remaining 15 000-odd lines onto the next
spreadsheet?

If you use the Data Import Wizard to bring the file into Excel, there
is a setting for "Start Import at Row". If this is set to 1 then Excel
will import as many rows as it can starting with this (i.e. 65536 or
64k). However, you can change this value up to 32k, so if you import
with this setting then you will get rows from 32k to 80k into another
worksheet - there is obviously an overlap of records, but it is quite
easy to delete the first 32k records in the second sheet to give you
the remaining 16k records. Using this technique you can import files of
up to 96k records.

Note that my "k" refers to 1024.

Hope this helps.

Pete

paulp wrote:

i need to import data from a .asc file into excel. the problem is that i need
to import just over 80 000 lines & excel only allows the first 65 500 lines
to import. how do i get the remaining 15 000-odd lines onto the next
spreadsheet?

i've tried this out & excel will only import up to the 65 500th line & not
import anything beyond that. for eg., i started the import at line 32 000,
hoping for every line to the 80 000th to be imported. however, only up to
line 65 500 was & i did open a new document (book) to import the second set
of data.

"Pete_UK" wrote:

If you use the Data Import Wizard to bring the file into Excel, there
is a setting for "Start Import at Row". If this is set to 1 then Excel
will import as many rows as it can starting with this (i.e. 65536 or
64k). However, you can change this value up to 32k, so if you import
with this setting then you will get rows from 32k to 80k into another
worksheet - there is obviously an overlap of records, but it is quite
easy to delete the first 32k records in the second sheet to give you
the remaining 16k records. Using this technique you can import files of
up to 96k records.

Note that my "k" refers to 1024.

Hope this helps.

Pete

paulp wrote:

i need to import data from a .asc file into excel. the problem is that i need
to import just over 80 000 lines & excel only allows the first 65 500 lines
to import. how do i get the remaining 15 000-odd lines onto the next
spreadsheet?

I use XL2000 and I have done this many times with large files, so I
don't know why it doesn't work for you.

I remember seeing a macro on the Microsoft support site which could be
used to import very large files - it just chopped the data up into 64k
segments on different sheets in the same workbook, so you could do a
Google search for that.

You could also consider a move to XL2007, which has up to 1 million row
capability.

Hope these suggestions help.

Pete

paulp wrote:

i've tried this out & excel will only import up to the 65 500th line & not
import anything beyond that. for eg., i started the import at line 32 000,
hoping for every line to the 80 000th to be imported. however, only up to
line 65 500 was & i did open a new document (book) to import the second set
of data.

"Pete_UK" wrote:

If you use the Data Import Wizard to bring the file into Excel, there
is a setting for "Start Import at Row". If this is set to 1 then Excel
will import as many rows as it can starting with this (i.e. 65536 or
64k). However, you can change this value up to 32k, so if you import
with this setting then you will get rows from 32k to 80k into another
worksheet - there is obviously an overlap of records, but it is quite
easy to delete the first 32k records in the second sheet to give you
the remaining 16k records. Using this technique you can import files of
up to 96k records.

Note that my "k" refers to 1024.

Hope this helps.

Pete

paulp wrote:

i need to import data from a .asc file into excel. the problem is that i need
to import just over 80 000 lines & excel only allows the first 65 500 lines
to import. how do i get the remaining 15 000-odd lines onto the next
spreadsheet?

paulp

Couple of choices other than the one given by Pete_UK

1. break the file into two files and import one to Sheet1 and other to Sheet2

2. use the macro found here.

http://support.microsoft.com/default...;en-us;Q120596


Gord Dibben MS Excel MVP

On Tue, 23 Jan 2007 09:31:02 -0800, paulp
wrote:

i need to import data from a .asc file into excel. the problem is that i need
to import just over 80 000 lines & excel only allows the first 65 500 lines
to import. how do i get the remaining 15 000-odd lines onto the next
spreadsheet?