Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

HELP with AWK or SED. Need to replace the commas between double quotes in CSV file

Login or Register to Reply

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting HELP with AWK or SED. Need to replace the commas between double quotes in CSV file
# 1  
Old 07-12-2012
HELP with AWK or SED. Need to replace the commas between double quotes in CSV file
Hello experts,

I need to validate a csv file which contains data like this:
Sample.csv
"ABCD","I",23,0,9,,"23/12/2012","OK","Street,State, 91135",0
"ABCD","I",23,0,9,,"23/12/2012","OK","Street,State, 91135",0

I just need to check if all the records contain exactly the number of expected fields. In this case NF=10.
How do i go about this ? I guess if i convert the commas between quotes to something else (say, pipe), it can be easily validated using :
Code:
awk -F, '{print NF}' <file>

What is the simplest way to achieve this.
Please help.

Thanks Gurus!!

---------- Post updated at 02:10 AM ---------- Previous update was at 02:08 AM ----------

Sample output must look like this :
 
"ABCD","I",23,0,9,,"23/12/2012","OK","Street

---------- Post updated at 02:13 AM ---------- Previous update was at 02:10 AM ----------

Sample output:

"ABCD","I",23,0,9,,"23/12/2012","OK","Street|State| 91135",0
"ABCD","I",23,0,9,,"23/12/2012","OK","Street|State| 91135",0

Bad editing above. Sorry.
Last edited by shell_boy23; 07-12-2012 at 04:09 AM.. Reason: Adding sample output
# 2  
Old 07-12-2012
Code:
$ nawk -F\" -v OFS=\" '{gsub(/,/,"\|",$10)}1' input.txt
"ABCD","I",23,0,9,,"23/12/2012","OK","Street|State| 91135",0 
"ABCD","I",23,0,9,,"23/12/2012","OK","Street|State| 91135",0

This User Gave Thanks to itkamaraj For This Post:
shell_boy23
# 3  
Old 07-12-2012
Code:
perl -F, -lane 'print join(",",@F[0..7]),",",join("|",@F[8..10]),",",$F[-1]' inputfile

This User Gave Thanks to balajesuri For This Post:
shell_boy23
# 4  
Old 07-12-2012
Try:
Code:
awk 'NR%2-1{gsub(/,/,"|")}1' RS=\" ORS=\" infile

Code:
"ABCD","I",23,0,9,,"23/12/2012","OK","Street|State| 91135",0
"ABCD","I",23,0,9,,"23/12/2012","OK","Street|State| 91135",0



--
Here is an alternative format:
Code:
awk 'NR%2{gsub(/,/,"|")}1' RS=\" ORS=\" infile

Code:
"ABCD"|"I"|23|0|9||"23/12/2012"|"OK"|"Street,State, 91135"|0
"ABCD"|"I"|23|0|9||"23/12/2012"|"OK"|"Street,State, 91135"|0

This User Gave Thanks to Scrutinizer For This Post:
shell_boy23
# 5  
Old 07-12-2012
Thanks, Scrutinizer. This works perfectly fine.

---------- Post updated at 03:40 AM ---------- Previous update was at 03:09 AM ----------

Hi Scrutinizer,

I'm going with the below logic you suggested:
Code:
awk 'NR%2-1{gsub(/,/,"|")}1' RS=\" ORS=\" infile

I will need to revert this file to the original format after validation.
how can we do that with a similar awk command?

---------- Post updated at 03:50 AM ---------- Previous update was at 03:40 AM ----------

Got it! Don't bother answering.
I'll use a simple sed substitution to revert the file to original format.
Thanks guys!
# 6  
Old 07-12-2012
Or just use a copy of the original?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

sed command to replace consecutive double quotes

I need to replace consecutive double quotes in a csv file, the data in the file is enclosed in double quotes but there are some places where the quotes are repeating Example is below Incoming data is : "Pacific Region"|"PNG"|"Jimmy""|""| Need output as: "Pacific... (10 Replies)
Discussion started by: abhilashnair
10 Replies

2. Shell Programming and Scripting

Replace Double quotes within double quotes in a column with space while loading a CSV file

Hi All, I'm unable to load the data using sql loader where there are double quotes within the double quotes As these are optionally enclosed by double quotes. Sample Data : "221100",138.00,"D","0019/1477","44012075","49938","49938/15043000","Television - 22" Refurbished - Airwave","Supply... (6 Replies)
Discussion started by: mlavanya
6 Replies

3. 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

4. Shell Programming and Scripting

sed command to replace string that contain blackslash,double quotes

Hi All, I have been trying to replace a string using the sed command string value contain blackslash and double quotes. I am not a expert writer of unix script but do try not to ask question. I have almost given up. Hope you all can give me some suggestion I want to replace a place string... (6 Replies)
Discussion started by: thanush9sep
6 Replies

5. Shell Programming and Scripting

awk to find and replace Double quotes between pipes

Hello, Need a AWK command to find and replace Double Quotes in Pipe delimited files Actully its a CSV file converted to Pipe but the double quotes still exists. So i want to Get rid of them. Example Input 1|2|3|sadsad|"Abc Efg 3"""|dada Output 1|2|3|sadsad|Abc Efg 3"|dada Thanks... (5 Replies)
Discussion started by: krux_rap
5 Replies

6. Shell Programming and Scripting

How to match fields surrounded by double quotes with commas?

Hello to all, I'm trying to match only fields surrounded by double quotes that have one or more commas inside. The text is like this "one, t2o",334,"tst,982-0",881,"kmk 9-l","kkd, 115-001, jj-3",5 The matches should be "one, t2o" "tst,982-0" "kkd, 115-001, jj-3" I'm trying with... (11 Replies)
Discussion started by: Ophiuchus
11 Replies

7. Shell Programming and Scripting

How to delete the commas in a .CSV file that are enclosed in a string with double quotes?

Okay, I would like to delete all the commas in a .CSV file (TEST.CSV) or at least substitute them with empty space, that are enclosed in double quote. Please see the sample file as below: column 1,column 2,column 3,column 4,column 5,column 6,column 7,column 8,column 9,column 10... (8 Replies)
Discussion started by: dhruuv369
8 Replies

8. Shell Programming and Scripting

Preserve commas inside double quotes (perl)

Hi, I have an input file like this $ cat infile hi,i,"am , sam", y hello ,good, morning abcd, " ef, gh " ,ij no, "good,morning", yes, "good , afternoon" from this file I have to split the fields on basis of comma"," however, I the data present inside double qoutes should be treated as... (3 Replies)
Discussion started by: sam05121988
3 Replies

9. Shell Programming and Scripting

Replace double double quotes using AWK/SED

Hi, I have data as "01/22/97-"aaaaaaaaaaaaaaaaa""aaa""aabbbbbbbbcccccc""zbcd""dddddddddeeeeeeeeefffffff" I want to remove only the Consequitive double quotes and not the one which occurs single. My O/P must be ... (2 Replies)
Discussion started by: Bhuvaneswari
2 Replies

10. Shell Programming and Scripting

replace value with double quotes of specific coulmn value in csv file

Hi, I am trying to replace a specific column values in a csv file with double quotes. Example: SNO,NAME,ZIPCODE,RANK 1,Robert,74538,12 2,Sam,07564,13 3,Kim, Ed,12345,14 Desired Output: SNO,NAME,ZIPCODE,RANK 1,Robert Ken,74538,12 2,Sam Mik,"07564",13 3,"Kim, Ed",12345,14 I... (3 Replies)
Discussion started by: techmoris
3 Replies
Login or Register to Ask a Question