Home » Server Options » Text & interMedia » How to extract datas from table to csv
How to extract datas from table to csv [message #76152] Wed, 08 December 2004 21:39 Go to next message
Selva
Messages: 15
Registered: June 2001
Junior Member
have a doubt while spool the records from a table to csv i had a problem,

actually my requirement is when we use the colsep it places the each data to each cell of the excel,but in some cases in table one column values are inserted as

for example column address has the value

address1,address2....etc in this case the column delimited values will be placed in the next cells,ie means one column has placed in multiple cells,

so the issue is.. in that case how to place each column value in each cell..?

please find the following i used

SET ECHO OFF VERIFY OFF PAGESIZE 0 TERMOUT OFF TRIMSPOOL OFF LINESIZE 6000 COLSEP ','

SPOOL /appl/noa/abinitio/sample.csv

select ,,,,,,,,,,,,,,,,,,,query

SPOOL OFF

SET TERMOUT ON

can u please send me a reply as soon as possible...?

Thanks in advance

Report message to a moderator

Re: How to extract datas from table to csv [message #76156 is a reply to message #76152] Thu, 09 December 2004 05:07 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
If a column contains commas, and you want that value to occupy only a single column in your CSV file, then surround it with double quotes.
----------------------------------------------------------------------
SQL> CREATE TABLE t (text VARCHAR2(15), more_text VARCHAR2(15));
 
Table created.
 
SQL> INSERT INTO t VALUES ('ok','also ok');
SQL> INSERT INTO t VALUES ('1,2,3','still no commas');
SQL> INSERT INTO t VALUES ('comma-free','so, now what?');
SQL> INSERT INTO t VALUES ('8,9,10',',11,12,13');
SQL> SELECT t.text
  2  ,      t.more_text
  3  FROM   t
  4  /
 
TEXT            MORE_TEXT
--------------- ---------------
ok              also ok
1,2,3           still no commas
comma-free      so, now what?
8,9,10          ,11,12,13
 
SQL> SELECT DECODE(INSTR(t.text,',')
  2         ,      0, t.text
  3         ,      '"' || t.text || '"')      text
  4  ,      DECODE(INSTR(t.more_text,',')
  5         ,      0, t.more_text
  6         ,      '"' || t.more_text || '"') more_text
  7  FROM   t
  8  /
 
TEXT              MORE_TEXT
----------------- -----------------
ok                also ok
"1,2,3"           still no commas
comma-free        "so, now what?"
"8,9,10"          ",11,12,13"
 
SQL>

For more on the CSV file format, click here.

Report message to a moderator

Re: How to extract datas from table to csv [message #76178 is a reply to message #76156] Sun, 16 January 2005 20:23 Go to previous message
Selva
Messages: 15
Registered: June 2001
Junior Member
how to decode this format?

name age address
selva 24 addr1-aadr2-addr3
joy 26 addr5-addr6-aad9

in to required output format

name age address
selva 24 addr1,aadr2,addr3
joy 26 addr5,addr6,aad9

can you please write decode function for this?

Thanks in advance.
selva

Report message to a moderator

Previous Topic: BInd problem while loading to oracle
Next Topic: ORA-04045: errors during recompilation/revalidation of CTXSYS.CONTEXT
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 22 22:14:45 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.