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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Is there a way to handle commas inside the data when generating a csv file from shell script?
# 1  
Old 01-24-2019
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
Code:
Person VARCHAR2(25),
Favorite VARCHAR2(100)

Sample Data
Code:
Greta        rain drop on roses
Liesl          whiskers on kitten
Kurt            chocolate,chocolate cookies

excerpt from code
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
Code:
"Greta", "rain drop on roses"
"Liesl" ,"whiskers on kitten"
"Kurt "," chocolate,chocolate cookies"

currently I get
Code:
Greta, rain drop on roses
Liesl ,whiskers on kitten
Kurt , chocolate,chocolate cookies

which when imported into excel shows 4 columns for Kurt

Last edited by vbe; 01-24-2019 at 02:39 PM.. Reason: code tags
# 2  
Old 01-24-2019
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.
This User Gave Thanks to joeyg For This Post:
# 3  
Old 01-24-2019
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.
# 4  
Old 01-24-2019
Or perhaps:
Code:
set colsep '","'

And then run the output through sed to add the outer two double quotes:
Code:
sqlplus -s myuser/mypassword@mydb | sed 's/.*/"&"/' <<EOF

This User Gave Thanks to Scrutinizer For This Post:
# 5  
Old 01-24-2019
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)
# 6  
Old 01-24-2019
What OS are you running this on?
Can you show us exactly what you are doing?
# 7  
Old 01-24-2019
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)

Code:
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

Code:
#!/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
Code:
PERSON                                            ","FAVORITE                                                                                                                                                                                                                                                                                                                                                                                                                                                       
--------------------------------------------------","----------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                           
Greta                                             ","rain drop on roses                                                                                                                                                                                                                                                                                                                                                                                                                                             
Liesl                                             ","whiskers on kitten                                                                                                                                                                                                                                                                                                                                                                                                                                             
Kurt                                              ","chocolate, chocolate chip cookies

Thanks for your help




Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!

Last edited by RudiC; 01-24-2019 at 05:11 PM.. Reason: Added CODE tags.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script that should remove unnecessary commas between double quotes in CSV file

i have data as below 123,"paul phiri",paul@yahoo.com,"po.box 23, BT","Eco Bank,Blantyre,Malawi" i need an output to be 123,"paul phiri",paul@yahoo.com,"po.box 23 BT","Eco Bank Blantyre Malawi" (5 Replies)
Discussion started by: mathias23
5 Replies

2. Shell Programming and Scripting

Generating CSV from Column data

Hi List, I have a chunk of data like so: User Account Control: User Account Control: User Account Control: User Account Control: Disabled User Account Control: User Account Control: User Account Control: Disabled User Account Control: User Account Control: ... (3 Replies)
Discussion started by: landossa
3 Replies

3. Shell Programming and Scripting

Shell script to extract data from csv file

Hi everyone, I have a csv file which has data with different heading and column names as below. Static Data Ingested ,,,,,,,,,,,,Known Explained Rejections Column_1,column_2,Column_3,Column_4,,Column_6,Column_7,,% Column_8,,Column_9 ,Column_10 ,... (14 Replies)
Discussion started by: Vivekit82
14 Replies

4. UNIX for Dummies Questions & Answers

Shell script to extract data from csv file

Hi Guys, I am new to shell script.I need your help to write a shell script. I need to write a shell script to extract data from a .csv file where columns are ',' separated. The file has 7 columns having values say column 1,column 2.....column 7 as below along with their values. Name, Address,... (7 Replies)
Discussion started by: Vivekit82
7 Replies

5. UNIX for Dummies Questions & Answers

Shell script to extract data from csv file based on certain conditions

Hi Guys, I am new to shell script.I need your help to write a shell script. I need to write a shell script to extract data from a .csv file where columns are ',' separated. The file has 5 columns having values say column 1,column 2.....column 5 as below along with their valuesm.... (1 Reply)
Discussion started by: Vivekit82
1 Replies

6. Shell Programming and Scripting

shell script to remove extra commas from CSV outp file

Name,,,,,,,,,,,,,,,,,,,,Domain,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Contact,Phone,Email,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Location -----------------------,------------------------------------------------,-------,-----,---------------------------------,------------------------------------ ----... (1 Reply)
Discussion started by: sreenath1037
1 Replies

7. Shell Programming and Scripting

Read data from .csv file through shell script & modify

I need to read data from a file called "test.csv" through shell script where the file contains values like name,price,descriptor etc. There are rows where descriptor (& in some rows name) are written as string & other characters like "car_+" OR "bike*" etc where it should contains strings like... (3 Replies)
Discussion started by: raj100
3 Replies

8. Shell Programming and Scripting

Exporting data as a CSV file from Unix shell script

Friends...This is the first time i am trying the report generation using shell script... any suggestions are welcome. Is there a way to set the font size & color when i am exporting the data from unix shell script as a CSV file ? The following sample data is saved as a .csv file in the... (2 Replies)
Discussion started by: appu2176
2 Replies

9. Shell Programming and Scripting

how to handle , in data where separator also commas in awk script

TEST_HEME,"SubNetwork=ONRM_RootMoR,SubNetwork=ARNC1",CELL when I split by FS="," then $0=TEST_HEME $1="SubNetwork=ONRM_RootMoR $2=SubNetwork=ARNC1" but I need this will be single value "SubNetwork=ONRM_RootMoR,SubNetwork=ARNC1" (4 Replies)
Discussion started by: Hemendra
4 Replies

10. Shell Programming and Scripting

Shell Script to Load data into the database using a .csv file and .ctl file

Since i'm new to scripting i'm findind it difficult to code a script. The script has to be an executable with 2 paramters passed to it.The Parameters are 1. The Control file name(.ctl file) 2. The Data file name(.csv file) Does anybody have an idea about it? :confused: (3 Replies)
Discussion started by: Csmani
3 Replies
Login or Register to Ask a Question

Featured Tech Videos