sql-how to output data from sql query to excel format

Question

I'd like to get the results from a SQL query in Excel format.

For example:

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?

You have to use a programming language to do that for you. Are you comfortable with any? Maybe PHP/Java/Python/Perl? — user183037
– user183037, Commented Apr 28, 2011 at 2:03
can you elaborate on what you need? Would CSV format be acceptable since Excel can open it or do you need a full excel file type? — Justin C
– Justin C, Commented Apr 28, 2011 at 2:13
You can use a product like TOAD, perform the query right click and export the results to Excel. — Robert Love
– Robert Love, Commented Apr 28, 2011 at 2:15
If you want to export the data as an actual spreadsheet ( .xls rather than .csv) there are a number of PL/SQL tools you can download from the interweb. See my answer to a similar question here: stackoverflow.com/questions/2135798/… — APC
– APC, Commented Apr 28, 2011 at 4:35

DCookie · Accepted Answer · 2011-04-28 02:14:36Z

7

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.

answered Apr 28, 2011 at 2:14

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sql-how to output data from sql query to excel format

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related