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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Bill B
Messages: 1484
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 Go to previous messageGo to next message
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 #196536 is a reply to message #196527] Thu, 05 October 2006 19:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I believe the point is to concatenate all of the fields into one, rather than selecting separate fields, to eliminate the extra spaces and, since it is a comma-separated value (csv) file, to replace any commas within the data, so that they are not mistaken for field delimiters when loading into Excel.
Re: Leading spaces in spool files [message #196554 is a reply to message #196536] Thu, 05 October 2006 21:44 Go to previous message
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.

Previous Topic: SQL*NET message from client
Next Topic: sql loader
Goto Forum:
  


Current Time: Thu Dec 08 10:28:22 CST 2016

Total time taken to generate the page: 0.05220 seconds