Problem: I have 3 queries I want to run. I want the end result to be a single spreadsheet that has all the query results included.

This problem may sound familiar to you, I have talked about how to do this with the GUI here – but you get one workbook per exported TABLE.

The problem with my previous ‘solution’ is that you would need to code your queries to database VIEWs and then export those.

Here’s a quick and dirty way to get everything you want to an ‘excel’ file. You’ll get a CSV file, which you can then open in Excel and convert if you’d like.

My queries are simple, number of rows are small – to make the post simple, but you can substitute your stuff and should be A-OK.

cd c:\users\jdsmith
SET sqlformat csv
SET feedback off
SET echo off
spool blog_queries_excel.csv
SELECT * FROM regions;
SELECT * FROM locations;
SELECT * FROM departments;
spool off

Execute this code in SQL Developer with F5 or in SQLcl..and your output will look like so, when opened in Excel:

Ta-da.

What does the code do?

cd tells us where to read and write for working with files (it changes the SQLPath essentially)

set sqlformat csv tells us to take the ouptut and put it through our formatter, using the csv style.

set feedback off tells us to suppress messages like ’27 rows selected’ or ‘table created’

set echo off tells us not to include the query that was executed in the output

spool tells us to copy the output to a file

If you want to suppress the output in the console or script output panel and JUST write to the file, then do this

But Jeff, I want lines between my tables…

Then change the script…turn feedback back on, or use PROMPTs or simply select the whitespace as desired into the output.

I hope this helps!

End of day, when you’re done with your file, and you’re in Excel, you’ll start cleaning it up IN EXCEL. All of this I’ve just shown you is just a kickstarter to get the data into the file that much faster.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

22 Comments

  1. Hi Jeff,

    Is there a SET SQLFORMAT xlsx?

    I’ve used the Export feature via right clicking the output and selecting Export -> Format=excel 2003+ and also checking the “Query Worksheet Name” option. Then put in my output file and click Next. The files open in Excel as expected and varchar columns with numbers show as characters, leading zeros are retained.

    I’ve tried the above code with SET SQLFORMAT csv. I get a csv file. But when I use the GUI Export I get an Excel file. With better formatting and multiple worksheets.

    Can you point me somewhere to help figure this out?

    Best Regards,
    Rob

    • We can’t spool pdf or excel files, only the text based ones, so CSV is way to go.

      You can use the cart and sdcli to to automate exports of tables and views to excel files though.

  2. Thanks. Followed the steps above but I get this error:

    >>Query Run In:Query Result

    The csv/Excel gets create in the right place but is empty!

  3. thomas TOALE Reply

    Jeff,
    Something I struggle with often iw hen my data is numeric and it is greater than 15 digits. normally the csv file looks fine but when I open in excel it in exponential notation. I get around that by transform in excel. Agin the csv the data is good.

    However, when I spool to sqlformat csv I get the data looking like expo in the csv file so I can’t even transform into excel successfully.
    Any way around that?

  4. I just tried this for the same SQL statement for different sets of data and it was pretty slick. I have noticed other scripts that I run that have REM and prompt. Do you have blog posts in regards to those?

    Chad

    • No idea, completely different tool and company. Is there a reason you can’t use SQL Developer?

  5. I run the spool command, the file is created but there is no data

    • Show me..

      Try some something like

      Set sqlformat csv
      Spool c:\file.csv
      Select 1,2,3 from dual
      Spool off

    • Maurizio

      I have tried these commands:
      Set sqlformat csv
      cd ‘C:\test’
      spool ‘file.csv’
      Select 1,2,3 from dual;
      Spool off

      16/04/2020 09:25 0 file.csv
      1 File 0 byte

      file is empty.
      I use sql developer versione 19.1.0.094 Build 094.2042

    • Maurizio

      Resolved. You need to run the script by pressing the F5 key. So the file is populated. thank of lot

  6. Vijayadevan Reply

    Hi Jeff,

    Great post! I have one question. The whole process of spooling a .csv file, is it going to be slower/faster than the SQL Developers own ‘Export’ option for creating the query result as a .csv file, or they both are same?

  7. Outstanding knowledge you have here! – I’m very grateful I found your blog and by reading and trying, I get some knowledege to understand better how use SQL Developer and PL/SQL in my current job position (and why not, maybe in other job positions too).
    Thank you for share your knowledge.

  8. Matthew Holloway Reply

    Storing this away in my rainy day file. I won’t need this often but this is looks way less painful than what I was doing.

  9. Have I told you lately how much I appreciate you and all you do?

    Thanks, Jeff for being wonderful!

    ~Melody

  10. Hi Jeff,
    Great post and here is an unrelated question I haven’t found an answer to.
    Is there a way to share a report and make it, the SQL, not editable?
    I trust the end users, the DBAs do not.
    I thought possibly keeping the file in a shared folder and restricting permissions on the folder.
    The Reports have Bind variables so each viewer could run it for their data without changing the underlying SQL.
    Is this possible? The higher level question not necessarily my thought of how to do it.

    • Sure, create your report in the database as a view or stored procedure…or an APEX Application.

Write A Comment