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?
# 8  
Old 01-24-2019
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
Code:
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
This User Gave Thanks to apmcd47 For This Post:
# 9  
Old 01-24-2019
My SQL is way beyond rusty, so to say corroded away, but couldn't you



Code:
select '"' & Person, Favorite & '"' from FavoriteThings;

This User Gave Thanks to RudiC For This Post:
# 10  
Old 01-25-2019
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.
# 11  
Old 01-25-2019
For sheer curiosity - what be the output of the proposed select...?
# 12  
Old 01-25-2019
when I tried
Code:
#!/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
Code:
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

# 13  
Old 01-25-2019
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 :

Quote:
SQL Server and Microsoft Access use the + operator.
...


Oracle uses the CONCAT(string1, string2) function or the || operator.
...

MySQL uses the CONCAT(string1, string2, string3...) function.
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