OraFAQ Forum: SQL & PL/SQL » Writing to CSV or excel using UTL

Home » SQL & PL/SQL » SQL & PL/SQL » Writing to CSV or excel using UTL_FILE in ORACLE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Writing to CSV or excel using UTL_FILE in ORACLE [message #600454]

Wed, 06 November 2013 22:19

nathran
Messages: 6
Registered: January 2009

Junior Member

Hi Friends,
I have small requirement.
......
I have a table call TAB which is having n no of coulmns & rows.
For example, take COL1.

COL1
-----
AA
BB
CC
DD

I need to extract these values into csv or excel format, butvalues has to written into a single cell.
This is like how we enter data in a excel cell using ALT+ENTER. (Wrap text).
Please help........

[Updated on: Wed, 06 November 2013 22:35]

Report message to a moderator

Re: Writing to CSV or excel using UTL_FILE in ORACLE [message #600459 is a reply to message #600454]

Wed, 06 November 2013 23:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>This is like how we enter data in a excel cell using ALT+ENTER. (Wrap text).
what exactly needs to exist in file that represents ALT+ENTER (which has NOTHING to do with Oracle)?

Report message to a moderator

Re: Writing to CSV or excel using UTL_FILE in ORACLE [message #600548 is a reply to message #600454]

Thu, 07 November 2013 14:11

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

nathran wrote on Thu, 07 November 2013 09:49

I need to extract these values into csv or excel format, butvalues has to written into a single cell.
This is like how we enter data in a excel cell using ALT+ENTER. (Wrap text).

Oracle cannot control a CSV/Excel file. ALT+ENTER is not a defined operation in Oracle. There are few things which can control the output, for example, chr(10) --> Line feed, but then, it's out of the box for Oracle to alter settings for an external tool, which you want as a requirement.

Regards,
Lalit

Report message to a moderator

Re: Writing to CSV or excel using UTL_FILE in ORACLE [message #600549 is a reply to message #600454]

Thu, 07 November 2013 14:25

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

So you want all column values in one cell, right? Then use:

SELECT  '"' || LISTAGG(col1,chr(10)) WITHIN GROUP(ORDER BY ROWID) || '","' ||
        LISTAGG(col2,chr(10)) WITHIN GROUP(ORDER BY ROWID) || '","' ||
        .
        .
        .
        LISTAGG(colN,chr(10)) WITHIN GROUP(ORDER BY ROWID) || '"'
  FROM  tbl;

and write output to CSV file. Just keep in mind, UTL_FILE is PL/SQL package and runs on database server side. Therefore file created by UTL_FILE also resides on database server side.

SY.

[Updated on: Thu, 07 November 2013 14:28]

Report message to a moderator

Re: Writing to CSV or excel using UTL_FILE in ORACLE [message #600554 is a reply to message #600549]

Thu, 07 November 2013 16:30

nathran
Messages: 6
Registered: January 2009

Junior Member

Thanks for your reply.
But i need to show them as shown below in a single cell.

"AA"
"BB"
"CC"

But not like
"AA""BB""CC"

Thanks

Report message to a moderator

Re: Writing to CSV or excel using UTL_FILE in ORACLE [message #600555 is a reply to message #600554]

Thu, 07 November 2013 17:21

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Code I posted will produce:

"AA
BB
CC"

not:

"AA"
"BB"
"CC"

SY.

Report message to a moderator

Re: Writing to CSV or excel using UTL_FILE in ORACLE [message #600558 is a reply to message #600554]

Thu, 07 November 2013 23:41

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

nathran wrote on Fri, 08 November 2013 04:00

But i need to show them as shown below in a single cell.

"AA"
"BB"
"CC"

But not like
"AA""BB""CC"

SQL> WITH TBL AS
  2   (SELECT 'AA' COL1, 'BB' COL2, 'CC' COL3 FROM DUAL)
  3  SELECT '"' || LISTAGG (COL1, CHR(10)) WITHIN
  4   GROUP (ORDER BY ROWID) || '"' || CHR (10) || '"' || LISTAGG (COL2, CHR(10)) WITHIN
  5   GROUP (ORDER BY ROWID) || '"' || CHR (10) || '"' || LISTAGG (COL3, CHR(10)) WITHIN
  6   GROUP (ORDER BY ROWID) || '"' || CHR (10) CELL_CONTENT
  7    FROM TBL;
 
CELL_CONTENT
--------------------------------------------------------------------------------
"AA"
"BB"
"CC"

Report message to a moderator

Re: Writing to CSV or excel using UTL_FILE in ORACLE [message #600575 is a reply to message #600558]

Fri, 08 November 2013 06:36

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Based on "but values has to written into a single cell", it is not what OP requested. If I got it right, OP wants to combine column values from all rows into a single cell where each value is on next line. Then, using code I posted on , for example, DEPT table:

SELECT  '"' || LISTAGG(deptno,chr(10)) WITHIN GROUP(ORDER BY ROWID) || '","' ||
        LISTAGG(dname,chr(10)) WITHIN GROUP(ORDER BY ROWID) || '","' ||
        LISTAGG(loc,chr(10)) WITHIN GROUP(ORDER BY ROWID) || '"'
  FROM  dept
/

'"'||LISTAGG(DEPTNO,CHR(10))WITHINGROUP(ORDERBYROWID)||'","'||LISTAGG(DNAME,CHR(
--------------------------------------------------------------------------------
"10
20
30
40","ACCOUNTING
RESEARCH
SALES
OPERATIONS","NEW YORK
DALLAS
CHICAGO
BOSTON"


SCOTT@orcl >

And if OP saves it to a SCV file, file will have:

"10
20
30
40","ACCOUNTING
RESEARCH
SALES
OPERATIONS","NEW YORK
DALLAS
CHICAGO
BOSTON"

Now opening such file with Excel will display a spreadsheet with one row and three cells where each word would be on a separate line.

SY.

Report message to a moderator

Previous Topic:	How to count different items with one sentence instead of above?
Next Topic:	Dynamic Condition in STATIC SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 18:18:22 CDT 2024