Oracle command line to export table data to csv using spool having issues when data has double quotes(") in it

Question

I am having ORACLE 11 version installed on my linux machine. I am trying to export data using below. It is working fine for all the tables except for tables having double quotes(") as data in any of column. Below is my code

set echo off
set feedback off
set linesize 30000
set recsep off
set trimspool off
set verify off
set pagesize off
spool "/home/rsau12/file.csv";
SELECT  '"'||COLUMN_1||'"'||','
'"'||COLUMN_2||'"'||','
'"'||COLUMN_3||'"'||','
        ...
'"'||COLUMN_N||'"'
FROM TABLE; 
spool off;

Please let me know how to handle if data has (") in it

Can you upgrade the client to 12.2? That version introduced the set markup csv command, which greatly simplifies exports. (Btw, in addition to double quotes, you also have to worry about commas. Many of the "simple" solution for CSV files ignore most of the CSV features.) — Jon Heller
– Jon Heller, Commented May 31, 2020 at 0:18
unfortunately no. Yes I am aware of that feature but I can't use that so I specified oracle 11 in question.thanks for reply. — user1024962
– user1024962, Commented May 31, 2020 at 2:50
@JonHeller is talking about the client, not the database. You just have to add an instant client version 12.2 with it's sqlplus. Is that also not possible? — wolφi
– wolφi, Commented May 31, 2020 at 16:26
Yes I checked with System Admin before posting the question here .. They said it is not possible at this time as there is freeze for some time on any new installation. — user1024962
– user1024962, Commented May 31, 2020 at 16:36

alvalongo · Accepted Answer · 2020-06-01 03:29:52Z

1

You need to replace double quotes(") with two double quotes(""):

select        chr(34)||replace(COLUMN_1,chr(34),chr(34)||chr(34))||chr(34)
       ||','||chr(34)||replace(COLUMN_2,chr(34),chr(34)||chr(34))||chr(34)
       ||','||chr(34)||replace(COLUMN_3,chr(34),chr(34)||chr(34))||chr(34) as data
from table_1

Example:

if some columns are numeric you don't need doble quotes (").

"Los Angeles","California, USA","""Hello World"" say John"

answered Jun 1, 2020 at 3:29

alvalongo

6164 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

Oracle command line to export table data to csv using spool having issues when data has double quotes(") in it

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related