Community

    • Login
    • Search
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Search

    Replace comma in text between quotes

    Help wanted · · · – – – · · ·
    2
    3
    1884
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • Stephen Yorke
      Stephen Yorke last edited by

      I have numerous fields in an input file where it is like:
      field1, field2, “field3, firstname”, field4, field5

      The end result is that I want to replace all of the commas with | except the items within the quotes and remove the quotes.

      I’m not sure this is best done in Notepad++ or not.
      I wasn’t able to figure out a way to do the full change in Notepad++ so I was attempting to do this in multiple steps. Thinking I could

      • modify the comma within the quotes to some other delimiter,
      • modify all of the commas to the or bar |
      • then modify the other delimited created in step 1 back to a comma and then remove the quotes

      Any assistance is appreciated, thanks

      1 Reply Last reply Reply Quote 0
      • Terry R
        Terry R last edited by

        @Stephen-Yorke said:

        I’m not sure this is best done in Notepad++ or not.

        I’m thinking Excel might be a better option. The comma is the “default” delimiter, however another character can become the delimiter. The quotes exist due to the current delimiter being ALSO part of a field. So when you change the delimiter Excel would not need to quote that field anymore.

        Yes if could be done with regexs, possibly as 3 steps just as you suggest although I think 1 regex might also achieve it. Unfortunately I’m not in a position to provide one right now.

        I do wonder though why this is necessary. Most programs reading in delimited data know how to deal with it correctly, such as your situation.

        Terry

        1 Reply Last reply Reply Quote 1
        • Terry R
          Terry R last edited by Terry R

          @Stephen-Yorke
          I have now created a regex that will only target commas (,) outside of quoted fields.
          So we have (use search mode as regular expression and wraparound ticked).
          Find What:,\h*(?![^,]+”)
          Replace With:| <----- note there is a space after the | character. This is to put a space after every |.

          After running that you then can remove all quotes. I haven’t given you the regex for that, I will leave that for you to do, it is a very easy one to create.

          Give it a go and let us know if it suits your needs. Although your 4th field is quoted, this will work on leaving ANY field alone if quoted.

          Terry

          1 Reply Last reply Reply Quote 2
          • First post
            Last post
          Copyright © 2014 NodeBB Forums | Contributors