A column value has comma which leads to different columns when exported into a CSV file

User_UH6TP

I have columns which needs to be generated as CSV file. The problem is that one column value has three commas in it. so it creates three seperate columns in CSV file. 

E.g

Select DOC_TITLE AS "Document_Title".

Value: STEELWORK DRAWINGS VESSELS (PLATFORMS, LADDERS, CATWALK) - UNIT 1400 TRAIN 6 STRUC. STEELWORK AREA 8 PLATFORM FOR V-1202

In the CSV file it gives me three columns

Column 1 = STEELWORK DRAWINGS VESSELS (PLATFORMS

Column 2 = LADDERS

Column 3 = CATWALK) - UNIT 1400 TRAIN 6 STRUC. STEELWORK AREA 8 PLATFORM FOR V-1202

I am using Oracle 11g and Oracle developer

What do I do in order to have the entire column value fit into one column in the CSV file

Appreciate any help.

Pēteris Plūms

Hi,

in csv you can have delimiter in text if column is enclosed in ""

try Select '"'||DOC_TITLE||'"' AS "Document_Title" ...

I do not use developer my self, but I assume there should be option for adding qoutes automatically when exporting

cormaco

Make sure to select a value for left and right enclosure when you export:

Then it doesn't matter if your text columns contain commas.

Pēteris Plūms

Hi,

in csv you can have delimiter in text if column is enclosed in ""

try Select '"'||DOC_TITLE||'"' AS "Document_Title" ...

I do not use developer my self, but I assume there should be option for adding qoutes automatically when exporting

User_H3J7U

Oracle SQL Developer has a GUI to export CSV.

SQLcl and Oracle SQL Developer allow to represent result as CSV with sqlformat csv.

SQL> select /*csv*/ 123 col1, 'str with ,,, commas' col2 from dual;
"COL1","COL2"
123,"str with ,,, commas"

Jonathan Lewis

You haven't told us what tool you're using to generate the file, or which version of Oracle.

But if it's SQL*Plus and any recent version of Oracle you could do

set markup csv on
select {list of column} ...

This will produce your data with double-quote marks round each column which will handle your comma problem. (You will also need a few SET options like, "heading off",, "pagesize 0", etc

For further comment see : https://jonathanlewis.wordpress.com/2021/04/13/csv/

Regards

Jonathan Lewis

User_UH6TP

I use Oracle sql developer. Then use the cart tool to extract to csv file

cormaco

Make sure to select a value for left and right enclosure when you export:

Then it doesn't matter if your text columns contain commas.