76K+ records (Excel 97 +all others)

Topic

New Reply

I have a text file with 76885 records containing 6 columns of data, but an Excel worksheet has only 65536 rows. . This spreadsheet would be circulated to around 24 outside parties who will be using various versions and operating systems.

I realise that I can create two sets of 6 columns in one sheet, two worksheets in one file or two files but if I can create a single one this would be a lot better for the users.

Any ideas?

Reply | Quote

Replies

If the file is going to be used in various versions and OS’es, why not leave it as a text file. Most programs on most OS’es can open text files with fixed width columns and with separators.

Reply | Quote

Excellent (no pun intended Charlotte) idea Hans! In fact I first asked the 24 users what format they wanted. Fourteen said text, six said excel and four said both! .

OK – I have 41% of my customers (whatever happened to the 80-20 rule) that want an excel file and I suspect that at least half of them would struggle trying to stick 76K+ records into a worksheet. The error message you get tells you what happened but not why.

As for Access or Paradox! Well this is a paradox because I don’t have either application !

But I guess the bottom line is that you cannot have more than 65536 rows in a spreadsheet.

Reply | Quote

The 65536 truly is a magic number. While you can split the information over multiple worksheets, it then makes it more difficult to do anything useful with it.

Regards

Reply | Quote

If you are dealing with this many records, I would recommend you bite the bullet and use a database program such as Access or Paradox.

Regards

Reply | Quote

The limit of 65,536 rows is a hard limit and there is no way to exceed it. If some of your customers have older versions of Excel (95 definitely, don’t know about 97), then their limit is going to be 32,768 rows. You have listed all of the things that can be done without going to a database program.

Reply | Quote

Excel 97 limit: 65,536 rows.

Reply | Quote

Prior to XL97: 16384 rows
Quattro pro: some odd million rows….

You can have both text and an excel file if you make a query that searches the text file for records one needs…. (Data, Get External data).

Reply | Quote

Just as another suggestion…

You could try a text file and Excel.

Use MSquery to retrieve the records into Excel.
This still has the limitation of 65536 rows in Excel, however if only a filtered version is shown, it may be sufficient.

Note that this will incur a significant speed trade off as the text fiel will have no indexing to help the querys.

HIH

Reply | Quote