CODEWITHSUNDEEP
bashcsvunixawksed
John
John

Reputation: 1

I want a command to replace extra commas in a comma delimited csv file

I have below records in comma delimited csv file all fields values are under quotes(“”)

Sample record 1:

“1234”,”0”,”1”,2012-12-01 11:38:00”,”Dear ABC thank you for ,your, email.
If you have any further query please contact us.

Thanks & Regards,

John

Services abc company”,”Null”,”1”,”John”

Sample record 2:

“789”,”0”,”1”,2012-09-01 11:38:00”,”Dear XYZ thank you for “your”, email.
If you have any further query please contact us.

Thanks & Regards,

Max

Services department abc company”,”Null”,”1”,”Max”

Note: please help me with sed/awk commands or unix script using read line.

Solution i have tried:

sed -e ‘s/\([^”]\)”,\([^”]\)/\1~\2/g’ -e ‘s/\([^”]\),\([^”]\)/\1~\2/g’ file.csv

In the first part of sed I am removing text with the below pattern from record 2: *your”,

And in the second part, I am removing the extra comma in between normal text from record 1 : ,your,

Expectations: Remove all extra commas from records as below:

Output record 1:

“1234”,”0”,”1”,2012-12-01 11:38:00”,”Dear ABC thank you for your email.
If you have any further query please contact us.

Thanks & Regards

John
Services abc company”,”Null”,”1”,”John”

Output record 2:

“789”,”0”,”1”,2012-09-01 11:38:00”,”Dear XYZ thank you for “your” email.
If you have any further query please contact us.

Thanks & Regards

Max

Services department abc company”,”Null”,”1”,”Max”

Upvotes: 0

Views: 127

Answers (1)

sseLtaH
sseLtaH

Reputation: 11227

Using sed

$ sed -E 's/ ,?(“?[[:alnum:]]+”?),? / \1 /g' file.csv
“1234”,”0”,”1”2012-12-01 11:38:00”,”Dear ABC thank you for your email.
If you have any further query please contact us.

Thanks & Regards

John

Services abc company”,”Null”,”1”,”John”

“789”,”0”,”1”,2012-09-01 11:38:00”,”Dear XYZ thank you for “your” email.
If you have any further query please contact us.

Thanks & Regards,

Max

Services department abc company”,”Null”,”1”,”Max”

Upvotes: 1

Related Questions