I have a text file with about 5 million lines.
Question1: Do I have to break down the text file into sections of 65,536
lines?
Question2: How can I get Excel to copy the contents of the text file on a
sheet, and when it hits 65,536 lines, creates a new sheet and keeps copying?

Depending on how many columns are involved, you are ging to have such a
monstrously large file it may not be manageable.
You will have to split the data into no more than 65,536 rows to paste onto
each sheet. What kind of data source is supplying these 5 million lines?


---
Pops Jackson


"J@Y" wrote:

> I have a text file with about 5 million lines.
> Question1: Do I have to break down the text file into sections of 65,536
> lines?
> Question2: How can I get Excel to copy the contents of the text file on a
> sheet, and when it hits 65,536 lines, creates a new sheet and keeps copying?

Its a text file. it's about 130 mb.
The columns are not a problem.

"Pops Jackson" wrote:

> Depending on how many columns are involved, you are ging to have such a
> monstrously large file it may not be manageable.
> You will have to split the data into no more than 65,536 rows to paste onto
> each sheet. What kind of data source is supplying these 5 million lines?
>
>
> ---
> Pops Jackson
>
>
> "J@Y" wrote:
>
> > I have a text file with about 5 million lines.
> > Question1: Do I have to break down the text file into sections of 65,536
> > lines?
> > Question2: How can I get Excel to copy the contents of the text file on a
> > sheet, and when it hits 65,536 lines, creates a new sheet and keeps copying?

Chip has done this for you; see his page at
http://www.cpearson.com/excel/ImportBigFiles.htm
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"J@Y" <(E-Mail Removed)> wrote in message
newsB0FE50E-9ADA-4BFA-B12C-(E-Mail Removed)...
>I have a text file with about 5 million lines.
> Question1: Do I have to break down the text file into sections of 65,536
> lines?
> Question2: How can I get Excel to copy the contents of the text file on a
> sheet, and when it hits 65,536 lines, creates a new sheet and keeps
> copying?

Microsoft provides a way to import files that won't fit on a worksheet.

But have you thought about what you're going to do with the data after you
import it to 77 different worksheets?

It doesn't sound like it would be a nice thing to work on in excel.

http://support.microsoft.com/default...;EN-US;q120596
XL: Importing Text Files Larger Than 16384 Rows
(written for xl95, but has a note to change stuff for xl97+)

ps. xl2007 supports 1MB rows per worksheet, but that sounds just as bad to me.
I have no idea what you're doing, but are you sure excel should be the
application?

J@Y wrote:
>
> I have a text file with about 5 million lines.
> Question1: Do I have to break down the text file into sections of 65,536
> lines?
> Question2: How can I get Excel to copy the contents of the text file on a
> sheet, and when it hits 65,536 lines, creates a new sheet and keeps copying?

--

Dave Peterson

Since you insist, but chew well before swallowing...
http://support.microsoft.com/kb/120596/en-us
"Text files that are larger than 65,536 rows..."
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"J@Y" <(E-Mail Removed)>
wrote in message
I have a text file with about 5 million lines.
Question1: Do I have to break down the text file into sections of 65,536
lines?
Question2: How can I get Excel to copy the contents of the text file on a
sheet, and when it hits 65,536 lines, creates a new sheet and keeps copying?

I need to columnize the data afterwards in Excel and then run Vlookup like
searches through them, any suggestions as to how I can do it without Excel?


"Jim Cone" wrote:

>
> Since you insist, but chew well before swallowing...
> http://support.microsoft.com/kb/120596/en-us
> "Text files that are larger than 65,536 rows..."
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
>
> "J@Y" <(E-Mail Removed)>
> wrote in message
> I have a text file with about 5 million lines.
> Question1: Do I have to break down the text file into sections of 65,536
> lines?
> Question2: How can I get Excel to copy the contents of the text file on a
> sheet, and when it hits 65,536 lines, creates a new sheet and keeps copying?
>

