CODEWITHSUNDEEP
oracle-databasesqlplus
user4758229
user4758229

Reputation: 259

How to include double quotes around string result in sqlplus?

We are using SQL*Plus to export data to a csv file. We have both decimal and text columns, and each field is delimited by comma (,) but some text columns also contain commas. While importing data into some other DB we are seeing that these commas in the text are treated as column separators. Can anyone tell me how I can overcome this?

set tab off
SET head OFF
SET feedback OFF
SET pagesize 0
SET linesize 3000;
SET colsep ,
set trimspool on
set trimout on
set trims on
set null ""
set rowprefetch 2
set feedback off
set arraysize 1000
set PAGESIZE 50000
set STATEMENTCACHE 20
set numwidth 15
column coulmnName format 999999999.99
column coulmnName format 999999999.99
column coulmnName format 999999999.99
column coulmnName format 999999999.99

Upvotes: 2

Views: 16671

Answers (4)

Larry R. Irwin
Larry R. Irwin

Reputation: 121

If you are in a linux environment, you can use ~ as the separator and then sed to fix up the output. with this method, you don't have to know what the table content is in order to create the csv file. The script below wraps every field in double-quotes and drops the trailing delimiter:

ORDER="01"
TABLE="MY_DATA_TBL"
CONN="127.0.0.1:31521/abc0008.world" #via ssh tunnel
sqlplus -L login/pswd@//${CONN}<<EOF >/dev/null
  set pagesize 4000;
  set verify off;
  set feedback off;
  set long 99999;
  set linesize 32767;
  set trimspool on;
  col object_ddl format A32000;
  set colsep ~;
  set underline off;
  set headsep off;
  spool ${ORD}${TABLE}.tmp1;
  select * from ${TABLE};
EOF
cat ${ORD}${TABLE}.tmp1 | sed -e "s/\"/'/" -e 's/ * / /g' -e "s/^ //" -e "s/ ~/~/g" -e "s/~ /~/g" | tail -n +11 | head -n -1 > ${ORD}${TABLE}.tmp2
head -n 1 ${ORD}${TABLE}.tmp2 | sed -e "s/$/~/" > ${ORD}${TABLE}.tmp3
tail -n +2 ${ORD}${TABLE}.tmp2 >> ${ORD}${TABLE}.tmp3
cat ${ORD}${TABLE}.tmp3 | sed -e "s/^/\"/" -e "s/~$/\"/" -e "s/~/\",\"/g" > ${ORD}${TABLE}.csv

Upvotes: 0

MT0
MT0

Reputation: 168106

Just concatenate a double quote character to the start and end of columns with a string data type:

SELECT '"' || stringColumnA || '"' AS stringColumnA,
       numberColumnB,
       '"' || stringColumnC || '"' AS stringColumnC
       -- ...
FROM   table_name;

If your column already contains double quotes then escape them by doubling them up:

SELECT '"' || REPLACE( stringColumnA, '"', '""' ) || '"' AS stringColumnA,
       numberColumnB,
       '"' || REPLACE( stringColumnC, '"', '""' ) || '"' AS stringColumnC
       -- ...
FROM   table_name;

Upvotes: 2

William Robertson
William Robertson

Reputation: 16001

If you are able to upgrade to SQL*Plus 12.2, you can use set markup csv on:

SQL> set markup csv on
SQL> select * from departments;

"DEPARTMENT_ID","DEPARTMENT_NAME","MANAGER_ID","LOCATION_ID"
10,"Administration",200,1700
20,"Marketing",201,1800
30,"Purchasing",114,1700
40,"Human Resources",203,2400
50,"Shipping",121,1500
60,"IT",103,1400
70,"Public Relations",204,2700
80,"Sales",145,2500
90,"Executive",100,1700
100,"Finance",108,1700

Upvotes: 5

Littlefoot
Littlefoot

Reputation: 142798

Don't set column separator to a comma, but to something else (such as pipe | or exclamation ! or hash # or some other character that doesn't exist in data you're about to export). You'd, of course, use that sign as a separator while loading data into the target database.

Upvotes: 0

Related Questions