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
Dan KaibAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

woolmilkporcCommented:
Why not just

sed 's/\("[0-9]*\),\([0-9]*\),\([0-9]*"\)/\1;\2;\3/' IN.CSV > OUT.CSV
0
Dan KaibAuthor 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
0
woolmilkporcCommented:
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
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Dan KaibAuthor 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
0
Dan KaibAuthor 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
0
woolmilkporcCommented:
Here is a better solution:

awk -F"\"" '{OFS="\""; for (k=2; k<=NF; k+=2) gsub(",",";",$k); print}' IN.CSV
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
woolmilkporcCommented:
Please note that I edited my comment above!
0
Dan KaibAuthor 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
0
woolmilkporcCommented:
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!
1
Dan KaibAuthor 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
sed

From novice to tech pro — start learning today.