Home » SQL & PL/SQL » SQL & PL/SQL » Leading spaces in spool files
| Leading spaces in spool files [message #196488] |
Thu, 05 October 2006 10:07  |
Mike Bentley
Messages: 8
Registered: September 2006
|
Junior Member |
|
|
I am having trouble creating a spool file in csv format.
The script I run is (this is a much cut down version to illustrate the problem)
____________________________________
sqlplus -s user/password@ocxd
set linesize 6000
set feedback off
set heading off
set termout off
set trimspool on
set pagesize 100
set echo off
spool c:\oracle\ora92\bin\testh1.csv
SELECT ch_pt_lname||',' "Patient last name,",
ch_pt_fname||',' "Patient first name,",
ch_pt_minitial||',' "Init,",
ch_last4_ss "SSN"
FROM sst.ctbn_header
WHERE ch_created_datetime > (SELECT CE_LASTRUN_DATETIME FROM sst.ctbn_extract WHERE ce_jobname = 'EXTRACT')
AND ct_type_id = 'MSO'
ORDER BY ch_header_seqid;
spool off
___________________________________________
In the table lname is varchar2(50), fname is varchar2(30), minitial is varchar2(5) and ch_last4_ss is varchar2(4). When I run just the select in SQL*PLUS the allignment is fine except of course the underscores on the headings are the length of the column and not the length of the supplied header.
When I spool the output to the .csv file, there are leading spaces or unprintable characters in it so when the file is opened in Excel, often the data in a column is so far to the right it is not visible and the column has to be expanded greatly to see it. Although the headers are not appearing due to the set heading off it still seems to be saving the spaces of the underscore length and they of course are not comma delimited.
This is the .csv file opened in notepad
___________________________________________
GARNER,(45 spaces)ROY,(28 spaces)G,(5 spaces)4818
GARNER,(45 spaces)ROY,(28 spaces)G,(5 spaces)4818
COOLEY,(45 spaces)JOHNNY,(25 spaces)L,(5 spaces)0913
______________________________________________
On the full query, there are 4 names as well as multiple other fields and the number of spaces inserted changes according to the actual length of the preceeding columns. Can anyone help?
Mike
|
|
|
|
| Re: Leading spaces in spool files [message #196504 is a reply to message #196488] |
Thu, 05 October 2006 12:14  |
 |
ebrian
Messages: 2794
Registered: April 2006
|
Senior Member |
|
|
This should work for ya:
select 'Patient last name, Patient first name, Init, SSN' from dual
union all
SELECT ch_pt_lname||','||
ch_pt_fname||','||
ch_pt_minitial||','||
ch_last4_ss from (select ch_pt_lname, ch_pt_fname, ch_pt_minitial, ch_last4_ss
FROM sst.ctbn_header
WHERE ch_created_datetime > (SELECT CE_LASTRUN_DATETIME
FROM sst.ctbn_extract WHERE ce_jobname = 'EXTRACT')
AND ct_type_id = 'MSO'
ORDER BY ch_header_seqid); [Updated on: Thu, 05 October 2006 21:30] Report message to a moderator |
|
|
|
| Re: Leading spaces in spool files [message #196524 is a reply to message #196504] |
Thu, 05 October 2006 15:41 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
| ebrian wrote on Thu, 05 October 2006 12:14 |
This should work for ya:
select 'Patient last name, Patient first name, Init, SSN' from dual
union all
SELECT ch_pt_lname||',',
ch_pt_fname||',',
ch_pt_minitial||',',
ch_last4_ss from (select ch_pt_lname, ch_pt_fname, ch_pt_minitial, ch_last4_ss
FROM sst.ctbn_header
WHERE ch_created_datetime > (SELECT CE_LASTRUN_DATETIME
FROM sst.ctbn_extract WHERE ce_jobname = 'EXTRACT')
AND ct_type_id = 'MSO'
ORDER BY ch_header_seqid));
|
Almost right, use
select 'Patient last name, Patient first name, Init, SSN' from dual
union all
SELECT replace(ch_pt_lname,',',' ')||','||
replace(ch_pt_fname,',',' ')||','||
ch_pt_minitial||','||
ch_last4_ss
FROM sst.ctbn_header
WHERE ch_created_datetime > (SELECT CE_LASTRUN_DATETIME
FROM sst.ctbn_extract WHERE ce_jobname = 'EXTRACT')
AND ct_type_id = 'MSO'
ORDER BY ch_header_seqid);
[Updated on: Thu, 05 October 2006 15:43] Report message to a moderator |
|
|
|
| Re: Leading spaces in spool files [message #196527 is a reply to message #196524] |
Thu, 05 October 2006 16:56 |
|
ebrian
Messages: 2794
Registered: April 2006
|
Senior Member |
|
|
| Bill B wrote on Thu, 05 October 2006 15:41 |
Almost right, use
select 'Patient last name, Patient first name, Init, SSN' from dual
union all
SELECT replace(ch_pt_lname,',',' ')||','||
replace(ch_pt_fname,',',' ')||','||
ch_pt_minitial||','||
ch_last4_ss
FROM sst.ctbn_header
WHERE ch_created_datetime > (SELECT CE_LASTRUN_DATETIME
FROM sst.ctbn_extract WHERE ce_jobname = 'EXTRACT')
AND ct_type_id = 'MSO'
ORDER BY ch_header_seqid);
|
Rather inappropriate to respond with "Almost Right" when your code is "Almost Right".
I edited my code above to correct my copy & paste from the OP's original code and removed the fat fingered ')' prior to the semicolon.
Sorry...it's been a long day !!
[Updated on: Thu, 05 October 2006 21:43] Report message to a moderator |
|
|
|
|
|
| Re: Leading spaces in spool files [message #196554 is a reply to message #196536] |
Thu, 05 October 2006 21:44 |
|
ebrian
Messages: 2794
Registered: April 2006
|
Senior Member |
|
|
Your correct Barbara. The commas in my initial response were due to a copy & paste error from the OP's original query (corrected above). However, the data would be materially changed if it contained any commas and they were replaced with the REPLACE function. To truely accommodate for commas in the data, a different delimiter should actually be chosen.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 02 03:41:59 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
