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 Go to next message
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 #388628 is a reply to message #388622] Wed, 25 February 2009 13:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No way in SQL or SQL*Plus unless you specify it in the query.

Regards
Michel
Re: Using Spool & double quote to CSV file [message #388631 is a reply to message #388622] Wed, 25 February 2009 13:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Also,
>> My need is to have the text fields in double quotes
How to identify that without mentioning individual columns?
If not a scheduled job, use some GUI tool to generate csv.
Or
You have to write a routine that internally do all the tricks.
Just more complex for a simple thing.
Re: Using Spool & double quote to CSV file [message #388634 is a reply to message #388622] Wed, 25 February 2009 13:51 Go to previous messageGo to next message
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 #388635 is a reply to message #388634] Wed, 25 February 2009 13:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://asktom.oracle.com/tkyte/flat/index.html

It is one of the best solution available.
Please see what fits you.
Re: Using Spool & double quote to CSV file [message #388652 is a reply to message #388622] Wed, 25 February 2009 19:48 Go to previous messageGo to next message
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 #388674 is a reply to message #388652] Wed, 25 February 2009 22:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
@sangram_ocp
The OP is not looking into any of these options.
and certainly not an HTML output.
quoting the OP
>> I would like to not have to place manual quotes between each field.
Re: Using Spool & double quote to CSV file [message #388773 is a reply to message #388674] Thu, 26 February 2009 03:19 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: To_number function
Next Topic: inconsistent datatype ora:00932
Goto Forum:
  


Current Time: Sun Dec 11 00:13:32 CST 2016

Total time taken to generate the page: 0.08697 seconds