Help to find/replace all commas within curly brackets

Dev Shed Forums Sponsor:

June 27th, 2012, 01:20 PM

mikeysweet

Registered User

Join Date: Jun 2012

Posts: 3

Time spent in forums: 28 m 7 sec
Reputation Power: 0

Help to find/replace all commas within curly brackets

I'm bagging my head on my keyboard trying to see if that would help me, but it's not.

Issue: I have a CSV file that includes a couple of fields that begin and end with curly brackets. Within those brackets, there are commas. Below is a line from this CSV file:

Code:

"A",3,"Bqt- Open Music","Open","Music","Bqt- Open Music",{1,$0.00,2,$20.00,3,$30.00},92,8,3,0,0,0,0,{},,$0.00,0,1,1,1,1,0,0,15,"Open Music",1,0,{},{17,1,18,0,19,0,26,0,27,0,28,0,29,0,30,0,33,0,34,0,35,0,36,0,37,0,38,0,39,0,40,0},0,0

I need to find the commas (,) ONLY within the curly brackets and turn them into pipes (|)

So far, all I can figure out is how to find commas that have digits or dollar sign ($) around them:

Code:

(?<=[\d\$]),

I would like the end results to look like this:

Code:

"A",3,"Bqt- Open Music","Open","Music","Bqt- Open Music",{1|$0.00|2|$20.00|3|$30.00},92,8,3,0,0,0,0,{},,$0.00,0,1,1,1,1,0,0,15,"Open Music",1,0,{},{17|1|18|0|19|0|26|0|27|0|28|0|29|0|30|0|33|0|34|0|35|0|36|0|37|0|38|0|39|0|40|0},0,0

Thanks for all your help with this.

June 27th, 2012, 02:02 PM

requinix

Still alive

Join Date: Mar 2007

Location: Washington, USA

Posts: 12,690

Folding Points: 417516 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 5 Months 1 Week 4 Days 3 h 43 m 47 sec
Reputation Power: 8969

Send a message via Google Talk to requinix

Are you converting them away from commas so that your CSV parsing thing won't misinterpret them as field delimiters? If so, what parser (ie, what's reading the file)?

__________________
Site rules and FAQ | Project Suggestions | mod_rewrite guide | PHP New User Guide and FAQ - PHP Stickies | Regex Resources

June 27th, 2012, 02:17 PM

mikeysweet

Registered User

Join Date: Jun 2012

Posts: 3

Time spent in forums: 28 m 7 sec
Reputation Power: 0

Try not to laugh too much, but I need to open this up in Excel. I have a client who will need to edit the items and it's easier for him to just edit while in Excel.

BUT, your question has peaked my interest. Is there an application that would be better suited to open this in? I'm perfectly happy to open using a different application, replace the troublesome commas with a pipe, re-save as a CSV, and then open it in Excel. Unfortunately, I don't know of a robust application that could distinguish commas within brackets and field-delimiting commas.

June 28th, 2012, 06:51 AM

abareplace

Registered User

Join Date: Dec 2011

Posts: 29

Time spent in forums: 8 h 25 m 9 sec
Reputation Power: 0

You need to search for a comma that is preceded by opening brace. Additionally, there is no closing brace between the opening brace and the comma.

Code:

Search for: (?<={[^}]+),
Replace to: |

This solution uses variable-length lookbehind, which is supported by .NET and several other tools, including my program. Unfortunately, it would not work in PHP or Python.

June 28th, 2012, 09:54 AM

mikeysweet

Registered User

Join Date: Jun 2012

Posts: 3

Time spent in forums: 28 m 7 sec
Reputation Power: 0

This morning, a coworker who was also thinking about this drafted the following regex and it works perfectly. Thanks for your help!

Code:

,(?=[^{}]*\})

June 28th, 2012, 12:33 PM

requinix

Still alive

Join Date: Mar 2007

Location: Washington, USA

Posts: 12,690

Time spent in forums: 5 Months 1 Week 4 Days 3 h 43 m 47 sec
Reputation Power: 8969

I was also thinking it might be easier to replace the {}s with quotes.

June 28th, 2012, 03:02 PM

spacebar208

Contributing User

Join Date: Apr 2012

Location: spaceBAR Central

Posts: 188

Time spent in forums: 2 Days 9 h 10 m 2 sec
Reputation Power: 41

The left/right curly braces could be deleted and the commas changed to a TAB and then they should be able to open the file with excel and it will place each value into a column:

Code:

sed 's/[{|}]//g;s/,/\t/g' test.txt > test.xls

Help to find/replace all commas within curly brackets

Developer Shed Advertisers and Affiliates