• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1406
  • Last Modified:

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
0
parsi_cnu
Asked:
parsi_cnu
1 Solution
 
catchmeifuwantCommented:
Put all these into a file (say MyFile.sql)

------------MyFile.sql -------------------
set feedback off;
set heading off;
set echo off;
set verify off
spool mytable.csv

select col1||','||col2||','||col3||','||col4
from mytable
where last_process_time > sysdate-1

spool off

----------- End of MyFile.sql -------------

From SQLPLUS, login to the DB and execute the file.

1)sqlplus user/pwd@<host_string> @MyFile.sql

2)You'' find the CSV file mytable.csv (You can open this in Excel).

3)Or use toad to select the data and save them as Excel files.
0
 
slightwv (䄆 Netminder) Commented:
Another possible solution is retriving the data from excel itself.  Just set up an excel query to retrieve the data.
0
 
paquicubaCommented:
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.



0
 
parsi_cnuAuthor Commented:
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
0
 
oleggoldCommented:
it can be smth like that:
Select col1||chr(32)||col2||chr(32)||....  from tablename where creation_date >lastCreation_date  
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now