3

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
toofast
  • 33
  • 1
  • 1
  • 5

3 Answers3

4

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

William Robertson
  • 15,273
  • 4
  • 38
  • 44
  • good observation, I'm probably wrong guessing on leading and trailing blanks in the column data. – Marmite Bomber Nov 10 '15 at 15:59
  • In what way could there be problems with the column formatting? One of the fields is actually a date, but I can't imagine any risk. – toofast Nov 10 '15 at 16:21
  • I just mean the format for dates will depend on `nls_date_format` and your desktop territory settings unless you explicitly set a format with `to_char`. Also make sure `linesize` is set big enough for the output record. – William Robertson Nov 10 '15 at 16:39
2

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;
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • 1
    Instead of ltrim(rtrim(c1)) you could just use trim(c1) – pablomatico Nov 10 '15 at 15:57
  • Well, with the trim() function and setting the separator inside the select clause (instead of using colsep), I'm getting pretty much what I wanted. I'll run some more tests but for now it looks good. – toofast Nov 10 '15 at 16:17
0

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
Hugo
  • 1
  • 1