Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need Help - comma inside double quote in comma separated csv,

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need Help - comma inside double quote in comma separated csv,
# 1  
Old 09-02-2012
Java Need Help - comma inside double quote in comma separated csv,
Hello there,

I have a comma separated csv , and all the text field is wrapped by double quote. Issue is some text field contain comma as well inside double quote. so it is difficult to process.

Input in the csv file is ,
Code:
1,234,"abc,12,gh","GH234TY",34


I need output like below,
1~234~"abc,12,gh"~"GH234TY"~34 (separator to be replaced by ~)

or
1,234,"abc|12|gh","GH234TY",34 (comma inside double quote to be replaced by |)


Note : Comma inside double quote can present in any column. It is not fixed that Comma inside double quote will present in 3rd column only.

I have created one script to do this. It is working fine but time consuming.

Can I get any special command like sed, which will directly give me this output.


Many Thanks
Uttam
Last edited by radoulov; 09-03-2012 at 06:01 AM.. Reason: Subject line changed.
# 2  
Old 09-02-2012
Code:
awk -F\" 'BEGIN{OFS=FS;} {for(i=1;i<=NF;i=i+2){gsub(/,/,"|",$i);} print $0;}' input_file

Or if you want "~" as field seperator
Code:
awk -F\" 'BEGIN{OFS=FS;} {for(i=1;i<=NF;i=i+2){gsub(/,/,"~",$i);}}1' input_file

This User Gave Thanks to msabhi For This Post:
Uttam Maji
# 3  
Old 09-02-2012
@msabhi: solutions working fine, except that you need to start first soln's for loop at i=2 to produce what the OP requested.
This User Gave Thanks to RudiC For This Post:
Uttam Maji
# 4  
Old 09-03-2012
Hello there,

I tried both the command , but they are giving error .


1) awk -F\" 'BEGIN{OFS=FS;} {for(i=1;i<=NF;i=i+2){gsub(/,/,"|",$i);} print $0;}' RA_003.txt

Code:
awk: syntax error near line 1 
awk: illegal statement near line 1


2) awk -F\" 'BEGIN{OFS=FS;} {for(i=1;i<=NF;i=i+2){gsub(/,/,"~",$i);}}1' RA_003.txt

Code:
awk: syntax error near line 1 
awk: illegal statement near line 1
awk: syntax error near line 1 
awk: bailing out near line 1

Could you please help ?

Actually I am not able to rectify such big awk command in unix.



Thanks
Uttam
# 5  
Old 09-03-2012
use nawk instead of awk if you are in solaris.

Cheers,
Ranga Smilie
This User Gave Thanks to rangarasan For This Post:
Uttam Maji
# 6  
Old 09-03-2012
Code:
awk -F"\"" '{for(i=1;i<=NF;i++) {if(NF%2){ gsub(",","~",$i) }};print}' OFS=\" filename

---------- Post updated at 04:07 PM ---------- Previous update was at 04:02 PM ----------

OR TRY THIS

Code:
awk 'BEGIN{ORS=RS="\""}NR%2{gsub(",","~")}{print}' filename

# 7  
Old 09-04-2012
Thanks a lot , :-), below two are working properly.

Code:
nawk -F\" 'BEGIN{OFS=FS;} {for(i=1;i<=NF;i=i+2){gsub(/,/,"|",$i);} print $0;}' inputfile

Code:
nawk -F\" 'BEGIN{OFS=FS;} {for(i=1;i<=NF;i=i+2){gsub(/,/,"~",$i);}}1' inputfile

Last edited by Franklin52; 09-05-2012 at 03:53 AM.. Reason: Please use code tags for data and code samples
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to extract fields from a CSV i.e comma separated where some of the fields having comma as value?

can anyone help me!!!! How to I parse the CSV file file name : abc.csv (csv file) The above file containing data like abv,sfs,,hju,',',jkk wff,fst,,rgr,',',rgr ere,edf,erg,',',rgr,rgr I have a requirement like i have to extract different field and assign them into different... (4 Replies)
Discussion started by: J.Jena
4 Replies