I need to columnize the data afterwards in Excel and then run Vlookup like
searches through them, any suggestions as to how I can do it without Excel?

"Dave Peterson" wrote:

> Microsoft provides a way to import files that won't fit on a worksheet.
>
> But have you thought about what you're going to do with the data after you
> import it to 77 different worksheets?
>
> It doesn't sound like it would be a nice thing to work on in excel.
>
> http://support.microsoft.com/default...;EN-US;q120596
> XL: Importing Text Files Larger Than 16384 Rows
> (written for xl95, but has a note to change stuff for xl97+)
>
> ps. xl2007 supports 1MB rows per worksheet, but that sounds just as bad to me.
> I have no idea what you're doing, but are you sure excel should be the
> application?
>
> J@Y wrote:
> >
> > I have a text file with about 5 million lines.
> > Question1: Do I have to break down the text file into sections of 65,536
> > lines?
> > Question2: How can I get Excel to copy the contents of the text file on a
> > sheet, and when it hits 65,536 lines, creates a new sheet and keeps copying?
>
> --
>
> Dave Peterson
>

Do you have MS Access or a similar database program. Using VLookups is going
to grind your XL to a halt and very possible crash the application. Access or
suh will easily handle millions of rows of data in an very efficient manner.
--
HTH...

Jim Thomlinson


"J@Y" wrote:

> I need to columnize the data afterwards in Excel and then run Vlookup like
> searches through them, any suggestions as to how I can do it without Excel?
>
> "Dave Peterson" wrote:
>
> > Microsoft provides a way to import files that won't fit on a worksheet.
> >
> > But have you thought about what you're going to do with the data after you
> > import it to 77 different worksheets?
> >
> > It doesn't sound like it would be a nice thing to work on in excel.
> >
> > http://support.microsoft.com/default...;EN-US;q120596
> > XL: Importing Text Files Larger Than 16384 Rows
> > (written for xl95, but has a note to change stuff for xl97+)
> >
> > ps. xl2007 supports 1MB rows per worksheet, but that sounds just as bad to me.
> > I have no idea what you're doing, but are you sure excel should be the
> > application?
> >
> > J@Y wrote:
> > >
> > > I have a text file with about 5 million lines.
> > > Question1: Do I have to break down the text file into sections of 65,536
> > > lines?
> > > Question2: How can I get Excel to copy the contents of the text file on a
> > > sheet, and when it hits 65,536 lines, creates a new sheet and keeps copying?
> >
> > --
> >
> > Dave Peterson
> >

I can't imagine running up to 77 different versions of the =vlookup() through
64k rows would be able to done effectively.

I don't use Access, but it sounds like (from Jim Thomlinson's note) that it may
be time for you to try.

J@Y wrote:
>
> I need to columnize the data afterwards in Excel and then run Vlookup like
> searches through them, any suggestions as to how I can do it without Excel?
>
> "Dave Peterson" wrote:
>
> > Microsoft provides a way to import files that won't fit on a worksheet.
> >
> > But have you thought about what you're going to do with the data after you
> > import it to 77 different worksheets?
> >
> > It doesn't sound like it would be a nice thing to work on in excel.
> >
> > http://support.microsoft.com/default...;EN-US;q120596
> > XL: Importing Text Files Larger Than 16384 Rows
> > (written for xl95, but has a note to change stuff for xl97+)
> >
> > ps. xl2007 supports 1MB rows per worksheet, but that sounds just as bad to me.
> > I have no idea what you're doing, but are you sure excel should be the
> > application?
> >
> > J@Y wrote:
> > >
> > > I have a text file with about 5 million lines.
> > > Question1: Do I have to break down the text file into sections of 65,536
> > > lines?
> > > Question2: How can I get Excel to copy the contents of the text file on a
> > > sheet, and when it hits 65,536 lines, creates a new sheet and keeps copying?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson