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.
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.
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)
#!/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
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)
#!/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