MS Excel ?: How do I save a big, big table?

Factual Questions
1

In a text format.

I have a spreadsheet with 80,000 entries. If I open it, and save it, it cuts off at 65,536.

Also, I need it saved as a text file–a converted text file–not just a spreadsheet with a ‘.txt’ extension that has all kinds of embedded junk. I know how to do that on Excel, but I can’t get aroung the 65K limit.

Any ideas???

I’m using Excel 97 and Windows NT.

(I’m largely Excel self-taught, so I apologize if this is something that is taught on the first day.)

2

Technically, this is impossible - Excel has a record limit per table, and you’ve reached it. The data loss occurs when you open the document, which may have been an Excel export from another application that wasn’t smart enough to realize that Excel couldn’t really address the data.
Do you have MS Access? You can you that to import the entire table and then export to a formatted text version using space or tab delimiters (.prn, .txt)

3

Access is definitely the way to go if you have it. If you do, you just just open it and export it to a text file. Piece if cake. Let us know if you have access to Access (I crack myself up).

If you don’t have it, we can try to rig up something really convoluted. Or, you can just e-mail it to me, tell me how you want it to look, and I can send it right back to you. Your choice.

4

You can actually use Excel for this with the help of Visual Basic. I do that quite often. The question is, are you using Excel functions to sort and manipulate the data, or are you just using Excel as the viewer? As a viewer, you could just have separate worksheets for each group of 65K rows (continued on next sheet). And saving a multiple worksheet workbook is no problem, as long as you’re saving it as a workbook. The trick is to open a text file that large in Excel, or to re-save it. To do that, you’d need a VBA macro. You’d open Excel, run the macro (which would read the file and split it into two or more worksheets), and then when done manipulating or looking at the file, you’d run another macro to save the multiple worksheets into one text file.
Something like the following (not complete):

Open:
open <filename> for input as #<fileno>
do until eof(<fileno>)
if r=0 then
activeworkbook.worksheets.add
set ws=activesheet
endif
line input #<fileno>,rec
r=r+1
ws.cells(r,1)=rec
if r>65000 then
r=0
end if
loop
close #<fileno>

Close:
open <filename> for output as #<fileno>
for each ws in activeworkbook.worksheets
rx=ws.cells.specialcells(xlcelltypeastcell).row
for r=1 to rx
print #<fileno>, ws.cells(r,1)
next
next
close #<fileno>