Community
    • Login

    replace first two spaces with - third space with tab

    Scheduled Pinned Locked Moved Help wanted · · · – – – · · ·
    3 Posts 2 Posters 1.1k Views
    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.
    • Glen_StromG
      Glen_Strom
      last edited by

      I’m new to notepad++ advanced search & replace

      I am trying to edit a text file for importing into a database table, a sample line is like this:

      dd mon yyyy 1,234.56<tab>4,567.89<tab>…etc… (<tab> denotes a tab between sets of numbers, dd-mon-yyyy are real dates)

      In order to properly bring dd mon yyyy in to a date field, I need it to look like dd-mon-yyyy

      If I put () \b in the search field it finds the spaces I need, but also finds the space after yyyy, which I want replaced by a tab, not a dash

      I need the end result to look like dd-mon-yyyy<tab>1,234.02 & leave the rest of each line alone.

      I think this may be a 2 step process, first step to replace the first 2 spaces with dashes, second step to replace remaining spaces with tabs, but I’m stumped on how best to do it.

      Thanks in advance.

      PeterJonesP 1 Reply Last reply Reply Quote 0
      • PeterJonesP
        PeterJones @Glen_Strom
        last edited by PeterJones

        @Glen_Strom

        I think this may be a 2 step process, first step to replace the first 2 spaces with dashes, second step to replace remaining spaces with tabs

        Nope. A straightforward regex can handle it in one step. my search thought would be “look for one or two digits, space, a 3-letter month, space, a four-digit year, space – capturing each of the tokens from the date into a separate capture group; replace with group1, hyphen, group2, hyphen, group3, tab”. I would then translate that into regex-speak: FIND = (\d{1,2}) (\w+) (\d{4})\x20 (I used \x20 for the final space because it is easier to read/copy/paste in the forum; in your own regex, that could equally be a space character at the end of the FIND instead), REPLACE = ${1}-${2}-${3}\t, SEARCH MODE = regular expression.

        That converts

        11 mon 2021 1,234.56<tab>4,567.89<tab>…etc
        21 xxx 1999 1,234.56<tab>4,567.89<tab>…etc
        

        into

        11-mon-2021	1,234.56<tab>4,567.89<tab>…etc
        21-xxx-1999	1,234.56<tab>4,567.89<tab>…etc
        

        which I think is what you want

        ----

        Do you want regex search/replace help? Then please be patient and polite, show some effort, and be willing to learn; answer questions and requests for clarification that are made of you. All example text should be marked as literal text using the </> toolbar button or manual Markdown syntax. To make regex in red (and so they keep their special characters like *), use backticks, like `^.*?blah.*?\z`. Screenshots can be pasted from the clipboard to your post using Ctrl+V to show graphical items, but any text should be included as literal text in your post so we can easily copy/paste your data. Show the data you have and the text you want to get from that data; include examples of things that should match and be transformed, and things that don’t match and should be left alone; show edge cases and make sure you examples are as varied as your real data. Show the regex you already tried, and why you thought it should work; tell us what’s wrong with what you do get. Read the official NPP Searching / Regex docs and the forum’s Regular Expression FAQ. If you follow these guidelines, you’re much more likely to get helpful replies that solve your problem in the shortest number of tries.

        1 Reply Last reply Reply Quote 3
        • Glen_StromG
          Glen_Strom
          last edited by

          @PeterJones said in replace first two spaces with - third space with tab:

          ${1}-${2}-${3}\t

          Thanks so much, exactly what I was looking for!

          Now that I have it done I’ll look closer at your solution & figure out what it all means.

          It turns out the text file was a little wonky, all of the months but September were 3 letter, and the rest already had a tab like I wanted after the 4 digit year, but the September months had a space. So it was like 31<sp>Aug<sp> 2021<tab>1234.56 and 01<sp>Sept<sp>2021<sp>6789.12 - so it was just a matter of replacing all the Sept with Sep, then running your solution on them, then I ran the () \b in the search with - in the replace for the rest of them.

          In any case, I was able to generate an insert statement & got all my rows into my database table, so thanks again!

          1 Reply Last reply Reply Quote 1
          • First post
            Last post
          The Community of users of the Notepad++ text editor.
          Powered by NodeBB | Contributors