Toad World® Forums

Exporting data set from toad to save in excel

We have recently moved to Toad for Oracle 13.1.0.78. We have noticed that

  1. Any SQL query that takes about 5 mins to execute, takes the same amount of time to save into an excel file. We have tuned our queries to be more efficient, but that does not solve the amount of time it takes to save the output. we did not have this issue in the previous versions. Is there any toad settings that we are missing that can easily resolve this?

  2. the Option to save the output defaults to the last saved file location but saves some random file name on the desk top. Any reason why?

  1. We have to do a balancing act between
    a) using the two-way cursor that is tied to the grid where you already ran the query and
    b) re-executing the query in a one-way fashion.

The advantage of a) is that the query does not need to be executed again.
The disadvantage of a) is that all rows must be able to fit in memory, because that's how this kind of query works.

The advantage of b) is that rows are freed from memory after they are exported, so the export process takes much less memory and is faster after the query is re-executed.
The disadvantage of b) is that your query has to re-execute. So if it took 5 minutes to run the first time, well, you're probably looking at another 5 minutes before the export process actually begins.

If your result set is small enough to fit in memory, and you don't want the query to re-execute, then when you get to the "Export dataset" dialog, make sure "Display all results in grid" is checked.

If your dataset is too large to fit in memory, then you'll want that box unchecked (and query will re-execute).

  1. Well, it's not random. We name the file after the table that you are exporting from. That was a change that we made for 13.1. It turned out to a somewhat polarizing feature (some like it, some hate it), so in 13.2, you can right-click in the "file name" edit box and check "auto filename from table" to disable the auto file naming.

In 13.1, you can disable it like this:

  1. In Toad, Go to Options > General. Under “Application data directory” section click on “Open folder”
  2. Keep this folder open in Windows Explorer and Close Toad
  3. In Explorer, browse down to the User Files folder and Toad.ini with a text editor
  4. Add this under [SETTINGS]:
    ExportDataSetAutoFileName=0