I want to export oracle table into an Excel sheet side by side

Question

I have few oracle tables and I want to fix them into a same excel sheet side by side using spool command.

Can I use cell reference inside spool command while trying to export those tables into a excel file? Thanks in advance.

Answer 1

sqlplus user/pass@db

SQL>set markup csv on quote off;
SQL>spool output.csv
SQL>SELECT * FROM table;
.......
SQL>spool off
SQL>exit

Now open in Excel, and it should prompt you to convert what it sees as a text file. Tell it to use commas as the field delimiters, and it will import into a spreadsheet. Or open in a text editor and change commas to tabs, and then you can even copy/paste the contents directly into Excel and it will paste correctly into the rows/columns automatically.

If you have commas in your data, you can change the delimiter to another character that you don't have:

set markup csv on delimiter | quote off;

Or even more easy than that, simply query the table in the database client software of your choice, configured to copy with tab delimiters (if it gives you this option). Using DBArtisan, I simply highlight my results, ctrl-c, and ctrl-v into Excel and it works perfectly. No need to go through SQL*Plus and spool to file. I imagine other popular clients can do the same.

It is possible to create an Excel XML document using PL/SQL which you can email to yourself, but that's a lot more complicated than the above.

Answer 2

You tagged sql developer, you can use the cart to export your tables to single Excel file, each table will get it's own Worksheet

Set output options, and voila