CODEWITHSUNDEEP
oracle-databasespool
toofast
toofast

Reputation: 33

Spooling in Oracle adds spaces to columns... how can I avoid it?

I'm saving the results of a query in a csv file but unwanted spaces are added to some of the fields, when the original data in the database does not contain them.

For example, if one of the rows in the DB has the values "how", "are" and "you", what I get in the file after spooling is a line like :

"how    |  are |you      "

(the editor doesn't let me write more spaces, but you can imagine there are plenty of them)

When I just want it to be :

"how|are|you"

I've tried several setting options with no result. Is there a way to avoid these spaces? Thanks in advance!

What I got so far:

SET ECHO OFF;
SET NEWP 0 SPACE 0 PAGES 0 FEED OFF HEAD OFF TRIMS OFF TRIM OFF TAB OFF;
set colsep '|';
set lines 130;

spool myfile.csv

SELECT * FROM SOME_TABLE;

spool off;

This goes inside a call to sqlplus.

Upvotes: 3

Views: 22689

Answers (3)

William Robertson
William Robertson

Reputation: 16001

So you're trying something like this:

set colsep "|"

with demo as
   ( select 'Various' as col1, 'test' as col2, 'examples' as col3 from dual
     union all select 'How', 'are', 'you' from dual )
select col1, col2, col3
from   demo;

COL1   |COL2|COL3
-------|----|--------
Various|test|examples
How    |are |you

Unfortunately SQL*Plus isn't designed for unloading data like this - the separator is strictly for columns. You'll have to concatenate the values with pipes yourself e.g.

col1 || '|' || col2 || '|' || col3

(and watch out for column formatting e.g. dates).

Upvotes: 4

Hugo
Hugo

Reputation: 1

If you work on Linux or Unix, consider:

sqlplus ***/***@*** << EOF | sed -e 's/[\t ]*[,$]/,/g' > outputfile.txt
set head off
set colsep ','
etc...
select whatever from sometable;
exit;
EOF

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21063

Your table contains columns defined as CHAR(nn) which fills the values with blanks to the full length or your application stored the additional blanks along the strings.

use rtrim to remove trailing blanks - instaed of

 select c1||'|'||c2||'|'||c3
 from tst;

use

 select rtrim(c1)||'|'||rtrim(c2)||'|'||rtrim(c3)
 from tst;

use ltrim as well if you have both leading and trailing blanks 

 select ltrim(rtrim(c1))||'|'||ltrim(rtrim(c2))||'|'||ltrim(rtrim(c3))
 from tst;

UPDATE

As pointed out in comment the function trim can be used instead

 select  trim(c1)||'|'||trim(c2)||'|'||trim(c3)
 from tst;

Upvotes: 2

Related Questions