Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
A column value has comma which leads to different columns when exported into a CSV file

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.
Best Answers
-
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
-
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.
Answers
-
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
-
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"
-
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
-
I use Oracle sql developer. Then use the cart tool to extract to csv file
-
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.