We help IT Professionals succeed at work.

Replacing multiple embedded commas in a CSV file with sed

236 Views
Last Modified: 2018-12-14
I have a CSV file with fields containing multiple embedded commas in some fields that I need to change to a different character.

The line below is from a CSV file.

1511,Charleston Newspapers Inc      ,0108,Hamburg PA,"03,11,2017",03/11/2017 ,03
/2017,-1.71,03/13/2017 ,Unpaid,,,,,C210\quser,C210\quser,C210\quser,,No Approval
 Needed,^M

Using the sed command:

sed 's/\("[^,"]*\),\([^"]*"\)/\1;\2/g' IN.CSV > OUT.CSV

The above sed command changed "03,11,2017" to "03;11,2017".  I need to change all the commas in "03,11,2017" to semicolons "03;11;2017".

I am running AIX 7.1

Any suggestions would be greatly appreciated.

TIA,
Dan
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Why not just

sed 's/\("[0-9]*\),\([0-9]*\),\([0-9]*"\)/\1;\2;\3/' IN.CSV > OUT.CSV
Dan KaibProgrammer

Author

Commented:
Hi woolmilkporc,

Thank you for the reply.  That does work if there are 2 commas embedded in the field but not if there is only one or more than two.
I was hoping to change all the commas in the embedded field no matter how many exist in the field.

Thanks again,
Dan
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
This works if the field in question is the only one surrounded by double quotes:

awk -F"\"" '{OFS="\""; gsub(",",";",$2); print}' IN.CSV > OUT.CSV
Dan KaibProgrammer

Author

Commented:
Hi woolmilkporc,

Thank you for the reply.   That works GREAT for a single embedded field but not for multiple embedded fields.
Do you know of a way to handle multiple embedded fields?
The end-users have the potential to put a comma in many different fields; I'm trying to eliminate the commas before they create a problem.
There are users at 110+ stores entering data.

Thanks for your help,
Dan
Dan KaibProgrammer

Author

Commented:
Hi woolmilkporc,

The sed command in my question handles multiple embedded fields but not multiple embedded commas within the field.
Not sure if that helps any.

Dan
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Please note that I edited my comment above!
Dan KaibProgrammer

Author

Commented:
Hi woolmilkporc,

Thank you for the reply.

The only way I found to handle multiple embedded fields with multiple commas is similar to your last awk solution:

sed 's/\("[^,"]*\),\([^"]*"\)/\1;\2/g; s/\("[^,"]*\),\([^"]*"\)/\1;\2/g' IN.CSV > OUT.CSV

This takes out 2 commas from multiple embedded fields.

You have been a GREAT help and provided many options.

Thank you for everything,
Dan
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Seems you didn't notice the changes I made to my comment! I know, I should have been faster or I should have posted a new one, sorry!
Dan KaibProgrammer

Author

Commented:
Hi woolmilkporc,

My fault, I was too quick to close the question figuring there was nothing else to do.
I did see your change right after I closed the question.
I tested it and it is the perfect solution.

I tried to send you a message but I don't think I did it correctly.
It doesn't seem to like the Recipient woolmilkporc.

Thank you again,
Dan