Community

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

    Transpose multiple lines Horizontally in rows

    Help wanted · · · – – – · · ·
    2
    9
    331
    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.
    • Akruti Clinic
      Akruti Clinic last edited by

      Hi
      I have data where

      chinttt:
      6 PC COMBO BAGS
      Price - 740+$
      SHIPPING - 80 per pc
      STOCK - Available in 5 COLOURS
      NOTE:CONFORM COLOUR OF ACCESSORIES B4 BOOKING
      image-url:test.com/huhhu

      chinttt:

      • SPECIAL 6 PC COMBO *

      ◾️BOAT EARPHONE **
      ◾️
      ONE PLUS EARPHONE **
      *🤗😋🦾6 PC COMBO *

      • BEFORE PRICE 1150*
        *OFFER PRICE 1150
        *SHIPPING EXTRA *
      • FULL SALE COMBO TAKE ORDER FAST*
        image-url:test.com/huhhu

      chinttt:
      BACK AGAIN
      *QUALITY GUARANTEED *
      BRAND ::: MICHAEL KORS
      PRICE ::: 450+$
      SHPNG ::: 80 fix per combo if service available at given PINCODE.

      i would like to transpose this in two columns fields where data is mapped correctly as under chinttt like

      price                       details                      email

      740                      6 PC COMBO BAGS
                            SHIPPING - 80 per pc
                            STOCK - Available in 5 COLOURS
      1150              NOTE:CONFORM COLOUR OF ACCESSORIES B4 BOOKING
                            SPECIAL 6 PC COMBO *
                            ◾️BOAT EARPHONE **
                            ◾️ONE PLUS EARPHONE **
                            🤗😋🦾6 PC COMBO *
                            BEFORE PRICE 1150

                            *OFFER PRICE 1150
                            SHIPPING EXTRA *
                            FULL SALE COMBO TAKE ORDER FAST

      450…so on
      How can we acheive this

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

        @Akruti-Clinic said in Transpose multiple lines Horizontally in rows:

        i would like to transpose this in two columns fields where data is mapped correctly as under chinttt like

        My initial thought is that this will be difficult with a regular expression (regex). Regex expect some consistency in the data, regular data/formats and lines. All 3 examples provided have none of that.
        The first set has Price - 740+$, second BEFORE PRICE 1150* *OFFER PRICE 1150 and third PRICE ::: 450+$. So first and third are similar but not the same (different delimiter between word and value, and also the case of the word, Price vs. PRICE) and the second has 2 prices, so which one to pick? How many other variations to the data are there?

        About the only consistent item I can see is each set is started by the chinttt: At this point I would seriously consider not using regex as likely the result would be questionable.

        Terry

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

          @Terry-R said in Transpose multiple lines Horizontally in rows:

          Regex expect some consistency in the data, regular data/formats and lines. All 3 examples provided have none of that.

          I doubt this is a sane move, but here goes. I still stand by my original assessment that the data appears to be far too variable to consider running 1 (or more) regexes over it in the hope of reformatting it. But I love a challenge and actually in so far as the process itself, it isn’t too difficult. What will be the issue is obtaining the correct Price value from each set of data. The rest of the fields don’t matter and as there is a handy delimiter between sets (chinttt:) it makes it easier in some respects. At the end of the day if it doesn’t work I’ve wasted some time in putting together a possible solution and the requester may waste a small amount of time testing it. On the other hand if it works…???

          Assumptions:

          1. MOST IMPORTANT! Each set MUST contain at least 1 set of characters Price, the case of each letter does NOT matter (price, PRICE, pRiCe etc). The numerical value captured will be the first set of number(s) following. As an example if we have Price - per 2 items @ 740+$ the regex will pick 2 since that is the first numerical value following the string Price.
          2. In the situation where there are multiple Price in a set the first one will be used, EVERY TIME! As an example if we have
          BEFORE PRICE 75*
          *OFFER PRICE 1150
          

          Then the value picked will be 75 If however the 75 was missing then 1150 will be used even though it follows the second string Price as it is the FIRST numerical value following the first string Price.
          3. Blank Lines will be removed during this process, that is if any sets contain a blank line (even a line containing multiple spaces), it will be removed.

          I started with:

          chinttt:
          6 PC COMBO BAGS
          Price - 740+$
          SHIPPING - 80 per pc
          STOCK - Available in 5 COLOURS
          NOTE:CONFORM COLOUR OF ACCESSORIES B4 BOOKING
          image-url:test.com/huhhu
          
          chinttt:
          
          SPECIAL 6 PC COMBO *
          ◾️BOAT EARPHONE **
          ◾️ONE PLUS EARPHONE **
          *🤗😋🦾6 PC COMBO *
          
          BEFORE PRICE 1150*
          *OFFER PRICE 1150
          *SHIPPING EXTRA *
          FULL SALE COMBO TAKE ORDER FAST*
          image-url:test.com/huhhu
          chinttt:
          BACK AGAIN
          *QUALITY GUARANTEED *
          BRAND ::: MICHAEL KORS
          PRICE ::: 450+$
          SHPNG ::: 80 fix per combo if service available at given PINCODE.
          

          The steps:

          1. Replace the delimiter chinttt: with the Price numerical value followed by a single space. Find the first occurrence of Price and find the first numerical value immediately following, even if some other characters between. See assumption #1.
            Replace function
            Find What:^(chinttt:)(\R)(?s)(.*?price.*?((\d+)))(.*?)(?=\1|\z)
            Replace With:\4 \2\3\6

          2. Clear all blank/empty lines
            Line Operations, remove empty lines (containing blank characters)

          3. Indent all lines except the price line (1st line of set)
            Replace function
            Find What:^(?!\d+ \R)
            Replace With: 10 spaces

          4. Move the price down to the 1st line of data and keep indentation correct. Run repeatedly (replace all) until no more changes made
            Replace function
            Find What:(?-s)^(\d*?)(\d)(\h\R)(\d+)?(\h)
            Replace With:\1\3\2\4

          5. Clear all blank/empty lines
            Line Operations, remove empty lines (containing blank characters)

          and finished up with:

          740       6 PC COMBO BAGS
                    Price - 740+$
                    SHIPPING - 80 per pc
                    STOCK - Available in 5 COLOURS
                    NOTE:CONFORM COLOUR OF ACCESSORIES B4 BOOKING
                    image-url:test.com/huhhu
          1150      SPECIAL 6 PC COMBO *
                    ◾️BOAT EARPHONE **
                    ◾️ONE PLUS EARPHONE **
                    *🤗😋🦾6 PC COMBO *
                    BEFORE PRICE 1150*
                    *OFFER PRICE 1150
                    *SHIPPING EXTRA *
                    FULL SALE COMBO TAKE ORDER FAST*
                    image-url:test.com/huhhu
          450       BACK AGAIN
                    *QUALITY GUARANTEED *
                    BRAND ::: MICHAEL KORS
                    PRICE ::: 450+$
                    SHPNG ::: 80 fix per combo if service available at given PINCODE.
          

          Note that the second column above IS aligned, the character in front of BOAT and ONE may not be visible making it appear as if it is misaligned.

          I deliberately made the process a number of easy to understand steps. This was because trying to minimise the number of steps would have unnecessarily complicated understanding, and therefore the ability to alter to suit the data if this does not work. I do consider that possibility to be great.

          So you the requester might well undertake to use this and see if the data comes out OK, if so then I strongly suggest working on a copy of the data. If the process did NOT work as expected you may want to post further questions, or if it DID work we would like to know! Consider that I (or anyone) may or may NOT supply further help due to what seems to be highly variable data that may NOT react well to this type of editing.

          Terry

          1 Reply Last reply Reply Quote 1
          • Akruti Clinic
            Akruti Clinic last edited by

            Thanks Terry for clear explanation .With 200 entries under chintt didn’t worked .
            Instead can we move the whole line with string price like
            Price - 740+$              $              its data

            BEFORE PRICE 1150* $               its data
            *OFFER PRICE 1150

            PRICE ::: 450+$ $               $       its data

            Regards

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

              @Akruti-Clinic said in Transpose multiple lines Horizontally in rows:

              With 200 entries under chintt didn’t worked

              I suggested this would occur. You need to provide the examples in a better format, it is possible the data is being altered by the posting system. To show data without any issue, insert it into your post. Then select all the data and click on the </> button above the window. That will show it within the black box as you will see I have done in my previous post.

              Are you able to identify why it did not work, was it only some data sets that did not convert? Did you check that ALL sets contain the string price followed by a number?

              Terry

              1 Reply Last reply Reply Quote 1
              • Akruti Clinic
                Akruti Clinic last edited by

                @Terry-R said in Transpose multiple lines Horizontally in rows:

                chinttt: Price -                            price 740+$
                6 PC COMBO BAGS
                SHIPPING - 80 per pc
                STOCK - Available in 5 COLOURS
                NOTE:CONFORM COLOUR OF ACCESSORIES B4 BOOKING
                image-url:test.com/huhhu
                
                chinttt:
                
                SPECIAL 6 PC COMBO *
                ◾️BOAT EARPHONE **
                ◾️ONE PLUS EARPHONE **
                *🤗😋🦾6 PC COMBO *
                
                                                       BEFORE PRICE 1150*
                                                        *OFFER PRICE 1150
                *SHIPPING EXTRA *
                FULL SALE COMBO TAKE ORDER FAST*
                image-url:test.com/huhhu
                chinttt:
                BACK AGAIN
                *QUALITY GUARANTEED *
                BRAND ::: MICHAEL KORS
                                                         PRICE ::: 450+$
                SHPNG ::: 80 fix per combo if service available at given PINCODE
                

                so if we can transpose complete line containing text price.
                To be precise i am trying notepad++ to filter data from whatsapp group export chat with media which is highly unorganized with one common word price attached to every chat date wise and mobile number.

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

                  @Akruti-Clinic said in Transpose multiple lines Horizontally in rows:

                  to filter data from whatsapp group export chat with media

                  Those examples look very different now. And it makes the reason why my solution failed very clear. If you are able to understand regex at all (I did try and make it fairly simple) I was looking for chinttt: on a line all by itself with a carriage return/line feed immediately behind. In your first example the price is on the same line so it doesn’t work. So that was another assumption I had made.

                  To make it work it is fairly simple to sort out. We just need to have chinttt on a separate line. So the first step, call it:
                  0.5 Replace function
                  Find What:^(chinttt:)
                  Replace With:\1\r\n

                  The rest of the steps will follow as already stated.
                  If chinttt already is on a line by itself it doesn’t matter as eventually we remove all blank lines.

                  One question though, why did you alter the first example in your first post so that 6 PC COMBO BAGS was before the price line where in the latest version it clearly isn’t? When we try to help we aren’t impressed in fabricated examples. As you can see now, it clearly confuses the issue.

                  So a note for any future requests, provide clear unaltered examples.

                  Terry.

                  1 Reply Last reply Reply Quote 1
                  • Akruti Clinic
                    Akruti Clinic last edited by

                    Thanks for Explaining well.
                    You question
                    One question though, why did you alter the first example in your first post so that 6 PC COMBO BAGS was before the price line where in the latest version it clearly isn’t?
                    No data changed i want to have two columns with complete line with string price to right so that shift+alt+arrow can be used to pull price separate;y to be copied to excel like column a with data and b with price…i thinks its going more comple will find another,

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

                      @Akruti-Clinic said in Transpose multiple lines Horizontally in rows:

                      No data changed

                      I am referring to this
                      3027cd08-02f8-4d28-bf77-1c33c8fcc4a0-image.png

                      The order of the lines changed and some text was missing, namely Price - which showed on the first line immediately behind chinttt.

                      I am thinking that English is not your primary language as the last sentence you wrote i thinks its going more comple will find another, I do not understand. If you are still having issues, explain the problem. As you supplied some “real data” which allowed me to fix the previous problem, maybe you can show further examples where the process did not work and maybe it is still possible to update the solution to accommodate any other need.

                      Terry

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