CODEWITHSUNDEEP
sqlexport-to-csvoracle-sqldeveloper
roostersign
roostersign

Reputation: 300

Remove Blank Line In Between Select Queries When Spooling to CSV File

I'm using oracle sql developer in order to spool some data to a csv file. So far I have this in one file:

SET head OFF;
SET echo OFF; 
SET termout OFF; 
SET verify OFF; 
set colsep ,
set pagesize 0
set feedback off
@sandbox.sql

Sandbox.sql contains this:

spool "C:\TEMP\test.csv"
select 'Date,Average' from dual;
select to_char(rollup_time, 'mm/dd/yyyy'), average from sample_table
order by 1;
spool off;

This produces some csv file that looks like this:

Date Average

1/1/2012 900
1/2/2012 910

I want to get rid of the blank line that follows the column headings, but I cannot set trimspool on (option does not exist) and union all fails to work because the data types are not the same. Does anyone know how to remove that blank line in oracle's sql developer environment?

Upvotes: 3

Views: 41758

Answers (3)

thatjeffsmith
thatjeffsmith

Reputation: 22467

Here you go, using SQL Developer (version 18.2)

I don't have your table, but I do have HR.EMPLOYEES.

cd c:\sqldev
set feedback off
set pagesize 100
set sqlformat csv
spool so-no-blanks.csv
select 'Date, Average' from dual;
select to_char(hire_date, 'mm/dd/yyyy'), salary from employees
fetch first 3 rows only;
spool off

-- set sqlformat csv: tells SQL Developer's script engine to automatically return the resultset in a CSV format (json, xml, insert, html, delimited are also available)

SQL Developer's script engine was updated a few years ago to support pretty much everything SQL*Plus offers, plus a TON more. Like that 'cd' command - it sets the path when doing something like a 'spool'

enter image description here

Upvotes: 1

fabio
fabio

Reputation: 356

I have just found myself in the same situation as roostersign and I have managed to avoid the blank line between my two queries in SQL*Plus with:

set space 0

I got there by testing the suggestions here: SQL*Plus: Formatting query results

Upvotes: 0

Sarav
Sarav

Reputation: 51

Use "Set Newpage none" command.

SET NEWP[AGE] {1 | n | NONE} Sets the number of blank lines to be printed from the top of each page to the top title. A value of zero places a formfeed at the beginning of each page (including the first page) and clears the screen on most terminals. If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between the report pages.

Upvotes: 4

Related Questions