How do I spool to a CSV formatted file using SQLPL

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?

Answer 1

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;

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

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