Toad World® Forums

TOAD Multiple grid export into one excel workbook

Good Morning All,

I have joined the forum after looking high and low across the net and here to find a solution to my query. I have been teased a few times but its never the answer I am looking for. I will however still state my apolgies if this query has been resolved here.

I run a script which outputs 8 grids in Toad for Oracle 10.6 and I would like them all to export over to Excel on seperate sheets, so I would have 8 sheets in 1 workbook. I have managed to do this by using Excel Instance but surely there must be a faster way instead of doing it 1 by 1. Is there a way of doing this within Toad and not using other applications?

a script or option?

Kind Regards

Maz

A similar question was posted yesterday (www.toadworld.com/…/23989.aspx). I put an example of how to potentially do this in Automation Designer.

If your script is just a series of “select * from table/view”, then you can multiselect and rt-click those tables or views in the schema browser, then right-click and choose “Export Dataset”, and choose the “Excel Instance” export format. That will put each table/view on its own sheet in excel.

Thanks guys, I was looking for Toad to do this automatically however it looks like the best Toad can do is output as Excel Instance or use another application. Thanks for the quick response.

I know this thread is old, but the next beta (12.9.0.26) will have a button on the Script Output tab to export all grids to one Excel file.

This thread is now even older but it still pops up at the top of search results in Google, so thank you for updating with this information!

You’re welcome Caitlin. By the way, an option was added somewhat recently (12.10 or 12.11) that allows the “Excel file” export option to add a new sheet to existing files.

So if you wanted to do this by automation, you could:

  • make the 1st action export a query to excel file (to go on the first sheet in the excel file). Choose the “if file exists…overwrite” option.
  • make the 2rd and subsequent actions export another query (to go on the next sheet in the excel file). Choose the “if file exists…add new sheet” option.

I have it and I am still on 11.6

On Fri, Jul 28, 2017 at 10:47 AM, John Dorlon bounce-jdorlon@toadworld.com wrote:

RE: TOAD Multiple grid export into one excel workbook

Reply by John Dorlon
You’re welcome Caitlin. By the way, an option was added somewhat recently (12.10 or 12.11) that allows the “Excel file” export option to add a new sheet to existing files.

So if you wanted to do this by automation, you could:

  • make the first action delete the target file if it exists
  • make the 2nd action export a query to excel file (to go on the first sheet in the excel file)
  • make the 3rd and subsequent actions export another query (to go on the next sheet in the excel file)…be sure to choose “if file exists…add new sheet” in the Export Dataset action.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.


Gene L. Bradley Jr.

Systems Analyst

Office of Information Technology

Jackson State University

1400 J R Lynch Street

P.O. Box 17750

Jackson, MS 39217

ph 601.979.1042

fax 601.371.9146

email gbradley@jsums.edu

*In God we trust; all others bring data. * ~W.E. Deming

CONFIDENTIALITY STATEMENT

This electronic transmission is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by telephone (601) 979-1042. Thank you.

OK, I went back and checked. It’s been there for exporting to MS Access - MDB and ACCDB files - for ages. And also for ages, you could append to a new sheet with the “Excel Instance” export format. But for “Excel File” - the ability to add a sheet to an existing file - that’s new in 12.9.