Hello Tom,
I love SQLcl for spooling data to a CSV-File for using in Excel. But i have trouble with comma inside a field.
This is my SQL-Statement:
SET LINESIZE 3000
SET TRIMSPOOL ON
SET PAGESIZE 0
SET SERVEROUTPUT ON
SET VERIFY OFF
SET TERMOUT ON
SET FEEDBACK OFF
SET SQLFORMAT CSV
DEFINE _TYP =&1
DEFINE _Monat =&2
DEFINE _BEW =&3
DEFINE _FILE =&4
SPOOL &_FILE
SELECT a.pers_nr as "Pers Nr",
a.monat as "Month",
a.ag as "AG",
a.name as "Name",
a.surname as "Surname",
a.vereinb_az as "Vereinb Az",
a.hobby as "Hobby"
FROM zus_zahlungsmerkm_verg a
where a.bew = '&_BEW' AND
a.monat = '&_Monat' AND
a.wegf_schl <= '00' AND
(a.verggr_schl like 'E%' OR a.verggr_schl like 'K%' or a.verggr_schl like 'MLE%')
order by a.name, a.surname;
SPOOL OFF
If I am spooling the data to a CSV-File it looks like the following:
"Pers Nr","Month","AG","Name","Surname","Vereinb Az","Hobby"
"12345678",201712,"999R","Dent","Arthur",19,75,"Hitchhiking"
But I need to put the field "Vereinb Az" -> 19,75 also in quotes for not getting confusion in my Excel-file.
Is there a way to spool all the fields as "text" to the file?
kind regards
Torsten
I spoke to the PM for SQLcl - there is no way to do this.
However, for countries where comma is the decimal separator, typically people will use a different delimiter in their spool file, eg
SQL> set sqlformat csv ~
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~20/FEB/81~1600~300~30
7521~"WARD"~"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