Home » SQL & PL/SQL » SQL & PL/SQL » Using Spool & double quote to CSV file (Oracle9i Release 9.2.0.1.0;TNS for 32-bit Windows: 9.2.0.1.0 )
| Using Spool & double quote to CSV file [message #388622] |
Wed, 25 February 2009 13:07  |
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
|
Junior Member |
|
|
I would like to use the SPOOL command to export data on the server to directory for later processing to a CRM application. My need is to have the text fields in double quotes as a requirement of the down-stream process. Most of the SPOOL example only note 'SELECT * from mytable;'. Due to the number of columns I would like to not have to place manual quotes between each field.
How does one note to the process the text fields with double quote? In my previous experience, I used a utility our DBA created, but never gave it a second thought until needing it now at new employer.
While I have been searchin in ORAFAQ, I must not be using the proper key word.
SET termout off
spool ON
spool d:\crm_exchange\crm_export_order.csv
SELECT *
FROM crm_export_order;
spool off
|
|
|
|
|
|
|
|
| Re: Using Spool & double quote to CSV file [message #388634 is a reply to message #388622] |
Wed, 25 February 2009 13:51  |
Jim_Fisher
Messages: 15
Registered: January 2009
Location: CANADA
|
Junior Member |
|
|
Thank you both, I had hoped for a quick (easy) out.
The code is more then just the 'SELECT *', but wanted to save from print all the columns. There are three more SQL like below, covering orders, returns, intercompany from various tables for single load to CRM 'demanding' specifications.
IN_TXN_CODE||IN_NO INV_NO
,TO_CHAR(IN_DT,'DD-MON-YYYY') INV_DATE
,'Order' ORDER_TYPE
,IN_CUST_CODE BCUST_CODE
,IN_CUST_NAME BCUSTOMER_NAME
,IN_BILL_ADDR_LINE_1 BCUSTOMER_ADD1
,IN_BILL_ADDR_LINE_2 BCUSTOMER_ADD2
,TRIM(GET_NAME(IN_BILL_CITY_CODE,'1')) BCUSTOMER_CITY
,IN_BILL_STATE_CODE BSTATE_CODE
,IN_BILL_POSTAL_CODE BPOSTAL_CODE
,IN_BILL_COUNTRY_CODE BCOUNTRY_CODE
,IN_BILL_TEL BTEL
,IN_BILL_FAX BFAX
,IN_SHIP_TO_ADDR_CODE SCUST_ADDR
,IN_CUST_NAME SCUSTNAME
,IN_SHIP_ADDR_LINE_1 SCUSTOMER_ADD1
,IN_SHIP_ADDR_LINE_2 SCUSTOMER_ADD2
,TRIM(GET_NAME(IN_SHIP_CITY_CODE,'1')) SCUSTOMER_CITY
,IN_SHIP_STATE_CODE SSTATE_CODE
,IN_SHIP_POSTAL_CODE SPOSTAL_CODE
,IN_SHIP_COUNTRY_CODE SCOUNTRY_CODE
,IN_SHIP_TEL STEL
,IN_SHIP_FAX SFAX
,IN_LPO_NO ORDER_PO
,IN_SM_NAME ORDER_SALESTAFF
,TO_CHAR(IN_DEL_DT,'DD-MON-YYYY') ORDER_DATE
,NULL SHIP_VIA
,NULL PARTNER_ID
,NULL OPPORTUNITY_ID
,ITEM_NUMBER
,ITEM_ID
,ITEM_SERIAL_NUMBER
FROM INVOICE_HEAD H
,INVOICE_ITEM I
,INVOICE_SERIAL S
WHERE IN_SYS_ID = INI_IN_SYS_ID (+)
AND INI_SYS_ID = INS_INI_SYS_ID (+)
[Updated on: Wed, 25 February 2009 13:52] Report message to a moderator |
|
|
|
|
|
| Re: Using Spool & double quote to CSV file [message #388652 is a reply to message #388622] |
Wed, 25 February 2009 19:48 |
sangram_ocp
Messages: 12
Registered: February 2009
Location: Mumbai
|
Junior Member |
|
|
Hi Jim,
I was trying the same 2 years back...when i was a fresher...
you can try with these steps
set lines 2000
set pagesize 1000
set heading off
set markup html on --- this will send your output to html file.
spool c:\xyz.html
SELECT col1,col2 from table1;
spool off
open xyz.html
copy the content of html file to a excel file.
or
set lines 2000
set pagesize 1000
set termout off
set heading off
spool c:\xyz.csv
select col1||','||col2||','||col3||',' from tab1;
spool off
note: dont put pipe(||) before the from clause.
if this does not fulfill your requirement pls send a mail to
I have done this many times....if you want you can call me ...
Sangram
sangram.dash@citi.com
Mobile: 09920672073 (India)
|
|
|
|
|
|
| Re: Using Spool & double quote to CSV file [message #388773 is a reply to message #388674] |
Thu, 26 February 2009 03:19 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You could create a simple function that takes a date,string or number, and returns it wrapped in double quotes.
If you called this function for each column of your query, and set the COLSEP parameter to a comma, then that would meet yyour requirements.
You could set COLSEP to "," if you can find a way of adding a leading and trailing " to each line of the file.
|
|
|
|
| Re: Using Spool & double quote to CSV file [message #388795 is a reply to message #388773] |
Thu, 26 February 2009 04:24 |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Wow! I thought colsep was only one character.
SQL> set colsep '","'
SQL> show colsep
colsep "",""
SQL> select * from emp;
EMPNO","ENAME ","JOB "," MGR","HIREDATE "," SAL"," COMM"," DEPTNO
----------","----------","---------","----------","-------------------","----------","----------","----------
7369","SMITH ","CLERK "," 7902","17/12/1980 00:00:00"," 800"," "," 20
7499","ALLEN ","SALESMAN "," 7698","20/02/1981 00:00:00"," 1600"," 300"," 30
7521","WARD ","SALESMAN "," 7698","22/02/1981 00:00:00"," 1250"," 500"," 30
7566","JONES ","MANAGER "," 7839","02/04/1981 00:00:00"," 2975"," "," 20
7654","MARTIN ","SALESMAN "," 7698","28/09/1981 00:00:00"," 1250"," 1400"," 30
7698","BLAKE ","MANAGER "," 7839","01/05/1981 00:00:00"," 2850"," "," 30
7782","CLARK ","MANAGER "," 7839","09/06/1981 00:00:00"," 2450"," "," 10
7788","SCOTT ","ANALYST "," 7566","19/04/1987 00:00:00"," 3000"," "," 20
7839","KING ","PRESIDENT"," ","17/11/1981 00:00:00"," 5000"," "," 10
7844","TURNER ","SALESMAN "," 7698","08/09/1981 00:00:00"," 1500"," 0"," 30
7876","ADAMS ","CLERK "," 7788","23/05/1987 00:00:00"," 1100"," "," 20
7900","JAMES ","CLERK "," 7698","03/12/1981 00:00:00"," 950"," "," 30
7902","FORD ","ANALYST "," 7566","03/12/1981 00:00:00"," 3000"," "," 20
7934","MILLER ","CLERK "," 7782","23/01/1982 00:00:00"," 1300"," "," 10
14 rows selected.
Rest the problem of first and last "
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Sun May 25 14:48:39 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
