Help with Replace Text Regex

elicohen
elicohen Contributor
in Magic ETL

Hi!  Struggling to figure out how to replace this: { "value":"Email", "id":"13469" }  with this: Email

 

Any ideas?

Comments

  • GrantSmith
    GrantSmith Coach

    You can utilize a two step regex in a Replace Text block. See the attached images on how it's done. This is under the assumption it's in the same order / format each time.

     

    It would be easier if Domo supported RegEx Match Groups so you could easily just utilize the number shortcuts for the groups it matches within parentesis.

     

    The other option would possibly utilize the regex functions in your database if that's an option.

     

    Screen Shot 2020-01-30 at 8.41.51 AM.png

    Make sure you select RegEx from both gear wheels under the search term

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Unknown
    Unknown

    Unfortunately,Screen Shot 2020-02-04 at 4.12.32 PM.pngScreen Shot 2020-02-04 at 4.11.58 PM.png this did not work.  I made sure that regex was selected for both options... here's a screen shot of what I included in the ETL step and the output....  any other ideas?

  • zcameron
    zcameron Domo Employee

    I would suggest using this RegEx under the term to search for:

     

    ^{"value"\s*:\s*"(.*)",.*}$

     

    And put $1 in the Replace field.

     

    You'd only need one entry that way. It will capture everything after the colon and quote and before the following quote. Can you give that a try and see how that goes?

  • GrantSmith
    GrantSmith Coach

    You might also need to have a space inbetween the first { and the "

     

    ^{ *"value"\s*:\s*"(.*)",.*}$

    This version would handle if there is a leading space or not.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Unknown
    Unknown

    It doesn't like the regex.  Have a look at the screenshot.  Screen Shot 2020-02-04 at 5.11.45 PM.png

  • Unknown
    Unknown

    I tried it with the space as well.  No dice.

  • zcameron
    zcameron Domo Employee

    Sorry, my fault... I forgot to escape the curly braces. The RegEx engine that MagicETL uses will require that.

     

    ^\{\s*"value"\s*:\s*"(.*)",.*\}$

  • Unknown
    Unknown

    Still not working.  Unfortunately.

  • zcameron
    zcameron Domo Employee

    Very odd... It worked in my testing with the example you provided. Can you post some screen shots of what you're seeing now, or give some more detail around how it isn't working?

  • Unknown
    Unknown

    sure.  Here goes....  First Image is output.  Second image is what we did.

     

    Screen Shot 2020-02-05 at 1.14.43 PM.pngScreen Shot 2020-02-05 at 1.13.27 PM.png

  • zcameron
    zcameron Domo Employee

    I wonder if we're seeing some whitespace after or before the value. What if you added in a check for that like this:

     

    ^.*\{\s*"value"\s*:\s*"(.*)",.*\}.*$

     

     

  • Unknown
    Unknown

    deleted (dupe)

  • Unknown
    Unknown

    Still no dice.  I copied your code in, reran and took a screenshot of the output.  Still not replaced.  See:

     

    Screen Shot 2020-02-05 at 3.56.02 PM.png

  • Blind5quirrel
    Blind5quirrel Member

    For anyone else running into this issue, you need to select the "Use RegEx" option from the field settings where you add your actual RegEx. It won't work until you do this.

    Use RegEx.PNG


  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    Just in case you want to parse multiple objects gracefully. You can do this in MySQL. but actually Magic 2.0 with a little creativity supports a similar workflow with the Add Formulas tile.


    https://www.youtube.com/watch?v=oYcpYE7DiV4


    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories