#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    3
    Rep 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.
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,143
    Rep Power
    9398
    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)?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    3
    Rep 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.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    29
    Rep 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.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    3
    Rep 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:
    ,(?=[^{}]*\})
  10. #6
  11. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,143
    Rep Power
    9398
    I was also thinking it might be easier to replace the {}s with quotes.
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    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

IMN logo majestic logo threadwatch logo seochat tools logo