How to automate exporting data to excel using SQL script rather than a table or view?
I am using TOAD 11.0
Thanks
-
Go to automation designer.
-
drop in an “Export dataset” action.
-
double-click the new action and set whatever export options you want.
-
On the “Dataset” tab, enter your query. Click “Apply” and then close the dialog.
-
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:
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:
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:
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:
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:
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:
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:
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 