Remove trailing spaces from CSV output 1

[MCubitt]

Hi there.

I am using SQL to create CSV format output. This works but each column is exactly a fixed length, even when I rtrim the value.

I have used
column "UserID" format A20;
so the heading is fixed or have I done this wrong?

An extract is:

var uCopyToUserID varchar2(20)
EXEC :uCopyToUserID := UPPER('&Copy_To_UserID')
var uCopyToName varchar2(30)
EXEC :uCopyToName := ('&Copy_To_Name')

column "UserID" format A20;
column "Description" format A30;

select :uCopyToUserID as UserID, substruCopyToName,1,30) Description from...


The output is:
WEEDENS ,steve weeden ,



Many thanks








There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MPeccorini]

I think in this case it would be better if you concatenate the whole string. in that way you have far more control over white spaces.

For example, you could have the following:

select '"' || :uCopyToUserID || '","' ||
substruCopyToName,1,30) || '"'
from ...

 

[SantaMufasa]

MCubitt,

You are welcome to use my GENASCII.sql script that creates .CSV files:

Section 1 -- Sample invocation:

SQL> @genascii
Enter the table to ASCII: s_dept
Enter the flat file to write: MCubitt.txt

Following output is generated script that writes text output from table "s_dept"

set echo off
set feedback off
set heading off
set pagesize 0
spool MCubitt.txt
Select
'"'||ID|| '"'
||',"'||NAME|| '"'
||',"'||REGION_ID|| '"'
from s_dept
/
spool off
set feedback on
set heading on
set pagesize 20

Following is text output written to file "MCubitt.txt"

"10","Finance","1"
"31","Sales","1"
"32","Sales","2"
"33","Sales","3"
"34","Sales","4"
"35","Sales","5"
"41","Operations","1"
"42","Operations","2"
"43","Operations","3"
"44","Operations","4"
"45","Operations","5"
"50","Administration","1"
"51","Information Tech","1"

Output file = "MCubitt.txt"

Section 2 -- GENASCII.sql code:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor Dasages, LLC, makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
accept x prompt "Enter the table to ASCII: "
accept y prompt "Enter the flat file to write: "
prompt
prompt Following output is generated script that writes text output from table "&x"
prompt 
set verify off
set feedback off
set heading off
set pagesize 0
set linesize 32767
set trimout on
set trimspool on
spool temp.sql
prompt set echo off
prompt set feedback off
prompt set heading off
prompt set pagesize 0
prompt spool &y
prompt Select
select decode (rownum,1,'''"''||','||'',"''||') || column_name || '|| ''"''' 
  from user_tab_columns
 where table_name = upper('&x');
prompt from &x
prompt /
prompt spool off
prompt set feedback on
prompt set heading on
prompt set pagesize 20
spool off
prompt
prompt Following is text output written to file "&y"
prompt
@temp
set echo off
prompt
prompt Output file = "&y"
prompt

Let us know is this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:24 (28Jun04) UTC (aka "GMT" and "Zulu"), 09:24 (28Jun04) Mountain Time)

 

[MCubitt]

Okay, getting closer maybe?

select '"' || :uCopyToUserID || '"'
from ifsapp.fnd_user_tab fut
where fut.identity like :uCopyFromUserID
order by fut.identity;

but error is:
'"'||:UCOPYTOUSERID||'"'
----------------------------------
"SMITHJ"
ERROR:
ORA-01756: quoted string not properly terminated



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

My mistake, the error related to the NEXT line!

Thanks all!


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

MCubitt,

In reviewing your current error, I should point out that my GenASCII.sql script works for tables that the currently logged user owns. So where you are accessing schema "ifsapp", you would either need to modify GenASCII.sql to use either "ALL_TABLES" or "DBA_TABLES" and qualify with "OWNER" or you would need to log in as "ifsapp".

Out of curiosity, what happens if you log in as "ifsapp", run the GenASCII.sql script against "fnd_user_tab", and modify the generated script to use your WHERE condition?

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:35 (28Jun04) UTC (aka "GMT" and "Zulu"), 10:35 (28Jun04) Mountain Time)

 

[MCubitt]

Santa, Unfortunately I don't think your script will work for me since it really is not a simple case of extracting data without thrills.

The current statement (before messing with!) which works is:

