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

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Berkley.

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

Last updated: January 22, 2021 - 10:41 am UTC

Version: SQL Plus Release 12.2.0.1.0

Viewed 100+ times

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 we 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

  (1 rating)

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 :)