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