select ' ' as "Rec", ' ' as "Action",'"' || :uCopyToUserID || '"' as UserID, substruCopyToName,1,30) Description,
:uCopyToUserID as x, :uCopyToUserID as y, :uCopyToUserID as z, :uCopyToUserID as IFS,
(select substr(value,1,30) from fnd_user_property where identity = fut.identity and name='DEPARTMENT') "Department",
(select substruCopyToName,1,instruCopyToName,' ')-1) || '.' || substruCopyToName,instruCopyToName,' ')+1,lengthuCopyToName)-instruCopyToName,' ')+1) || substr(value,instr(value,'@'),length(value)-instr(value,'@')+1) from fnd_user_property where identity = fut.identity and name='SMTP_MAIL_ADDRESS') "Email",
(select substr(value,1,30) from fnd_user_property where identity = fut.identity and name='SMTP_MAIL_HOST') "Host",
(substruCopyToName,1,1) || substruCopyToName,instruCopyToName,' ')+1,1)) "Streamserve_Initials",
('./out/' || :uCopyToUserID) "Streamserve_PDF",
(select 'X' from user_finance where userid = fut.identity and company='OKM') "OKM",
(select 'X' from user_finance where userid = fut.identity and company='OIA') "OIA",
(select 'X' from user_allowed_site where userid = fut.identity and contract='OKMMN') "OKMMN",
(select 'X' from user_allowed_site where userid = fut.identity and contract='OKMSV') "OKMSV",
(select 'X' from user_allowed_site where userid = fut.identity and contract='OKMDM') "OKMDM",
(select 'X' from user_allowed_site where userid = fut.identity and contract='OIAMN') "OIAMN",
(select 'X' from user_allowed_site where userid = fut.identity and contract='OIADM') "OIADM",
(select default_company from user_finance where userid = fut.identity and rownum = 1) "Default_company",
(select contract from user_allowed_site where userid = fut.identity and user_site_type='DefaultSite') "Default_site",
(select DEFAULT_PROFILE_OWNER from CLIENT_PROFILE_USER where user_name = fut.identity) "Cost_Profile",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_ACCOUNTS_ADMINSTRATOR') "KM_ACCOUNTS_ADMINSTRATOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_ACCOUNTS_MANAGER') "KM_ACCOUNTS_MANAGER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_ACCOUNTS_SUPERVISOR') "KM_ACCOUNTS_SUPERVISOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_AUDITOR') "KM_AUDITOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_BASE_ROLE') "KM_BASE_ROLE",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_COURSE_CO-ORDINATION') "KM_COURSE_CO_ORDINATION",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_CREDIT_ANALYSTS') "KM_CREDIT_ANALYSTS",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_DEMO_BASIC') "KM_DEMO_BASIC",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_DEMO_SENIOR') "KM_DEMO_SENIOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_ENGINEER') "KM_ENGINEER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_EXPENSES_ADMINISTRATOR') "KM_EXPENSES_ADMINISTRATOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_EXPENSES_USER') "KM_EXPENSES_USER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_EXPORT+OIA_LA_COORDINATOR') "KM_EXPORT_OIA_LA_COORDINATOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_EXPORT+OIA_LA_MANAGER') "KM_EXPORT_OIA_LA_MANAGER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_EXPORT+OIA_LA_SUPER') "KM_EXPORT_OIA_LA_SUPER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_EXPORT_SHIPPING_COORDINATOR') "KM_EXPORT_SHIPPING_COORDINATOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_FINANCE_MANAGER') "KM_FINANCE_MANAGER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_FINANCIAL_ACCOUNTANT') "KM_FINANCIAL_ACCOUNTANT",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_GENERAL_ENQUIRIES') "KM_GENERAL_ENQUIRIES",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_IMPORT_SHIPPING_COORDINATOR') "KM_IMPORT_SHIPPING_COORDINATOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_INVENTORY_CONTROLLER') "KM_INVENTORY_CONTROLLER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_IT_SUPPORT') "KM_IT_SUPPORT",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_LOGISTICS_MANAGER') "KM_LOGISTICS_MANAGER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_OIA_BASIC') "KM_OIA_BASIC",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_OIA_SENIOR') "KM_OIA_SENIOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_PLANNER_BUYER') "KM_PLANNER_BUYER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_PRICING+DATA_ADMINISTRATOR') "KM_PRICING_DATA_ADMINISTRATOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_PROCESS_MANAGER') "KM_PROCESS_MANAGER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_PRODUCT_MANAGER') "KM_PRODUCT_MANAGER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_PURCHASING_MANAGER') "KM_PURCHASING_MANAGER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_QUALITY') "KM_QUALITY",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_DOC_MAN_ADMINISTRATOR') "KM_R_D_DOC_MAN_ADMINISTRATOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_DOC_MAN_USER') "KM_R_D_DOC_MAN_USER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_ECR_OR_ECO_ADMIN') "KM_R_D_ECR_OR_ECO_ADMIN",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_ECR_OR_ECO_COMMITTEE') "KM_R_D_ECR_OR_ECO_COMMITTEE",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_ECR_OR_ECO_MFG_ENGINEER') "KM_R_D_ECR_OR_ECO_MFG_ENGINEER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_ECR_OR_ECO_ORIGINATOR') "KM_R_D_ECR_OR_ECO_ORIGINATOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_ECR_OR_ECO_PRELIM_GROUP') "KM_R_D_ECR_OR_ECO_PRELIM_GROUP",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_ECR_OR_ECO_QA_RA_MAN') "KM_R_D_ECR_OR_ECO_QA_RA_MAN",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_ECR_OR_ECO_READER') "KM_R_D_ECR_OR_ECO_READER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_GENERAL_READER') "KM_R_D_GENERAL_READER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_PDM_ADMINISTRATOR') "KM_R_D_PDM_ADMINISTRATOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_PDM_READER') "KM_R_D_PDM_READER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_PDM_USER') "KM_R_D_PDM_USER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_PROJECT_ADMINISTRATOR') "KM_R_D_PROJECT_ADMINISTRATOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_PROJECT_INVOICE') "KM_R_D_PROJECT_INVOICE",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_PROJECT_LEADERS') "KM_R_D_PROJECT_LEADERS",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_PROJECT_READER') "KM_R_D_PROJECT_READER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_PROJECT_USER') "KM_R_D_PROJECT_USER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_R_D_UNCONFIRMED_DAY') "KM_R_D_UNCONFIRMED_DAY",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_REGULATORY') "KM_REGULATORY",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_SERVICE_BASIC') "KM_SERVICE_BASIC",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_SERVICE_ORD_COORD') "KM_SERVICE_ORD_COORD",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_SERVICE_ORD_MANAGER') "KM_SERVICE_ORD_MANAGER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_SERVICE_SENIOR') "KM_SERVICE_SENIOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_SHIPPING_MANAGER') "KM_SHIPPING_MANAGER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_STORES_PERSON') "KM_STORES_PERSON",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_SUPERVISOR_MANAGER') "KM_SUPERVISOR_MANAGER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_UK+OIA_COORDINATOR') "KM_UK_OIA_COORDINATOR",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_UK+OIA_MANAGER') "KM_UK_OIA_MANAGER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_UK+OIA_SUPER') "KM_UK_OIA_SUPER",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_WAREHOUSE_BASIC') "KM_WAREHOUSE_BASIC",
(select 'X' from fnd_user_role where identity = fut.identity and oracle_role='KM_WAREHOUSE_SENIOR') "KM_WAREHOUSE_SENIOR"
from ifsapp.fnd_user_tab fut
where fut.identity like :uCopyFromUserID
order by fut.identity;




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