2. Shell Programming and Scripting

awk to parse comma separated field and removing comma in between number and double quotes

Hi Experts, Please support I have below data in file in comma seperated, but 4th column is containing comma in between numbers, bcz of which when i tried to parse the file the column 6th value(5049641141) is being removed from the file and value(222.82) in column 5 becoming value of column6. ... (3 Replies)
Discussion started by: as7951
3 Replies

3. Shell Programming and Scripting

awk comma seperated value within double quote

Hi, I have a data file separated by comma, data enclosed by "" head file.txt "HD","Sep 13 2016 1:05AM","0001" "DT","273093045","192534" "DT","273097637","192534" .. I want to get the 3rd column value (0001) to be assigned to my variable I tried FILE_VER=`cat file.txt | awk... (2 Replies)
Discussion started by: Prasannag87
2 Replies

4. Shell Programming and Scripting

Insert single quote on every word separated by comma

Hello, I have a text file as:-ABC BCD CDF DEF EFGI need to convert as 'ABC', 'BCD', 'CDF', 'DEF', 'EFG' using a unix command anybody can help me out on this. Regards, Jas Please wrap all code, files, input & output/errors in CODE tags. It makes them easier to read and preserves... (12 Replies)
Discussion started by: jassi10781
12 Replies

5. Shell Programming and Scripting

Convert column to quote and comma separated row

Hi, I have a list of tables in a file.txt C_CLAIM C_HLD C_PROVIDER I want the output to be 'C_CLAIM','C_HLD','C_PROVIDER' Currently I'm usin awk and getting output which is almost correct but still has minor defects awk -vORS="','" '{ print $1 }' file.txt The output of... (4 Replies)
Discussion started by: wahi80
4 Replies

6. UNIX for Dummies Questions & Answers

Add a field separator (comma) inside a line of a CSV file

Hi... I can't find my little red AWK book and it's been a long while since I've awk'd. But I need to take a CSV file and convert the first word of the fifth field to its own field by replacing a space with a comma. This is for importing a spreadsheet of issues into JIRA... Example: a line... (9 Replies)
Discussion started by: Tawpie
9 Replies

7. Shell Programming and Scripting

awk print - fields separated with comma's need to ignore inbetween double quotes

I am trying to re-format a .csv file using awk. I have 6 fields in the .csv file. Some of the fields are enclosed in double quotes and contain comma's inside the quotes. awk is breaking this into multiple fields. Sample lines from the .csv file: Device Name,Personnel,Date,Solution... (1 Reply)
Discussion started by: jxrst
1 Replies

8. Shell Programming and Scripting

How to redirect in comma separated csv from grep

Hi, I am newbie in unix. Could someone tell me how do I redirect my grep output to a csv/excel ? I have used below command but the outputs are appearing in one column Not in different column. grep -e cmd -e cmd1 test.txt | cut -f 5 | sort | uniq -c> op.csv I do not understand how do I... (14 Replies)
Discussion started by: kmajumder
14 Replies

9. Shell Programming and Scripting

want to remove comma present inside double qoute

Hi friends... I have a dat file like below:- test_file.dat abc,ttt,"""123,89.98",yyu,opp vvv,"23,76.68",w564,"54,98.87",985 i need the remove the comma present inside the double qoute as output:- out_test_file.dat abc,ttt,"""12389.98",yyu,opp vvv,"2376.68",w564,"5498.87",985... (6 Replies)
Discussion started by: gani_85
6 Replies

10. Shell Programming and Scripting

sed removing comma inside double quotes

I have a csv file with lines like the followings 123456,"ABC CO., LTD","XXX" 789012,"DEF LIMITED", "XXX" before I bcp this file to database, the comma in "CO.," need to be removed first. My script is cat <filename> | sed 's/"CO.,"/"CO."/g' but it doesn't work. Can anyone here able to... (2 Replies)
Discussion started by: joanneho
2 Replies
Login or Register to Ask a Question