Hi, i am using Excel 2002, and I am wondering what is an efficient way
to import text files with more than 65536 lines to excel (eg. if it's
120000 lines, I want the excel workbook to automatically create two
tabs, with the first tab having the first part of the text file, line
1 to line 65000, the second tab having line 65001 to 120000.)

Right now, i am using a splitter (HJSplit) to first manually split the
large text file into several small files, then i import each file to
an individual tab in a excel workbook. But obviously that's not the
best way.

Thank you very much.

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


Gord Dibben MS Excel MVP

On Tue, 22 Jul 2008 10:52:17 -0700 (PDT), MCI <330006@gmail.com> wrote:

>Hi, i am using Excel 2002, and I am wondering what is an efficient way
>to import text files with more than 65536 lines to excel (eg. if it's
>120000 lines, I want the excel workbook to automatically create two
>tabs, with the first tab having the first part of the text file, line
>1 to line 65000, the second tab having line 65001 to 120000.)
>
>Right now, i am using a splitter (HJSplit) to first manually split the
>large text file into several small files, then i import each file to
>an individual tab in a excel workbook. But obviously that's not the
>best way.
>
>Thank you very much.

See http://www.cpearson.com/excel/ImportBigFiles.aspx for code that will
import a text file of any size, automatically moving from one sheet to the
next.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"MCI" <330006@gmail.com> wrote in message
news:699a465f-ede2-48ce-ba80-8574f027c7e1@r66g2000hsg.googlegroups.com...
> Hi, i am using Excel 2002, and I am wondering what is an efficient way
> to import text files with more than 65536 lines to excel (eg. if it's
> 120000 lines, I want the excel workbook to automatically create two
> tabs, with the first tab having the first part of the text file, line
> 1 to line 65000, the second tab having line 65001 to 120000.)
>
> Right now, i am using a splitter (HJSplit) to first manually split the
> large text file into several small files, then i import each file to
> an individual tab in a excel workbook. But obviously that's not the
> best way.
>
> Thank you very much.

Also, http://www.cpearson.com/excel/imptext.htm


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Gord Dibben" wrote:

> http://support.microsoft.com/default...;en-us;Q120596
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 22 Jul 2008 10:52:17 -0700 (PDT), MCI <330006@gmail.com> wrote:
>
> >Hi, i am using Excel 2002, and I am wondering what is an efficient way
> >to import text files with more than 65536 lines to excel (eg. if it's
> >120000 lines, I want the excel workbook to automatically create two
> >tabs, with the first tab having the first part of the text file, line
> >1 to line 65000, the second tab having line 65001 to 120000.)
> >
> >Right now, i am using a splitter (HJSplit) to first manually split the
> >large text file into several small files, then i import each file to
> >an individual tab in a excel workbook. But obviously that's not the
> >best way.
> >
> >Thank you very much.
>
>

I'd continue to use HJSplit (maybe even mechanize it in a macro).

Then record a macro that would open both text files and parse them the way I
want.

Then when I needed to do it again, I'd run the macro (or HJSplit and then the
macro).

MCI wrote:
>
> Hi, i am using Excel 2002, and I am wondering what is an efficient way
> to import text files with more than 65536 lines to excel (eg. if it's
> 120000 lines, I want the excel workbook to automatically create two
> tabs, with the first tab having the first part of the text file, line
> 1 to line 65000, the second tab having line 65001 to 120000.)
>
> Right now, i am using a splitter (HJSplit) to first manually split the
> large text file into several small files, then i import each file to
> an individual tab in a excel workbook. But obviously that's not the
> best way.
>
> Thank you very much.

--

Dave Peterson