How do I spool to a CSV formatted file using SQLPLUS?

Question

I want to extract some queries to a CSV output format. Unfortunately, I can't use any fancy SQL client or any language to do it. I must use SQLPLUS.

How do I do it?

Gautious

Answer 1

If you are using 12.2, you can simply say

set markup csv on
spool myfile.csv

Willyt

Does anyone know how to you turn off echo, the obvious "set echo off" doesn't seem to work with this? – Stirrup

Assuming that this is because you are executing a script and writing to a file, you should just "set termout off" – Willyt

if you also want to set the delimiter: SET MARKUP CSV ON DELIMITER | – Hodgkins

"set termout off" does not work if you are issuing your commands directly in the terminal. Execute your commands as a script instead and it will silence the output. – Synthesize

Answer 2

You could also use the following, although it does introduce spaces between fields.

set colsep ,     -- separate columns with a comma
set pagesize 0   -- No header rows
set trimspool on -- remove trailing blanks
set headsep off  -- this may or may not be useful...depends on your headings.
set linesize X   -- X should be the sum of the column widths
set numw X       -- X should be the length you want for numbers (avoid scientific notation on IDs)

spool myfile.csv

select table_name, tablespace_name 
  from all_tables
 where owner = 'SYS'
   and tablespace_name is not null;

Output will be like:

    TABLE_PRIVILEGE_MAP           ,SYSTEM                        
    SYSTEM_PRIVILEGE_MAP          ,SYSTEM                        
    STMT_AUDIT_OPTION_MAP         ,SYSTEM                        
    DUAL                          ,SYSTEM 
...

This would be a lot less tedious than typing out all of the fields and concatenating them with the commas. You could follow up with a simple sed script to remove whitespace that appears before a comma, if you wanted.

Something like this might work...(my sed skills are very rusty, so this will likely need work)

sed 's/\s+,/,/' myfile.csv 

Karlsruhe

The "," is missing in te colsep line. Also headsep off and linesize X are likely to be useful. Edit the answer and I'll accept it. – Gautious

Thanks for the feedback, Daniel. The "set linesize" is definitely useful, and I have used that in the past for this kind of thing. – Karlsruhe

The sed command is : cat myfile.csv | sed -e 's/[ \t]*|/|/g ; s/|[ ]*/|/g' > myfile.csv. Anyways, Oracle really sucks. – Gibun

And to get a header with the column names use set pagesize 1000 instead of 0. In my previous comment, you can't redirect to the same file : cat myfile.csv | sed -e 's/[ \t]*|/|/g ; s/|[ ]*/|/g' > my_other_file.csv. – Gibun

I filtered out the blanks and the dashes used to underline with grep and tr like this grep -v -- ----- myfile.csv | tr -d [:blank:] > myfile.csv. – Cymose

I'm new to this. Where are these files being created? – Homopolar

@Homopolar the spool command can take a directory path and file name, so that you can specify exactly where the output file will be placed. Otherwise, it would depend on the location where you are executing the script. – Karlsruhe

Only starting 2 columns' values are separated with , but after that they are not getting separated. Don't know why! – Underbred

Hi, what if there are 64 columns and 10000000 lines of data in the table? – Dwaynedweck

this solution does not take care of quote characters feature for CSV files – Lambskin

as also suggested in another answer you can use 'set markup csv on' if you are using sqlplus 12.2 – Tepper

Answer 3

I use this command for scripts which extracts data for dimensional tables (DW). So, I use the following syntax:

set colsep '|'
set echo off
set feedback off
set linesize 1000
set pagesize 0
set sqlprompt ''
set trimspool on
set headsep off

spool output.dat

select '|', <table>.*, '|'
  from <table>
where <conditions>

spool off

And works. I don't use sed for format the output file.

Whyalla

Answer 4

I see a similar problem...

I need to spool CSV file from SQLPLUS, but the output has 250 columns.

What I did to avoid annoying SQLPLUS output formatting:

