Oracle Spool to CSV Formatting Issue

Question

I wish to export a two column table to CSV using the Oracle Spool. When I execute the export, the first column formats fine, the second column however spills over into a third column in XLS. The contents of the second column in the DB are quite long.

Below is what I am trying:

column MachineName Format a20;
column OperatorName Format a32000;
set colsep ,
set echo off
set feedback off
set heading on
set linesize 32000
set pagesize 0
set termout off
set trim off
set trimspool on
set array 100
set underline off
set wrap off
set flush off
set verify off
set embedded on

Below are the results. In this example, the "Lucio Victor" should be in the same column as "Revolo Borja". (In the DB, this is all in one column)

MACHINENAME         ,OPERATORNAME
CM101               ,Revolo Borja, Lucio Victor
CM101               ,Revolo Borja, Lucio Victor
CM101               ,Revolo Borja, Lucio Victor
CM101               ,Revolo Borja, Lucio Victor

Results should be

MACHINENAME         ,OPERATORNAME
CM101               ,Revolo Borja Lucio Victor
CM101               ,Revolo Borja Lucio Victor
CM101               ,Revolo Borja Lucio Victor
CM101               ,Revolo Borja Lucio Victor

for what it's worth, in SQLcl (still in beta), you can have your output automatically formatted to CSV. SQLcl is a new command line interface developed by the SQL Developer team — thatjeffsmith
– thatjeffsmith, Commented Jul 29, 2015 at 13:10

davegreen100 · Accepted Answer · 2015-07-29 11:09:32Z

1

put " around your operatorname column, excel is reading the comma as a field delimeter

answered Jul 29, 2015 at 11:09

davegreen100

2,1253 gold badges15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

italian_homes Over a year ago

I think I have just figured out why....but don't know how to fix. When I look at the raw data for that column it is like this:

Collectives™ on Stack Overflow

Oracle Spool to CSV Formatting Issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related