Is there a way to handle commas inside the data when generating a csv file from shell script?

I am extracting data via sql query and some of the data has commas. Output File must be csv and I cannot update the data in the db (as it is used by other application).

Example

table FavoriteThings

Person VARCHAR2(25),
Favorite VARCHAR2(100)

Sample Data

Greta        rain drop on roses
Liesl          whiskers on kitten
Kurt            chocolate,chocolate cookies

excerpt from code

sqlplus -s myuser/mypassword@mydb <<EOF

set feedback off
set linesize 999
whenever sqlerror exit failure;
set trimspool on
set lines 2000
set pages 0
set colsep ","
set serveroutput off


SPOOL favorites.csv
select Person, Favorite from FavoriteThings 
SPOOL_OFF
EXIT
EOF

Output needs to be

"Greta", "rain drop on roses"
"Liesl" ,"whiskers on kitten"
"Kurt "," chocolate,chocolate cookies"

currently I get

Greta, rain drop on roses
Liesl ,whiskers on kitten
Kurt , chocolate,chocolate cookies

which when imported into excel shows 4 columns for Kurt

I think in sqlplus there is a colsep setting.
set colsep |
to set to a | pipe character

you can then do a manual export into Excel, specifying the | as delimiter or
edit(replace) the , to ; and then the | to , --> and you would have a csv file with ; between elements

Otherwise, perhaps format your output inside you SELECT statement.

1 Like

Thanks for your response. Unfortunately this must be csv separated. The file needs to be automatically generated and the recipient can't handle a pipe delimited file. I don't know how to format the select.

Or perhaps:

set colsep '","'

And then run the output through sed to add the outer two double quotes:

sqlplus -s myuser/mypassword@mydb | sed 's/.*/"&"/' <<EOF
1 Like

Thank you for this suggestion.
set colsep '","' works as a column separator but the sed command is not adding the " at the front or end of each line, somehow it is removing the eol. After running the sed collmand all output appears in 1 line in the file (with no " at the beginning or end)

What OS are you running this on?
Can you show us exactly what you are doing?

No longer all on 1 line but still can't get the beginning and end double quote

my version of linux (gotten by typing uname -a )

Linux myhost 3.8.13-68.3.4.el6uek.x86_64 #2 SMP Tue Jul 14 15:03:36 PDT 2015 x86_64 x86_64 x86_64 GNU/Linux

This is the script

#!/bin/sh

RPTDATE=`date "+%Y%m%d"`

OUTFILE="FavoriteThings.${RPTDATE}.csv"


sqlplus -s myuser/mypassword@mydb <<EOF
SET PAGESIZE 50000
SET COLSEP '","'
SET LINESIZE 500
SET FEEDBACK OFF

SPOOL $OUTFILE
select Person, Favorite from FavoriteThings;
SPOOL OFF
EXIT
EOF

# add the " to beginning and end of each line.

sed 's/.*/"&"/' $OUTFILE

echo "File is done" $OUTFLE

This is what FavoriteThings.20190124.csv has

PERSON                                            ","FAVORITE                                                                                                                                                                                                                                                                                                                                                                                                                                                       
--------------------------------------------------","----------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                           
Greta                                             ","rain drop on roses                                                                                                                                                                                                                                                                                                                                                                                                                                             
Liesl                                             ","whiskers on kitten                                                                                                                                                                                                                                                                                                                                                                                                                                             
Kurt                                              ","chocolate, chocolate chip cookies

Thanks for your help

Please use the code tags for code, like it tells you in the message window. You either need to use the in-place option to sed, or use an intermediate file, like this

sed 's/^.*$/"&"/' ${OUTFILE} >${OUTIFILE}-new
 mv ${OUTIFILE}-new ${OUTIFILE}  

Note also I anchored the match to start and end of line (probably not necessary)

Andrew

1 Like

My SQL is way beyond rusty, so to say corroded away, but couldn't you

select '"' & Person, Favorite & '"' from FavoriteThings;
1 Like

Thanks the intermediate file option worked.

--- Post updated at 08:20 AM ---

RudiC, thanks for your suggestion, unfortunately I could not get it to work.

For sheer curiosity - what be the output of the proposed select... ?

when I tried

#!/bin/sh

RPTDATE=`date "+%Y%m%d"`

OUTFILE="FavoriteThings.${RPTDATE}.csv"

echo "START $0 at `date`"

sqlplus -s myuser/mypassword@mydb <<EOF
SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 500
SET FEEDBACK OFF

SPOOL $OUTFILE
select '"'&Person, &'"'Favorite from FavoriteThings;
SPOOL OFF
EXIT
EOF


echo "END $0 at `date`"

I got

START ./TryFavoriteThings.sh at Fri Jan 25 19:47:50 GMT 2019
Enter value for person: old   1: select '"'&Person, &'"'Favorite from FavoriteThings
new   1: select '"'SPOOL OFF, &'"'Favorite from FavoriteThings
select '"'SPOOL OFF, &'"'Favorite from FavoriteThings
                       *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Well, my memory may have deceived me - as "&" seems to be wrong, what is the "string concatenation" operator in SQL?

EDIT: Found this on the www :