• Login
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.
  • G
    Glen_Strom
    last edited by Sep 28, 2021, 9:23 PM

    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.

    P 1 Reply Last reply Sep 28, 2021, 9:32 PM Reply Quote 0
    • P
      PeterJones @Glen_Strom
      last edited by PeterJones Sep 28, 2021, 9:33 PM Sep 28, 2021, 9:32 PM

      @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
      • G
        Glen_Strom
        last edited by Sep 29, 2021, 2:40 AM

        @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
        2 out of 3
        • First post
          2/3
          Last post
        The Community of users of the Notepad++ text editor.
        Powered by NodeBB | Contributors