MCubitt,

Perhaps surprisingly, my script would work for you if you prepend a "CREATE TABLE MCUBITT AS..." in front of your working "SELECT" statement. (Also, you would need to remove your "...ORDER BY" since that clause is not allowed on a CREATE TABLE statement.)

You could accomplish the same effect by manually embedding concatenation ("||") characters and removing column aliases. But that becomes a lot of work, which I'll bet takes much longer than creating a temporary table as above, in order for the script to do all the work for you.

But, you decide and let us know.

Mufasa
(aka Dave of Sandy, Utah, USA @ 08:32 (29Jun04) UTC (aka "GMT" and "Zulu"), 01:32 (29Jun04) Mountain Time)

 

[MCubitt]

In the end I manually recreated the script column by column and it now works.

HOWEVER.. I now get blank lines!

 

[SantaMufasa]

MCubitt,

The reason you are getting extra blank lines results from a combination of factors. To resolve the problems, I recommend the following SQL*Plus formatting commands:

set pagesize 0
set trimspool on

Your current settings of "pagesize 9000" still results in one blank line at the beginning; "pagesize 0" supresses even that line. LINESIZE 4000 makes the lines VERY LONG. The "trimspool on" setting will trim any unnecessary trailing blanks spaces.

Let us know if this helps resolve your problems.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:10 (29Jun04) UTC (aka "GMT" and "Zulu&quot, 12:10 (29Jun04) Mountain Time)

 

[MCubitt]

Thanks, I set 4000 to cater for "worse case" but realisticly 3000 should be enough (I know 2000 isn't).

Anyway, works well, thanks.



There's no need for sarcastic replies, we've not all been this sad for that long!