Automate Export Data To Excel Using SQL Script

How to automate exporting data to excel using SQL script rather than a table or view?

I am using TOAD 11.0

Thanks

  1. Go to automation designer.

  2. drop in an “Export dataset” action.

  3. double-click the new action and set whatever export options you want.

  4. On the “Dataset” tab, enter your query. Click “Apply” and then close the dialog.

  5. You can rt-click to schedule the action, or run it from command line with -a and the name of the action. Look in the help under “Execute Actions from the Command Line” for more details.

[mention:8f80b602d7b341c180ac08cf275a6479:e9ed411860ed4f2ba0265705b8793d05]

Thanks for pointing to correct answer, much appreciated.

In case I have a script, what command I should add at the end, that allow me to export data from Data Grid in Excel file?

I have solution for you. We covered it in another thread. I'll be back in the office in about 20 mins

On Jul 19, 2017 9:29 AM, "sergheiutm" bounce-sergheiutm@toadworld.com wrote:

RE: Automate Export Data To Excel Using SQL Script

Reply by sergheiutm
In case I have a script, what command I should add at the end, that allow me to export data from Data Grid in Excel file?

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.

SET LINESIZE 2000

SET PAGESIZE 0

SET FEEDBACK OFF

SET TRIMSPOOL ON

SET SERVEROUTPUT ON

SET TERMOUT OFF

SET ECHO OFF

SPOOL path\directory\filename.csv

begin

DBMS_OUTPUT.ENABLE(1000000);

DBMS_OUTPUT.PUT_LINE('Col1,Col2, Col3, etc....'); --your column names separated by commas

end;

/

select col1||','||col2||','|| col3||','|| etc... -- your columns separated by ||','||

from your_table_name

where blahblah blah

;

The saved output is a .csv file that opens easily in excel.

If you want to fully automate it (once you are satisfied with your script results)

put this in a .bat file

@echo off

REM*************************************************************************************

cd\

cd path:\directory\etc

echo Running script so and so......

echo Please wait!!!...

sqlplus username@ORACLE_SID/your_password @your_script_name.sql

start excel output_filename.csv

exit

REM**********************************************************************************************

On Wed, Jul 19, 2017 at 9:28 AM, sergheiutm bounce-sergheiutm@toadworld.com wrote:

RE: Automate Export Data To Excel Using SQL Script

Reply by sergheiutm
In case I have a script, what command I should add at the end, that allow me to export data from Data Grid in Excel file?

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.

Hi;
This is export to “csv”.
“Export to Excel” in Toad has totally different meaning-real Excel file format.

Brg
Damir

Thank you, gene.l.bradley! I need some time to analyze your advice.

Could you please explain what does it mean " where blahblah blah". Not clear, what info I should insert here.

One more question is about “from your_table_name” statement: in my case, the script result is a combination of different columns from different tables. What should I set as a " table name"?

Hello, Damir Vadas. Actually, what I do now, is “Save Grid Contents” in Excel File after .sql file execution. I have to do it each time I need to run the script. I want that the final result of .sql scropt will be the grid export in Excel file.

Start Automation designer, Choose "Export Dataset"

Automation designer is intelligent Toad batch system.

Brg
Damir

Ok, I f you need anything else, just let me know.....

On Thu, Jul 20, 2017 at 3:04 AM, sergheiutm bounce-sergheiutm@toadworld.com wrote:

RE: Automate Export Data To Excel Using SQL Script

Reply by sergheiutm
Thank you, gene.l.bradley! I need some time to analyze your advice.

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.

That was just example..

That whole section is where your script goes.....

The example was just to show you where to put your script

On Thu, Jul 20, 2017 at 3:05 AM, sergheiutm bounce-sergheiutm@toadworld.com wrote:

RE: Automate Export Data To Excel Using SQL Script

Reply by sergheiutm
One more question is about "from your_table_name" statement: in my case, the script result is a combination of different columns from different tables. What should I set as a " table name"?

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.

What is quicker is to right click in the grid area,

export data,

excel instance

Comes up right away.

On Thu, Jul 20, 2017 at 3:05 AM, sergheiutm bounce-sergheiutm@toadworld.com wrote:

RE: Automate Export Data To Excel Using SQL Script

Reply by sergheiutm
Hello, Damir Vadas. Actually, what I do now, is "Save Grid Contents" in Excel File after .sql file execution. I have to do it each time I need to run the script. I want that the final result of .sql scropt will be the grid export in Excel file.

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.

resending with clarification,

Gene

SET LINESIZE 2000

SET PAGESIZE 0

SET FEEDBACK OFF

SET TRIMSPOOL ON

SET SERVEROUTPUT ON

SET TERMOUT OFF

SET ECHO OFF

SPOOL path\directory\filename.csv

begin

DBMS_OUTPUT.ENABLE(1000000);

DBMS_OUTPUT.PUT_LINE('Col1,Col2, Col3, etc....'); --your column names separated by commas

end;

/

select col1||','||col2||','|| col3||','|| etc... -- your columns separated by ||','||

from table1, table2, table3

where table1_key = table2_key

and table1_key = table3_key

and ......etc

;

The saved output is a .csv file that opens easily in excel.

If you want to fully automate it (once you are satisfied with your script results)

put this in a .bat file

@echo off

REM*************************************************************************************

cd\

cd path:\directory\etc

echo Running script so and so......

echo Please wait!!!...

sqlplus username@ORACLE_SID/your_password @your_script_name.sql

start excel output_filename.csv

exit

REM**********************************************************************************************

On Wed, Jul 19, 2017 at 9:28 AM, sergheiutm bounce-sergheiutm@toadworld.com wrote:

RE: Automate Export Data To Excel Using SQL Script

Reply by sergheiutm
In case I have a script, what command I should add at the end, that allow me to export data from Data Grid in Excel file?

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.

Hello Damir. How could I find Automation Designer? Does it a separate module for Toad, or it is a component part of new versions of Toad?

Hello gene.l.bradley

Thank you for clarification. I will try.

ok, let me know

On Jul 20, 2017 8:00 AM, "sergheiutm" bounce-sergheiutm@toadworld.com wrote:

RE: Automate Export Data To Excel Using SQL Script

Reply by sergheiutm
Hello gene.l.bradley

Thank you for clarification. I will try.

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.

hello damir,

would like to ask help on:

  • how i can insert date stamp on the exported file output, e.g. "c:\temp\xxx."date exported".xlsx"?
  • this is to prevent from overwriting existing files in same folder.
  • in my dataset tab, i want to get only the current records on the table dynamically, e.g.
    "select * from mytable
    where date="current record's date only here"
  • this is to ensure only the current records minus the old ones that were extracted will be exported.

hope am making sense?

thanks for the help :slight_smile: