How to use in Oracle – Output files -Comma, Pipe, or Tab Delimited Output


To generate comma or pipe delimited output for tools such as Excel, Access, and so on while spooling from SQL*Plus, use settings from SQL*Plus such as:


SET COLSEP ,

Or

SET COLSEP |

Example:

set pages 0 feed off
set colsep ,
select
object_name,
object_type,
status,
created
from all_objects
where owner='PUBLIC'
and rownum < 10 /* 9 rows for Sample Output */
;

Output:

ALL_ALL_TABLES ,SYNONYM ,VALID ,01-APR-2004
ALL_ARGUMENTS ,SYNONYM ,VALID ,01-APR-2004
ALL_ASSOCIATIONS ,SYNONYM ,VALID ,01-APR-2004
ALL_CATALOG ,SYNONYM ,VALID ,01-APR-2004
ALL_CLUSTERS ,SYNONYM ,VALID ,01-APR-2004
ALL_CLUSTER_HASH_EXPRESSIONS ,SYNONYM ,VALID ,01-APR-2004
ALL_COLL_TYPES ,SYNONYM ,VALID ,01-APR-2004
ALL_COL_COMMENTS ,SYNONYM ,VALID ,01-APR-2004
ALL_COL_PRIVS ,SYNONYM ,VALID ,01-APR-2004

For pipe delimited output:

set colsep |
select
object_name,
object_type,
status,
created
from all_objects
where owner='PUBLIC'
and rownum < 10 /* 9 rows for Sample Output */
;

Output:

ALL_ALL_TABLES |SYNONYM |VALID |01-APR-2004
ALL_ARGUMENTS |SYNONYM |VALID |01-APR-2004
ALL_ASSOCIATIONS |SYNONYM |VALID |01-APR-2004
ALL_CATALOG |SYNONYM |VALID |01-APR-2004
ALL_CLUSTERS |SYNONYM |VALID |01-APR-2004
ALL_CLUSTER_HASH_EXPRESSIONS |SYNONYM |VALID |01-APR-2004
ALL_COLL_TYPES |SYNONYM |VALID |01-APR-2004
ALL_COL_COMMENTS |SYNONYM |VALID |01-APR-2004
ALL_COL_PRIVS |SYNONYM |VALID |01-APR-2004

For TAB delimited output, you can use the following:

col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set colsep "&TAB"

select
object_name,
status,
created
from all_objects
where owner='PUBLIC'
and rownum < 10 /* 9 rows for Sample Output */
;

Output:

ALL_ALL_TABLES VALID 01-APR-2004
ALL_ARGUMENTS VALID 01-APR-2004
ALL_ASSOCIATIONS VALID 01-APR-2004
ALL_CATALOG VALID 01-APR-2004
ALL_CLUSTERS VALID 01-APR-2004
ALL_CLUSTER_HASH_EXPRESSIONS VALID 01-APR-2004
ALL_COLL_TYPES VALID 01-APR-2004
ALL_COL_COMMENTS VALID 01-APR-2004
ALL_COL_PRIVS VALID 01-APR-2004
Advertisements

4 thoughts on “How to use in Oracle – Output files -Comma, Pipe, or Tab Delimited Output

  1. Hey there,
    the trouble with this solution is, that – depending on the width of the columns – the values are padded with spaces. If you spool the above examples to a CSV file and import them to a spreadsheet, you’ll get the spaces imported, too. Thus, you could as well stick to a fixed length format. Or append the separator manually to each column, like:

    SELECT object_name, ‘;’
    , status, ‘;’
    , …

    Personally, when exporting data for use in Excel and such, I prefer a feature of SQL*Plus >= 10g. It can produce HTML output which can be imported directly to Excel. Example:

    set echo off feedback off
    set markup html on spool on
    spool objects.xls
    select …

    Regards,
    Uwe

    Like

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s