set linesize 9999
set pagesize 50000
spool myfile.csv
select x
from
(
select col1||';'||col2||';'||col3||';'||col4||';'||col5||';'||col6||';'||col7||';'||col8||';'||col9||';'||col10||';'||col11||';'||col12||';'||col13||';'||col14||';'||col15||';'||col16||';'||col17||';'||col18||';'||col19||';'||col20||';'||col21||';'||col22||';'||col23||';'||col24||';'||col25||';'||col26||';'||col27||';'||col28||';'||col29||';'||col30 as x
from (  
      ...  here is the "core" select
     )
);
spool off

the problem is you will lose column header names...

you can add this:

set heading off
spool myfile.csv
select col1_name||';'||col2_name||';'||col3_name||';'||col4_name||';'||col5_name||';'||col6_name||';'||col7_name||';'||col8_name||';'||col9_name||';'||col10_name||';'||col11_name||';'||col12_name||';'||col13_name||';'||col14_name||';'||col15_name||';'||col16_name||';'||col17_name||';'||col18_name||';'||col19_name||';'||col20_name||';'||col21_name||';'||col22_name||';'||col23_name||';'||col24_name||';'||col25_name||';'||col26_name||';'||col27_name||';'||col28_name||';'||col29_name||';'||col30_name from dual;

select x
from
(
select col1||';'||col2||';'||col3||';'||col4||';'||col5||';'||col6||';'||col7||';'||col8||';'||col9||';'||col10||';'||col11||';'||col12||';'||col13||';'||col14||';'||col15||';'||col16||';'||col17||';'||col18||';'||col19||';'||col20||';'||col21||';'||col22||';'||col23||';'||col24||';'||col25||';'||col26||';'||col27||';'||col28||';'||col29||';'||col30 as x
from (  
      ...  here is the "core" select
     )
);
spool off

