parsi_cnu
asked on
TO export oracle query output to excel sheet
I had a strange requirement my table gets appended daily. i Want to get data which is recent from last process time and i want that in EXCEL format.
Can u please help me how to get with it.
example query look like this.
Select * from tablename where creation_date >lastCreation_date
this output i want to capture in Excel sheet.
Thanks
parsi
Can u please help me how to get with it.
example query look like this.
Select * from tablename where creation_date >lastCreation_date
this output i want to capture in Excel sheet.
Thanks
parsi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Another possible solution is retriving the data from excel itself. Just set up an excel query to retrieve the data.
Or you can do this test..
Go to SQL*Plus and type:
SQL> EDIT EXCEL_TEST /* *** Notepad will open up, click 'YES' to create the Excel_Test.sql file **** */
Paste these commands and query in notepad:
--------------------------
SET FEEDBACK OFF
SET TERM OFF
SET ECHO OFF
SET VERIFY OFF
SET TIMING OFF
SET PAGESIZE 0
SET COLSEP ' ' -- <<--- To set the column separator, use the 'TAB' key to create the space between the quotes.
SET LINESIZE 300
SPOOL C:/EXCEL_TEST.XLS --<<--- This is the excel file name
SELECT TABLE_NAME, OWNER, PARTITIONED, LAST_ANALYZED FROM ALL_TABLES WHERE ROWNUM < 10;
SPOOL OFF
SET FEEDBACK ON
SET TERM ON
SET ECHO ON
SET VERIFY ON
SET TIMING ON
SET PAGESIZE 14
SET COLSEP ' ' -- <<--- To set the column separator, use the 'SPACE' key to create the space between the quotes.
SET LINESIZE 80
--------------------------
Close notepad and save your script, then type:
SQL> @EXCEL_TEST
SQL> -- << --- Once you see the sqlprompt again, go to your C:/ drive and find EXCEL_TEST.XLS, you should hava a clean an excel formatted file.
Go to SQL*Plus and type:
SQL> EDIT EXCEL_TEST /* *** Notepad will open up, click 'YES' to create the Excel_Test.sql file **** */
Paste these commands and query in notepad:
--------------------------
SET FEEDBACK OFF
SET TERM OFF
SET ECHO OFF
SET VERIFY OFF
SET TIMING OFF
SET PAGESIZE 0
SET COLSEP ' ' -- <<--- To set the column separator, use the 'TAB' key to create the space between the quotes.
SET LINESIZE 300
SPOOL C:/EXCEL_TEST.XLS --<<--- This is the excel file name
SELECT TABLE_NAME, OWNER, PARTITIONED, LAST_ANALYZED FROM ALL_TABLES WHERE ROWNUM < 10;
SPOOL OFF
SET FEEDBACK ON
SET TERM ON
SET ECHO ON
SET VERIFY ON
SET TIMING ON
SET PAGESIZE 14
SET COLSEP ' ' -- <<--- To set the column separator, use the 'SPACE' key to create the space between the quotes.
SET LINESIZE 80
--------------------------
Close notepad and save your script, then type:
SQL> @EXCEL_TEST
SQL> -- << --- Once you see the sqlprompt again, go to your C:/ drive and find EXCEL_TEST.XLS, you should hava a clean an excel formatted file.
ASKER
Hi
for the question Q_21417979.html i got the answer. But i need column names on the top of columns with tab delimiter.
How to do?
Thanks
parsi
for the question Q_21417979.html i got the answer. But i need column names on the top of columns with tab delimiter.
How to do?
Thanks
parsi
it can be smth like that:
Select col1||chr(32)||col2||chr(3
Select col1||chr(32)||col2||chr(3