Microsoft Excel Row Count Limit

Toad for Oracle
1

Hi, I thought Excel 2000 has a limit of 65,536 rows?

But today I tried to save 70,000 rows in Excel 2000 from Toad 9.7.2.5 Grid and
it did it !!!

Has somwething changed or the limit is something else ?

Thanks,

Shiva

2

The limit – forget the number – is at the workbook level. When Toad
hits the limit, it goes on to the next workbook. Maybe this is what you are
seeing?

3

Yes … this is what is happening.

Not sure though , how many workbook it will continue to.

By the way, my query takes 20 minutes to return 9999 ( My OCI array buffer size
) records in Toad Data grid.

And I it has to return app 30,000 records.

Issue is when I try to save this relatively long running query results in Excel
it does not finish !!!

Not even in two hours !!!

Is there away to make it fast ?

Thanks,

Shiv a

4

Hi Shiva,

By the way, my query takes 20 minutes to return 9999 ( My OCI array
buffer size ) records in Toad Data grid.
And I it has to return app 30,000 records.

Issue is when I try to save this relatively long running query results
in Excel it does not finish !!!
Not even in two hours !!!

Is there away to make it fast ?

There's a big "it depends" there. You need to find the bottleneck(s). It
could be the query, the database, the database's server, the network, your
PC, or a combination of them.

So, we'll start with what we know: What's the reason you have such a large
OCI buffer? Does the query perform better or worse with it at the default
of 500? Does your query perform well outside of Toad?

Rich -- [TeamT]

Disclaimer: Wishing Yancey Thigpen was still with the Stealers.

6

Make sure your grid option to load records to grid on the export is turned off.

7

Sorry I do not know how to start a new thread, but I have a question about TOAD that is related to this thread. It has to do with exporting data to Excel. I have a table with more than 256 columns/fields and I want to export the results of a query on that table to Excel but I am getting an error. I know newer versions of Excel(2007 and later) allow more than 256 columns, is there a way to export the results of a query of a table that has more than 256 columns/fields into Excel? Thank you.

8

Either user the “Excel Instance” export format, or, in Toad’s export dialog, make your file extension xlsx rather than xls