Create an Excel Spreadsheet from a Oracle Database

Question

I have a table in a Oracle database. I have to create a complex spreadsheet structure from the Oracle table. I am looking for best approach to achieve this. Can I use SSIS or use some Oracle utilities to create the spreadsheet.

Any help would be extremely appreciated.

Thanks in advance.

Regards Dibs

Answer 1

I suppose the problem is, just how complex is your "complex structure"?

Programming IDEs such as Oracle SQL Developer or Quest TOAD have wizards to export data table to CSV files.

If you want to join data from multiple tables you could use a view, or even write a SQL statement

select e.ename||','||d.dname
from   emp e
       join dept d on ( e.deptno = d.deptno )
/

(rembering that columns can often contain data which includes commas, so you may want to use a more unusual character - or set of characters - as your separator.)

Another quick way of doing soemthing is to use SQL*Plus's HTML reporting function. Many spreadsheet tools can import well-structured HTML and XML without a snatch. Find out more.

If you want to flatten a hierarchical structure or something even more convoluted you'll probably need to move into PL/SQL. A hand-rolled approach would use a variant of the above statement fitted to use UTL_FILE:

declare
    csv_fh  utl_file.filetype;
begin
    csv_fh := utl_file.fopen('C:\temp', 'data_export.csv', 'W');
    for r in   (  select e.ename, d.dname
                  from   emp e
                  join dept d on ( e.deptno = d.deptno )
                ) loop
        utl_file.put_line(csv_fh, r.ename||'|'||r.dname;
    end loop;
    utl_file.fclose(csv_fh);
end;

If you want to export specifically to Excel (i.e. a .XLS file) then you'll need to go beyond Oracle built-ins. The usual solution for exporting straight from PL/SQL to Excel is Tom Kyte's OWA_SYLK wrapper for the SYLK api. Find out more.

This works with single worksheets. If you want to export to multiple worksheets there are a couple of alternative solutions.

Sanjeev Sapre has his get_xl_xml package. As the name suggests it uses XML to undertake the transformation. Find out more.

Jason Bennett has written a PL/SQL object which generates an Excel XML document. Find out more.