I know it`s kinda hardcore, but it works for me...

Turbinal

do we need || for subquery too?, i dont think it is required for the subqueries. but yes it is required for the primary select. – Edition

What is the extra outer select x for ? This should work without it. @davidb, you are right that the concatenation is not required in the primary inner subquery, but aliasing all the columns as col1, col2...etc. is required there. – Superiority

Answer 5

With newer versions of client tools, there are multiple options to format the query output. The rest is to spool it to a file or save the output as a file depending on the client tool. Here are few of the ways:

• SQL*Plus

Using the SQL*Plus commands you could format to get your desired output. Use SPOOL to spool the output to a file.

For example,

SQL> SET colsep ,
SQL> SET pagesize 20
SQL> SET trimspool ON
SQL> SET linesize 200
SQL> SELECT * FROM scott.emp;

     EMPNO,ENAME     ,JOB      ,       MGR,HIREDATE ,       SAL,      COMM,    DEPTNO
----------,----------,---------,----------,---------,----------,----------,----------
      7369,SMITH     ,CLERK    ,      7902,17-DEC-80,       800,          ,        20
      7499,ALLEN     ,SALESMAN ,      7698,20-FEB-81,      1600,       300,        30
      7521,WARD      ,SALESMAN ,      7698,22-FEB-81,      1250,       500,        30
      7566,JONES     ,MANAGER  ,      7839,02-APR-81,      2975,          ,        20
      7654,MARTIN    ,SALESMAN ,      7698,28-SEP-81,      1250,      1400,        30
      7698,BLAKE     ,MANAGER  ,      7839,01-MAY-81,      2850,          ,        30
      7782,CLARK     ,MANAGER  ,      7839,09-JUN-81,      2450,          ,        10
      7788,SCOTT     ,ANALYST  ,      7566,09-DEC-82,      3000,          ,        20
      7839,KING      ,PRESIDENT,          ,17-NOV-81,      5000,          ,        10
      7844,TURNER    ,SALESMAN ,      7698,08-SEP-81,      1500,          ,        30
      7876,ADAMS     ,CLERK    ,      7788,12-JAN-83,      1100,          ,        20
      7900,JAMES     ,CLERK    ,      7698,03-DEC-81,       950,          ,        30
      7902,FORD      ,ANALYST  ,      7566,03-DEC-81,      3000,          ,        20
      7934,MILLER    ,CLERK    ,      7782,23-JAN-82,      1300,          ,        10

14 rows selected.

SQL>

• SQL Developer Version pre 4.1

Alternatively, you could use the new /*csv*/ hint in SQL Developer.

/*csv*/

For example, in my SQL Developer Version 3.2.20.10:

Now you could save the output into a file.

• SQL Developer Version 4.1

New in SQL Developer version 4.1, use the following just like sqlplus command and run as script. No need of the hint in the query.

SET SQLFORMAT csv

Now you could save the output into a file.

Festoonery

Answer 6

I know this is an old thread, however I noticed that no one mentioned the underline option, which can remove the underlines under the column headings.

set pagesize 50000--50k is the max as of 12c
set linesize 10000   
set trimspool on  --remove trailing blankspaces
set underline off --remove the dashes/underlines under the col headers
set colsep ~

select * from DW_TMC_PROJECT_VW;

Hail

Nice catch on the underline option, needed that one. – Trajan

Its nice if you want a csv with a top row that contains the title/headings for each column. It would help anyone who may want to view the csv file, and figure out what they are looking at, etc... – Hail

Answer 7

It's crude, but:

set pagesize 0 linesize 500 trimspool on feedback off echo off

select '"' || empno || '","' || ename || '","' || deptno || '"' as text
from emp

spool emp.csv
/
spool off

Synclastic

Answer 8

You can explicitly format the query to produce a delimited string with something along the lines of:

select '"'||foo||'","'||bar||'"'
  from tab

And set up the output options as appropriate. As an option, the COLSEP variable on SQLPlus will let you produce delimited files without having to explicitly generate a string with the fields concatenated together. However, you will have to put quotes around strings on any columns that might contain embedded comma characters.

Paginate

Answer 9

prefer to use "set colsep" in sqlplus prompt instead of editing col name one by one. Use sed to edit the output file.

set colsep '","'     -- separate columns with a comma
sed 's/^/"/;s/$/"/;s/\s *"/"/g;s/"\s */"/g' $outfile > $outfile.csv

Tamikatamiko

Answer 10

I have once written a little SQL*Plus script that uses dbms_sql and dbms_output to create a csv (actually an ssv). You can find it on my githup repository.

Loux

Answer 11

You should be aware that values of fields could contain commas and quotation characters, so some of the suggested answers would not work, as the CSV output file would not be correct. To replace quotation characters in a field, and replace it with the double quotation character, you can use the REPLACE function that oracle provides, to change a single quote to double quote.

set echo off
set heading off
set feedback off
set linesize 1024   -- or some other value, big enough
set pagesize 50000
set verify off
set trimspool on

spool output.csv
select trim(
       '"'   || replace(col1, '"', '""') || 
       '","' || replace(col2, '"', '""') ||
       '","' || replace(coln, '"', '""') || '"' ) -- etc. for all the columns
from   yourtable
/
spool off

Or, if you want the single quote character for the fields:

set echo off
set heading off
set feedback off
set linesize 1024   -- or some other value, big enough
set pagesize 50000
set verify off
set trimspool on

spool output.csv
select trim(
'"'   || replace(col1, '''', '''''') || 
'","' || replace(col2, '''', '''''') ||
'","' || replace(coln, '''', '''''') || '"' ) -- etc. for all the columns
from   yourtable
/
spool off

Interviewer

The trim() is unnecessary. – Superiority

For those of us still stuck in the past with 11.2.0.4 don't forget "set timing off" and "set feedback off" for the bottom of the file. – Franz

Answer 12

Use vi or vim to write the sql, use colsep with a control-A (in vi and vim precede the ctrl-A with a ctrl-v). Be sure to set the linesize and pagesize to something rational and turn on trimspool and trimout.

spool it off to a file. Then...

sed -e 's/,/;/g' -e 's/ *{ctrl-a} */,/g'  {spooled file}  > output.csv

That sed thing can be turned into a script. The " *" before and after the ctrl-A squeezes out all the useless spaces. Isn't it great that they bothered to enable html output from sqlplus but NOT native csv?????

I do it this way because it handles commas in the data. I turns them to semi-colons.

Ossuary

This fails the "I must use SQLPlus" test. – Gautious

Answer 13

There is a problem using sqlplus to create csv files. If you want the column headers only once in the output and there are thousands or millions of rows, you cannot set pagesize large enough not to get a repeat. The solution is to start with pagesize = 50 and parse out the headers, then issue the select again with pagesize = 0 to get the data. See bash script below:

#!/bin/bash
FOLDER="csvdata_mydb"
CONN="192.168.100.11:1521/mydb0023.world"
CNT=0376
ORD="0376"
TABLE="MY_ATTACHMENTS"

sqlplus -L logn/pswd@//${CONN}<<EOF >/dev/null
set pagesize 50;
set verify off;
set feedback off;
set long 99999;
set linesize 32767;
set trimspool on;
col object_ddl format A32000;
set colsep ,;
set underline off;
set headsep off;
spool ${ORD}${TABLE}.tmp;
select * from tblspc.${TABLE} where rownum < 2;
EOF
LINES=`wc -l ${ORD}${TABLE}.tmp | cut -f1 -d" "`
[ ${LINES} -le 3 ] && {
  echo "No Data Found in ${TABLE}."
}
[ ${LINES} -gt 3 ] && {
  cat ${ORD}${TABLE}.tmp | sed -e 's/ * / /g' -e 's/^ //' -e 's/ ,/,/g' -e 's/, /,/g' | tail -n +3 | head -n 1 > ./${ORD}${TABLE}.headers
}

sqlplus -L logn/pswd@//${CONN}<<EOF >/dev/null
set pagesize 0;
set verify off;
set feedback off;
set long 99999;
set linesize 32767;
set trimspool on;
col object_ddl format A32000;
set colsep ,;
set underline off;
set headsep off;
spool ${ORD}${TABLE}.tmp;
select * from tblspc.${TABLE};
EOF
LINES=`wc -l ${ORD}${TABLE}.tmp | cut -f1 -d" "`
[ ${LINES} -le 3 ] && {
  echo "No Data Found in ${TABLE}."
}
[ ${LINES} -gt 3 ] && {
  cat ${ORD}${TABLE}.headers > ${FOLDER}/${ORD}${TABLE}.csv
  cat ${ORD}${TABLE}.tmp | sed -e 's/ * / /g' -e 's/^ //' -e 's/ ,/,/g' -e 's/, /,/g' | tail -n +2 | head -n -1 >> ${FOLDER}/${ORD}${TABLE}.csv
}

Rigi

Answer 14

I wrote this purely SQLPlus script to dump tables to CSV in 1994.

As noted in the script comments, someone at Oracle put my script in an Oracle Support note, but without attribution.

https://github.com/jkstill/oracle-script-lib/blob/master/sql/dump.sql

The script also also builds a control file and a parameter file for SQL*LOADER

Vicereine

Answer 15

To do it from commandline (Checked on windows cmd) try the following:

echo exit | sqlplus -S -M "CSV ON" MY_USER/MY_PASS@MY_TNSNAME @c:\tmp\mySelect.sql

-S - Supress additional data

-M "CSV ON" - output as CSV

Laryngology

Answer 16

spool D:\test.txt

    select * from emp
    
    spool off

Isabeau

Answer 17

You could use csv hint. See the following example:

select /*csv*/ table_name, tablespace_name
from all_tables
where owner = 'SYS'
and tablespace_name is not null;

Gaeta