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

Re: [Clip] Can a clip re-order columns?

Expand Messages
  • 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 1 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 2 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 3 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 4 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.