Loading ...
Sorry, an error occurred while loading the content.

Can a clip re-order columns?

Expand Messages
  • adahma
    I m running 4.92 Pro, been a registered user of Pro for years, but have never had cause or time to dig into clip stuff. Now I have a need that I think a clip
    Message 1 of 6 , Mar 4, 2003
    • 0 Attachment
      I'm running 4.92 Pro, been a registered user of Pro for years, but
      have never had cause or time to dig into clip stuff. Now I have a
      need that I think a clip would work for, but I'm not real sure....

      I have a file that was sent to me that I need to load into a database,
      and is expected in a very specific format. Example data:

      Current format: 12345 ABCDEFG 345 0 XXXX IUYP
      Needed format : 12345 ABCDEFG 3450 XXXX IUYP

      I would need the 345 moved over one column so that it's flush against
      the zero that follows. I would want to apply that to all rows in the
      file. Possible with a clip?

      Thanks in advance!
      jdk
    • Sheri
      ... It would be possible to manipulate the space without a clip, using Modify|Block commands, but the following clip will also do it. The following is a
      Message 2 of 6 , Mar 5, 2003
      • 0 Attachment
        At 05:03 AM 3/5/03 +0000, adahma wrote:
        >Current format: 12345 ABCDEFG 345 0 XXXX IUYP
        >Needed format : 12345 ABCDEFG 3450 XXXX IUYP

        It would be possible to manipulate the space without a clip, using Modify|Block commands, but the following clip will also do it.

        The following is a regular expression which will insert a space after the 15th character and delete a space after the 22nd character. You may need to adjust it because the number of characters on the row is not clear from your sample (i.e., possibly you have spaces at the start of each row).

        Regards,
        Sheri

        H="CloseSpace"
        ^!Jump Doc_Start
        ^!Replace "^{^$StrFill(".";15)$}{^$StrFill(".";7)$}\s" >> "\1\s\2" RAIS
        ;end of clip
      • Michael Gerholdt
        get ahold of Wayne s NoteAwk; you ll need the gnu executable also, can put it in notetab pro directory. It will actually reorder columns based on input you
        Message 3 of 6 , Mar 5, 2003
        • 0 Attachment
          get ahold of Wayne's NoteAwk; you'll need the gnu executable also, can put
          it in notetab pro directory.

          It will actually reorder columns based on input you provide

          But it looks like you are not actually wanting to reorder, but rather
          concatenate two of the columns, right?

          I'd suggest looking at regular expressions combined with replace. Might be
          slightly daunting place to begin but very rewarding.

          On the other hand, you don't really need to mess with the source file to do
          this. Concatenate the fields in your SQL statement instead.

          If you are pulling the data into an array, for example, a zero-based array

          myArray(0) would hold 12345
          myArray(1) would hold ABCDEFG
          myArray(2) would hold 345
          myArray(3) would hold 0
          myArray(4) would hold IUYP

          So:

          insert into myTable (column1,column2,column3,column4)
          values
          (myArray(0),myArray(1),myArray(2)||myArray(3),myArray(4))

          the || is concatenate operator for Oracle ... not sure what database you are
          using or how you are grabbing the data; if not an array, still the concept
          is the same, just concatenate in the sql statement.

          You may want to do a trim on each of the values when grabbing to avoid any
          unwanted spaces at beginning/end.

          hth

          Regards,
          Michael Gerholdt
          SUNY College at Fredonia

          > I have a file that was sent to me that I need to load into a database,
          > and is expected in a very specific format. Example data:
          >
          > Current format: 12345 ABCDEFG 345 0 XXXX IUYP
          > Needed format : 12345 ABCDEFG 3450 XXXX IUYP
        • Alec Burgess
          Assuming that col 18 is always blank so that you want to remove the space at col 25 and insert it back at col 18 here is a one-liner that should do the trick.
          Message 4 of 6 , Mar 5, 2003
          • 0 Attachment
            Assuming that col 18 is always blank so that you want to remove the space at
            col 25 and insert it back at col 18 here is a one-liner that should do the
            trick.

            H="Fix db input"
            ^!replace "^{.................}{.......}." >> "\1\s\2" rwsa

            Note this can be done without using a clip (those are the ones I like best
            ;-) )
            by checking regular expression in the Replace dialog and setting:
            Find="^{.................}{.......}." and
            Replace="\1\s\2" (both without the quotes)

            the ^ says match for start-of-line
            the first string of dots matches col 1-18, the next string matches the
            number up to but not including the following space. Then replace the whole
            string up to col 25 by first string, a space and then second string.

            Regexp - ya gotta love em.

            Regards ... Alec
            --

            ---- Original Message ----
            From: "adahma" <jdkitch@...>
            To: <ntb-clips@yahoogroups.com>
            Sent: Wednesday, March 05, 2003 00:03
            Subject: [Clip] Can a clip re-order columns?

            > I'm running 4.92 Pro, been a registered user of Pro for years, but
            > have never had cause or time to dig into clip stuff. Now I have a
            > need that I think a clip would work for, but I'm not real sure....
            >
            > I have a file that was sent to me that I need to load into a database,
            > and is expected in a very specific format. Example data:
            >
            > Current format: 12345 ABCDEFG 345 0 XXXX IUYP
            > Needed format : 12345 ABCDEFG 3450 XXXX IUYP
            >
            > I would need the 345 moved over one column so that it's flush against
            > the zero that follows. I would want to apply that to all rows in the
            > file. Possible with a clip?
            >
            > Thanks in advance!
            > jdk
            >
            >
            >
            > ------------------------ Yahoo! Groups Sponsor
            > ---------------------~--> Get 128 Bit SSL Encryption!
            > http://us.click.yahoo.com/LIgTpC/vN2EAA/xGHJAA/dkFolB/TM
            > ---------------------------------------------------------------------~->
            >
            >
            >
            > Your use of Yahoo! Groups is subject to
            > http://docs.yahoo.com/info/terms/
          • JDK
            ... Thanks to everyone for all the suggestions. I actually ended up going with the reg exp on a search and replace. I had actually been trying to do that
            Message 5 of 6 , Mar 5, 2003
            • 0 Attachment
              On Wed, Mar 05, 2003 at 08:42:51AM -0500, Alec Burgess wrote:
              > Assuming that col 18 is always blank so that you want to remove the space at
              > col 25 and insert it back at col 18 here is a one-liner that should do the
              > trick.
              >
              > H="Fix db input"
              > ^!replace "^{.................}{.......}." >> "\1\s\2" rwsa
              >
              > Note this can be done without using a clip (those are the ones I like best
              > ;-) )
              > by checking regular expression in the Replace dialog and setting:
              > Find="^{.................}{.......}." and
              > Replace="\1\s\2" (both without the quotes)
              >
              > the ^ says match for start-of-line
              > the first string of dots matches col 1-18, the next string matches the
              > number up to but not including the following space. Then replace the whole
              > string up to col 25 by first string, a space and then second string.

              Thanks to everyone for all the suggestions. I actually ended up
              going with the reg exp on a search and replace. I had actually been
              trying to do that yesterday, and could not make it work, and it
              wasn't until I was looking at it again thanks to Alec, and realized
              that there was a check box to enable the reg exp stuff. Once I
              checked that it worked much better. :P

              Thanks!
              jdk
            • Ville Saalo
              ... the zero that follows. I would want to apply that to all rows in the file. Possible with a clip? You could ask if there s something that can not be done
              Message 6 of 6 , Mar 5, 2003
              • 0 Attachment
                > I would need the 345 moved over one column so that it's flush against
                the zero that follows. I would want to apply that to all rows in the
                file. Possible with a clip?




                You could ask if there's something that can not be done with clips. :) I
                see you already got your problem solved but in case you ever need to do
                that again with html-tables, we made a library for that a while back.


                See
                http://groups.yahoo.com/group/ntb-clips/messagesearch?query=sort%20html%
                20table , message #9111 by me is the first that counts. After that there
                are 6 replies with improvements.


                -Ville
              Your message has been successfully submitted and would be delivered to recipients shortly.