Is anyone else having issues with exporting to excel file? It seems to crash my client for datasets with over 50k rows, but exporting as excel instance works in those cases. There are 8 queries I need to run and save as individual excel files so it would be great to get it work. I’m hoping to ultimately use the automation designer to automate running the queries/saving to excel files.
I just tried exporting 60k to an Excel file from the Schema Browser and it worked fine with Excel 2010 (64 bit Toad 12.12). You mention Automation Designer, is that where you are having the problem? If so, what action are you doing it from? What version of Excel are you using? Does it work if you do it from Schema Browser/Data Grid - r-click -> Export Dataset?
Be sure to uncheck “Display all results in grid” in the export dataset window. It uses a lot less memory that way. 60K rows may or may not be a problem with it checked. It depends on the rows.
No, I’m exporting it via export dataset via editor. I use Excel 2013 (32bit Toad 12.12). My machine is 64 bit, does it matter if I’m using 32bit…?
I got it to work after unchecking display all results in grid, and checking launch after creation. Weird… 100k rows export btw.
When that box is unchecked, Toad runs the query again (separately from the cursor that is tied to the grid).
In the “new” query, after a row is processed and sent to Excel, Toad releases the row from memory.
When that box IS checked, then we use the cursor that is tied to the grid. Grids need to be able to scroll forward and backwards, so we can’t release the rows from memory after they are exported.
There are really only 2 cases where you should leave the box checked.
- Your query took a long time to run AND it does not return very many rows, OR…
- You don’t want to risk getting a different result by running the query again (like if another session changes the data after your initial SELECT)
My machine is 64 bit, does it matter if I'm using 32bit..?
Sorry, I forgot to answer that. No, it doesn't matter.
Okay so I ran a few different scenarios and figured out why it was crashing. It was due to one thing: saving the excel file as .xls. Changing it to .xlsx seems to have fixed the problem.
Thanks for the follow up. In the folder right above your user files folder, the should be a file called “Toad.el”. It’s an error log. Would you mind sending it to me? John.dorlon@quest.com. It might help me fix that problem in the .xls files.