Skip to Main Content
  • Questions
  • Spooling data to .csv file via SQL Plus

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Berkley.

Asked: January 20, 2021 - 2:21 pm UTC

Last updated: October 02, 2023 - 11:39 pm UTC

Version: SQL Plus Release 12.2.0.1.0

Viewed 10K+ times! This question is

You Asked

It is probably apparent from my job title that my role is far from a dba. I am, however, a frequent user of Oracle SQL Developer. I work in mass appraisal and use SQL Developer to select, update, insert, delete, etc., on a regular basis. I would like to bring some automation to some of the select statements that I run most frequently and spool the results to a .csv file. An example of this is a select statement for identifying recently sold properties that need to be reviewed.

The following command [using the Windows Command Prompt] has been stored as a scheduled task [using Windows Task Scheduler]:

G:\>sqlplus username/password@connection @G:\SALES_VALIDATION\bat_files\weekly_salesval_rev.sql

Weekly_salesval_rev.sql is the script containing the spool commands and select statement. I have included a link so you can view the script.
What command [or commands] can I incorporate so that the data will be formatted appropriately [and include column headings] for review in Excel? While there should be approximately 21 columns of data, the results are currently displaying in a scattered fashion within the first 3 columns of the .csv file.

If you need any other detail, please let me know. Also, if you would suggest or recommend other approaches to automating frequently run SQL select statements, let me know and I would be glad to look into those alternatives. Thank you for your time and help!

Berkley Rose


Product Versions:

SQL Plus Release 12.2.0.1.0

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0

Oracle SQL Developer 4.0.3.16

Windows Version 10.0.17763.1577

with LiveSQL Test Case:

and Chris said...

From 12.2, SQL*Plus has a

set markup csv on


command. As the name suggests, this returns the output in CSV format:

SQL> set markup csv on
SQL> select * from co.customers fetch first 10 rows only;

"CUSTOMER_ID","EMAIL_ADDRESS","FULL_NAME"
275,"elden.bufton@internalmail","Elden Bufton"
276,"chassidy.hinderaker@internalmail","Chassidy Hinderaker"
277,"broderick.faur@internalmail","Broderick Faur"
278,"jewel.ginnery@internalmail","Jewel Ginnery"
279,"james.williams@internalmail","James Williams"
280,"james.wilson@internalmail","James Wilson"
281,"mary.wilson@internalmail","Mary Wilson"
282,"patricia.wilson@internalmail","Patricia Wilson"
283,"james.smith@internalmail","James Smith"
284,"zavert.zimmer@internalmail","Zavert Zimmer"


This should solve your Excel formatting woes.

https://blogs.oracle.com/opal/fast-generation-of-csv-and-json-from-oracle-database

SQLcl has a similar option with the

set sqlformat csv

command. This includes a whole bunch of other formats for your exporting pleasure

https://www.thatjeffsmith.com/archive/2015/02/a-quick-4-1-trick-set-sqlformat/

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Spooling Data to .csv file

Berkley Rose, January 21, 2021 - 9:26 pm UTC

set markup csv on worked perfectly. Thank you!!!
Chris Saxon
January 22, 2021 - 10:41 am UTC

Great :)

Is COLUMN NOPRINT working?

Maritn, August 24, 2023 - 12:50 pm UTC

Hello
I can't get "COLUMN col_n NEW_VALUE col_n_var NOPRINT" working with MARKUP CSV. So I don't want col_n to be spooled to the file, but want the value to be kept in col_n_var.
If I run the sql without markup, it's working, but I have the blanks of the columns in my export...
What ever. The requirement I have is to append a trailing row to the export, containing the number of exported rows. Is there a variable which stores the number of rows (so the n of "n rows selected")?
Connor McDonald
October 02, 2023 - 11:39 pm UTC

I assume you're doing something like

select cols, count(*) over ()
from ...

In order to grab that total count at the end of the spool? In terms of efficiency, carrying a total row count throughout the query seems pretty expensive to me

I'd be more inclined just to do something like:

myscript.sql
============
set termout off
set feedback off
set pages 0
set markup csv on
spool c:\tmp\emp.dat
select * from scott.emp;
spool off
host ( awk "END {print NR-2}" c:\tmp\emp.dat >> c:\tmp\emp.dat )