Avatar of Dan Kaib
Dan Kaib
Flag for United States of America asked on

Replacing multiple embedded commas in a CSV file with sed

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
* sed* AIXShell ScriptingRegular Expressions

Avatar of undefined
Last Comment
Dan Kaib

8/22/2022 - Mon
woolmilkporc

Why not just

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

ASKER
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
woolmilkporc

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Dan Kaib

ASKER
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 Kaib

ASKER
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
ASKER CERTIFIED SOLUTION
woolmilkporc

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
woolmilkporc

Please note that I edited my comment above!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dan Kaib

ASKER
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
woolmilkporc

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 Kaib

ASKER
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23