How to export result to different Excel tabs in Toad for Data Analyst?

Question

Does anyone know how to export results from multiple queries to different sheets of the same Excel workbook using report automation in TOAD for data analytics? thank you

Answer 1

I'm not sure you can do this using Toad automatically, but there is a little trick you can do with Excel.

Write the first query and execute it in Toad, then right-click on the data grid of the query results and select "Export Dataset ...", in Excel format, select "Excel Instance" and click "OK". It will open Excel and add one sheet of data from your query.

Repeat the same process for the second query and it will add another sheet to the same document and fill in the data from the second query.

After completing all the queries and adding it to Excel, save the excel document.

If you want to do this completely automatically, there is another solution that you can use to create one Excel document with several sheets that are loaded with data from different queries. Purchase a third-party PL / SQL package, ORA_EXCEL.

Here is an example of how to do this:

BEGIN ORA_EXCEL.new_document; ORA_EXCEL.add_sheet('Employees'); ORA_EXCEL.query_to_sheet('select * from employees'); ORA_EXCEL.add_sheet('Departments'); ORA_EXCEL.query_to_sheet('select * from departments', FALSE); ORA_EXCEL.add_sheet('Locations'); ORA_EXCEL.query_to_sheet('select * from locations'); -- EXPORT_DIR is an Oracle directory with at least -- write permission ORA_EXCEL.save_to_file('EXPORT_DIR', 'example.xlsx'); END; 

It can generate the Excel file and store it in the Oracle directory, or you can get the generated Excel file in the BL / PL / SQL variable so you can save it in a table or create your own process for distributing the file, for example, sending it by e-mail.

You can find more detailed information on the documentation page / product examples: http://www.oraexcel.com/examples

Greetings

Answer 2

I do not think this function exists in TOAD.

The usual solution for exporting directly from PL / SQL to Excel - the Tom Kyte OWA_SYLK shell for the SYLK api - works only with single worksheets. There are several alternative solutions.

Sanjeev Sapre has a get_xl_xml package. As the name implies, it uses XML to perform the transformation. Find out more .

Jason Bennett wrote a PL / SQL object that generates an Excel XML document. More details ...

Answer 3

You no longer need to write code to output data for multiple sheets. As long as your SQL queries are clearly defined (with a semicolon), TDA or now TDP will automatically delete data for different SQLs in different sheets.

Answer 4

I have Toad for Data Analyst 2.6. I use the GO keyword between requests.

 Select * from tableA; GO Select * from tableB; 

This creates two tabs in Excel.