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:
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.
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.
22 Comments
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.
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!
I do this all the time it should work. What version are you using?
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?
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
Sorry, you have questions about REM and PROMPT commands?
This is what i want.. Would that work in pl/sql developer?
No idea, completely different tool and company. Is there a reason you can’t use SQL Developer?
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
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
yeah, something is wrong. maybe try a different directory? or upgrade to version 19.4?
Resolved. You need to run the script by pressing the F5 key. So the file is populated. thank of lot
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?
I would guess spool is slower.. But that’s just a guess.
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.
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.
Have I told you lately how much I appreciate you and all you do?
Thanks, Jeff for being wonderful!
~Melody
Ah, thanks! Noted and appreciated 🙂
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.