CODEWITHSUNDEEP
sqloracle-databasesqlplus
user2175401
user2175401

Reputation: 71

Spooling sql to a CSV when data contains commas

I'm trying to spool a sql query to a CSV file using a command prompt, however one column of data I'm returning contains comma's in the data and is causing the data to go into the next column. Is there a way to get around this? Ideally, I would like this particular column returned in the middle of query, not at the end.

I'm currently using the following commands:

set termout off;
set colsep ',';
set underline off;
set feedback off;
set pagesize 0;
set linesize 1500;
set trimspool on;

I know the set colsep ',' command is the issue however, haven't been able to figure out what to replace it with. Does anyone have any recommendations?

Also, right before my sql query, I'm using a select statement to pull the header information. Select 'a, b, c' from dual; Not sure if that makes a difference in the answer or not.

Upvotes: 7

Views: 24209

Answers (3)

user3600179
user3600179

Reputation: 21

You can modify your select query like

select REPLACE(column_name, ',',' ') column name from table_name

This will replace comma value from your column data with space.

Upvotes: 2

Wilduck
Wilduck

Reputation: 14096

Two potential answers. Neither of them perfect. If you have commas in some of the results, but no pipes (|), you could switch to a pipe-delimited with

set colsep '|'

Most software that can read csv will do just fine with that format.

If that doesn't work for you, you can realize that to treat commas within a column, you'll need to wrap every data item in quotes:

"data 1","data 2","data,with,commas"

To do this, each separator will need to be "," so you can

set colsep '","'

This will not have quotation marks at the beginning and ending of each line, so you can then wrap every line in quotes with sed:

sed 's/^[^$]*$/"&"/'

Upvotes: 7

BellevueBob
BellevueBob

Reputation: 9618

You can modify your query that returns the result set by surrounding that column with double-quotes. Assuming b is the culprit:

select a
      , '"' || trim(B) || '"' as b
      , c
from your_table;

Proper syntax depends on your RDBMS version of course.

Upvotes: 1

Related Questions