create flat file comma sep val csv from sql plus with field headers
21914Nov 24 2004 — edited Nov 29 2004I am trying to create dumps of tables as comma seperated values. I have it working except for getting the header at the top of the file.
So more or less I want some results like:
*********************
"Name","Deptno","EmpNo"
"Smith","9999","12345"
"Jones","9998","12355"
"Homer","9997","12333"
also note I am enclosing all values in quotes.
Once I try to make changes to include the header things start going bad. One problem is I have over 200,000 records in several tables so with the 50000 pagesize limit I set it to 0. This causes a problem because it does not put headings when set to 0.
Here is a sample of current sql plus code.
any help will be appreciated.
Also note, I tried using the "colsep" method, but it left spaces in between fields which I find unacceptable.
***********************************
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 500
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
set serveroutput on
SET VERIFY OFF
SET HEADING on
set sqlprompt ''
set trimspool on
SET MARKUP HTML OFF SPOOL OFF
set termout off
spool tryloc.txt
SELECT '"'||l.API ||'","'||
l.CNTYNM||'","'||
l.CNTY||'","'||
l.PRMT||'","'||
l.TXDSTNM||'","'||
l.TXDST||'","'||
l.QUADNM||'","'||
l.SQUADNM||'","'||
l.SQUAD||'","'||
l.SLATD||'","'||
l.SLATM||'","'||
l.SMIS||'","'||
l.SLOND||'","'||
l.SLONM||'","'||
l.SMIW||'","'||
l.LATDD||'","'||
l.LONDD||'","'||
l.UTME||'","'||
l.UTMN||'","'||
l.LFLGTR||'","'||
l.LFLG||'","'||
l.LCOMMENT||'","'||
l.LDATE||'"'
FROM OGUSER.LOC_LKUP_VIEW l
where l.prmt<5;
spool off
set trimspool off
set termout on
set heading on
set sqlprompt 'johns sql >'
set feedback on
set serveroutput off
set echo on
set verify on