How to output a CSV file using SQLPLUS Spool?

How to output a CSV file using SQLPLUS Spool?

I want to output several queries in CSV format without using SQL-client or programming language, using only SQLPlus. How to do it?

HOW DO I OUTPUT RESULTS TO A TEXT FILE USING SQLPLUS?

spool MyText.txt;

The MyText.txt file doesn’t exist. Will its SQLPlus be created?

HOW DO I USE SQLPLUS TO OUTPUT THE FILE NAME FROM THE TABLE?

SPOOL ON
SPOOL XXXXX.bkp
SELECT * FROM my_table WHERE field1='value';
SPOOL OFF

You need XXXXX.bkp to be the value of this table field.

THE SIZE OF THE OUTPUT FILE IS MORE THAN 4 GB.

spool on
>set heading off
>set trimsp on pages 0

But when the file size in which the sample results are placed becomes more than 4 GB, the recording stops.

ORACLE SQLPLUS: HOW DO I OUTPUT THE RESULTS WITH SQLPLUS WITHOUT SPOOL OFF?

HOW DO I OUTPUT SQLPLUS TO A CSV FILE?

FORMATTING HEADERS WHEN OUTPUT FROM SQLPLUS TO A CSV FILE

"HOST_SITE_TX_ID", "SITE_ID", "SITETX_TX_ID", "SITETX_HELP_ID".
"664436565","16","2195301","0"
"664700792","52","1099970","0"

EXPORT OF REQUEST RESULTS IN ONE COLUMN

SPOOL COMMAND: DO NOT OUTPUT SQL QUERY TO FILE

spool c:test.csv
select /*csv*/ username, user_id, created from all_users;
spool off;

But the first line of the results is written with the SQL query.

select /*csv*/ username user_id created from all_users; spool off;

But the first line in the results is written to the SQL query.

USERNAME USER_ID CREATED
REPORT 52 11-Sep-13
WEBFOCUS 51 18-Sep-12

Is there any way to fix this?

HOW DO I EXPORT THE RESULTS OF A SELECT QUERY TO A CSV FILE USING SPOOL?

set heading off
set arraysize 1
set newpage 0
set pages 0
set feedback off
set echo off
set verify off

spool 'c:farmerList.csv'.
/

FIRSTNAME, LASTNAME, TRN, CELL, PARISH

spool off

The file is saved in the directory. But it only records “select FIRSTNAME”, “LASTNAME”, “TRN”, “CELL”, “PARISH” and not the query results. What am I doing wrong?

HOW DO I SEND THE RESULT OF AN EXPORT FROM SPOOL USING A PROMPTMAILER WITHOUT USING THE COMMAND?

I need to paste this code into a php file so that the server administrator can add the script to the scheduler.

Answers to:
You can explicitly format the request to display a row with separators between columns. For example:

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

You can also set the desired output parameters. In SQLPlus, you can use the COLSEP variable to get a file with separators without generating a string by linking fields. You need to add quotes around lines that may contain commas.

For example, this is the case:

set pagesize 0 linesize 500 trimspool on feedback off echo off

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

spool emp.csv
/
spool off

You can also use the code below. But keep in mind that it doesn’t draw spaces between fields.

set colsep , -- separate fields with commas.
set pagesize 0 -- remove the headers
set trimspool on -- remove the blank values at the end.
set headsep off -- it can be useful depending on the headers
set linesize X -- X should be the total width of all fields
set numw X -- X must be the longest numerical value of the fields (to avoid the scientific format for the ID field)

spool myfile.csv

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

The conclusion will be about this:

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

It’s easier than listing all the fields and combining them with commas. You can also use a simple script for the sed command to remove spaces before commas.

For example:

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

I need to print the result from SQLPlus to csv file, but the output contains 250 fields. Here’s what I did to avoid annoying SQLPlus formatting:

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

The problem is, you lose the column headers. You can fix this with the following code:

set heading off
spool myfile.csv
select col1name||'; '||col2name||'; '||col3|col3name||'; '||col4name||'; '||col5name||'; '||col6name||'; '||col7name||'; '||col8|'; ||col9name||'; '||col10name||'; '||col11name||'; '||col12name||'; '||col13name||'; '||col14||'; '||col15name||'; ||col16name||'; '||col17name||'; '||col18|'; '||col19name||'; '||col20name||'; '||col21name||'; '||col22name||'; '||col23name||'; ||col24name||'; '||col25name||'; '||col26name||'; '||col27name||'; '||col28name||'; '||col29name||'; '||col30_name from dual;

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

In SQLPlus I prefer to use the set colsep command instead of editing the column headers. You can also use the sed command to edit the resulting file.

set colsep '," -- Separate columns with commas.
sed 's/^/"/;s/$/"/;s//s *"/"/g;s/"/s */"/g' $outfile > $outfile.csv

I use this command for scripts that extract data from tables:

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

The code works. So you do not need to use sed to format the resulting file.

I wrote a small script for SQLPlus which used dbms_sql and dbms_output to create a csv file (actually ssv). You can find it in my repository.

Make sure you initialize linesize and pagesize with some rational values and enable trimspool and trimout. Output the results to a file. Then…

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

The sed command can be executed in the script. The asterisk before and after ctrl-A removes spaces. This method helps solve the problem with commas in the data. I replace them with semicolons.

New versions of SQL client applications support several options for formatting query results:

IN SQLPLUS

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,20FEB-81,1600,300,30
7521, 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>

IN SQL DEVELOPER VERSION UP TO VERSION 4.1

/*csv*/

For example, in my SQL Developer version 3.2.20.10:

Now you can save this output to a file.

IN SQL DEVELOPER VERSION 4.1

SET SQLFORMAT csv

You can now save the output to a file.

You can use the csv hint. See the example above:

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

The underline parameter removes the underscores under column headers.

set pagesize 50000 -- 50 thousand is the maximum for version 12c.
set linesize 10000
set trimspool on - remove the gaps at the end.
set underline off - remove the accents under the headings
set colsep ~

select * from DW_TMC_PROJECT_VW;

Field values may contain commas and quotes, so some of the suggested variants will not work. To replace single quotes with double quotes inside a field, use the REPLACE function.

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

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

If you want to leave single quotes in field values, use the code below:

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

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

If you are using SQL Developer version 12.2, you can simply add

set markup csv on

WISSENSCHAFTSCOACHING: BERATUNG, COACHING, LEKTORAT, ÜBERSETZUNG