sql-how to output data from sql query to excel format
I'd like to get the results from a SQL query in Excel format.
For 开发者_StackOverflowexample:
SQL> clear breaks
breaks cleared
SQL> clear columns
columns cleared
SQL> clear compute
computes cleared
SQL> set pagesize 1000
SQL> set NEWPAGE 1
SQL> set linesize 100
SQL> column id format a15
SQL> column tarikh format a14
SQL> column jenis_pengguna format a15
SQL> Ttitle center 'ANALISIS PENGGUNAAN PORTAL BULAN APRIL 2011' skip 1 - center----------------------------------------------------------------------------skip3
SQL> set linesize 100
SQL> break on id skip 2 on tarikh
SQL> compute count of tarikh on id
SQL> SELECT id, tarikh, jenis_pengguna
FROM PENGGUNAAN
WHERE tarikh >= (sysdate)-30
GROUP BY (id, tarikh, jenis_pengguna);
Now, how would I get results that Excel could use or import?
One Excel format you can produce is comma separated value, or CSV. Simply do this with your select:
set feedback off
set linesize 100
set pagesize 0
spool yourfile.csv
SELECT id||','||tarikh||','||jenis_pengguna
FROM penggunaan
WHERE tarikh >= (sysdate)-30
GROUP BY (id, tarikh, jenis_pengguna);
You may have to tweak a few other settings to get a clean data-only output, but this should get you going. You can then read the file with Excel.
精彩评论