importing large text files -EXCEL- **URGENT!**

hi im new here but please help!

im trying to import a text file with around half a million lines, need to import 65000 lines at a time(due to excel's constraints) onto seperate sheets

i was going to do this by importing first 65000 lines, then importing text file starting at 130000, then importing file starting at 195000 etc on a +65000 increment loop.

however when importing text file the maximum value for "startrow" of text file is 32000 . so what do i do now?

help! code would be handy. the filename is ex0206.txt



a speedy response would be much apreciated!



thankyou!



Jake

 

jake
if you are so hurry.
Use the maximum EXCEL do. Then...
Try to use an OLE call to tone MS-Word insertion. Word support hight capacity of lines. I supposed...
Then you have in one file the limit capacity of EXCEL + WORD. Is it possible ?
I never used in my 40 years of profeesional work "FILES" so "big" like you have.
Good luck.
Lou Bob

 

[nbucska]

What do you do with the text using EXCEL ?? <nbucska@pcperipherals.com>

 

[DanTex]

This is actually a pretty common type of problem. Most people don't realize that &quot;canned&quot; programs like Excel and such usually have limits to their capabilities which render them useless for &quot;hard-core&quot; work with large data sets.

According to Excel 97 ( on my work computer ), the limit is 65536 rows, 256 columns. I'm not sure if you can have the max # of rows and columns simultaneously. I discovered long ago that the 256 column limit rendered Excel uncapable of some of the things I need to do. The max length of a single cell is 32000 characters. Can you have the max in each cell??? I dunno. The default number of worksheets at a time is only 255. Supposedly, this can be increased to the max memory available. However.... I don't know how you do that. Also... For a 1/2 MB file of data... you probably don't have enough memory in your computer to handle your particular file. Even if you did... I believe that most versions of Windows don't know how to use more than 512 MB of memory ( even if you have it installed ). To use more than that... you need to be very good with Windows to tweak it's settings correctly. Of course... every version of Windows out there is different. So... what works with one version may not work with another.

As I believe Nbucska ( above ) has asked... what do you want to do with the data? Excel probably isn't a very viable option for you. You'll either have to find another product with more capabilites or.... write a program yourself using Fortran, C, C++, Pascal, Ada, Mathlab, Basic, .... whatever your poison of choice is.

Dan

 

[tomatge]

I may be way off track here, but I'm puzzled as to why the maximum value of your startrow number is 32000. I'm wondering if this has been defined as a &quot;normal&quot; integer in a VBA macro, and whether defining it as a &quot;long&quot; integer will get you around the problem ????

 

[nbucska]

My experience dictates that if something is difficult, it
is always worth to step back and check if it is necessary
at all.

How do you want to process the data ? what do you want to
do with it ? Wouldn't be it easier to use some other
software or language ? Basic, Fortran, C, TECO , etc ?
<nbucska@pcperipherals.com>

 

[ppham]

I have the same problem. My ASCII text delimited file contains 700 to 1k columns. I'm looking for way to either split the file so it can fits into an Excel sheet, or way to have Excel cut at column 255 to make one file or one sheet, then continue the next 255 for next file or sheet until the end.

 

[etarhan]

There are a number of approximate workarounds descvribed by Microsoft for this problem.
Referring to the MS Support web site,

If you are using Excel 5.x, 7.x or 97, read article Q119770
If you are using Excel 2000, read article Q214015
If you are using Excel 98, read article Q189593

These may be helpful. If they are of no use, my advice is to divide the text file to smaller pieces before importing. An alternative might be to write a small program using a language which can communicate with (export to) Excel, and importing the first n lines, deleting the first n lines, importing the first n lines of the resultant file and so on, until the records are exhausted. Access would be helpful.

Good luck

 

[pipewelder1999]

If this is a delimited text file. MS Access can have a table size up to 1 GB. I would imagine that much data could be imported. The number of field names (Columns) is limited to 255 however if there is a primary key in each row, multiple imports could be done and the previously imported rows could be deselected. The text importing wizard allows you too select/deselect fields.

After that you could prepare a query that would show a recordset of the selected fields however you are still limited to 255 columns. If some of this data is redundant or repeats, the number of fileds could be reduced that were imported.

Gerald Austin
Iuka, Mississippi

http://www.weldinginspectionsvcs.com