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

Re: [Clip] parse words and replace with SQL statement - never mind

Expand Messages
  • MrPhillip
    Whoops. After my consternation over why something that used to work no longer works, I shut down NoteTab. I fired it back up, and now the code executes as I
    Message 1 of 5 , Oct 9, 2009
    • 0 Attachment
      Whoops. After my consternation over why something that used to work no longer works, I shut down NoteTab. I fired it back up, and now the code executes as I expect it to do. It may have been something from my trying to work on UNIX files from a PC and over a network.

      The immediate issue is resolved, but I still am open to suggestions on a better method to accomplish my goals.

      Thanks for being there anyway. I don't post to the group much (or follow it like I used to). Usually, when I am preparing to ask y'all a question, I figure out the answer, so you never see the post.

      Mr. Phillip

      -----Original Message-----
      >From: MrPhillip <mr.phillip@...>
      >Sent: Oct 9, 2009 11:38 AM
      >To: ntb-clips@yahoogroups.com
      >Subject: [Clip] parse words and replace with SQL statement
      >
      >Greetings;
      >
      >I am trying to take two columns of data and turn it into SQL statments. There is probably a more elegant solution than what I figured out, but it seemed to work fairly well until today. It parses the words into variables, creates the SQl statement, but the ^!Keyboard BACKSPACE no longer blanks out the line in preparation of writing the new statement in it.
      >
      >I did save the file as UNIX format, would this affect the BACKSPACE command?
      >
      >I'd like to understand the backspace command no longer blanks out the line, but more importantly, I am beginning to wonder if my approach is completely retarded and I should ask for guidance. [I'm thinking maybe I should be creating a new output file instead of trashing my original data file, but I have not been able to figure that out. It's probably simple, but I can't find an example to cheat off of.]
      >
      >Mr. Phillip
      >
      >; Takes a list of API and CWN and creates entries for the Finder CWN overides table
      >; Input:
      >; 60804950270000 EB0227_126
      >; 6080734236X000 GB0378_3_LOC
      >; 60804950260000 EB0227_126
      >; Output:
      >;insert into wellmaster.GOM_STD_NAME_OVERRIDE(API, STD_NAME) values('60804950270000', 'EB0227_126');
      >;insert into wellmaster.GOM_STD_NAME_OVERRIDE(API, STD_NAME) values('6080734236X000', 'GB0378_3_LOC');
      >;insert into wellmaster.GOM_STD_NAME_OVERRIDE(API, STD_NAME) values('60804950260000', 'EB0227_126');
      >; Mr. Phillip Sand Hansel II 8/206/8
      >^!SetDebug On
      >^!Info Remember to use API12 vlaues
      >^!Info Hit OK to start working...and wait for it!
      >^!SetScreenUpdate 0
      >; jumps to top of document
      >^!Jump 1
      >^!Set %row%=^$GetRow$
      >; begins loop
      >:LOOP
      >^!Set %row%=^$GetRow$
      >^!SetCursor ^%row%:1
      >; selects first word
      >^!Select WORD
      >^!IfError STOPLOOP
      >; get selection and trim blanks
      >^!Set %api%=^$StrTrim("^$GetSelection$")$
      >; if blank bail
      >^!If ^%api% = " " STOPLOOP
      >; if reading SQL, bail
      >^!If ^%api% = "#insert" STOPLOOP
      >; get next word
      >^!Jump SELECT_END
      >^!Select EOL
      >^!Set %cwn%=^$StrTrim("^$GetSelection$")$
      >; if it gets nothing, then stop loop
      >^!IfError STOPLOOP
      >; put cursor back at line start
      >^!SetCursor ^%row%;1
      >; and select to end of line
      >^!Select EOL
      >; delete the line
      >^!Keyboard BACKSPACE
      >; type in this line with new variables substituted
      >^!Keyboard #update wellmaster.GOM_STD_NAME_OVERRIDE set STD_NAME='^%cwn%' where API='^%api%';#
      >; after insert, go to next line
      >^!Jump +1
      >^!IfError STOPLOOP
      >; go to top of loop
      >^!Goto LOOP
      >; exit loop at end of file
      >:STOPLOOP
      >^!Info Processing Complete
      >;adds commit statement
      >^!Keyboard #commit;#
      >; set cursor back of top of file
      >^!SetCursor ^%start_tag%:1
      >
      >
      >Mr. Phillip Sand Hansel II
      >http://philliphansel.org


      Mr. Phillip Sand Hansel II
      http://philliphansel.org
    • flo.gehrke
      ... Mr. Phillip, I m not familiar with the background of this job. But turning a string like... 60804950270000 EB0227_126 6080734236X000 GB0378_3_LOC
      Message 2 of 5 , Oct 9, 2009
      • 0 Attachment
        --- In ntb-clips@yahoogroups.com, MrPhillip <mr.phillip@...> wrote:
        >
        > The immediate issue is resolved, but I still am open to suggestions
        > on a better method to accomplish my goals.


        Mr. Phillip,

        I'm not familiar with the background of this job. But turning a string like...

        60804950270000 EB0227_126
        6080734236X000 GB0378_3_LOC
        60804950260000 EB0227_126

        into...

        insert into wellmaster.GOM_STD_NAME_OVERRIDE(API, STD_NAME) values('60804950270000', 'EB0227_126');
        insert into wellmaster.GOM_STD_NAME_OVERRIDE(API, STD_NAME) values('6080734236X000', 'GB0378_3_LOC');
        insert into wellmaster.GOM_STD_NAME_OVERRIDE(API, STD_NAME) values('60804950260000', 'EB0227_126');

        could be done with just (one long line)...

        ^!Replace "(^[^\x20]+)\x20(.+)$" >> "insert into wellmaster.GOM_STD_NAME_OVERRIDE(API, STD_NAME) values('$1', '$2');" AWRS

        Tested with NT Pro 6.12

        Regards,
        Flo
      • MrPhillip
        Thank you flo, I feel a little silly about my complicated procedure. However, I make a new clip and cut & paste in your elegant example, but it does not seem
        Message 3 of 5 , Oct 9, 2009
        • 0 Attachment
          Thank you flo, I feel a little silly about my complicated procedure.

          However, I make a new clip and cut & paste in your elegant example, but it does not seem to work for me. I click to run the clip, but nothing happens. Maybe my old version of NT4.82 is a problem?

          I read the docs to attempt to more fully understand what you are doing. I think you are saying "if the line starts with something that is not blank, take that and the next not blank thing (which become $1 and $2), then replace them with SQL statemenst wrapped around them.

          I tried to use parts of what you said in the Find tool as a regex. I told it to find
          (^[^\x20]+) that selects the first 3 characters of the first word (curious?).

          I've followed the group close enough to know there are issues with regex in older versions of NoteTab. Maybe it is time for me to send Eric some more money?

          Mr. Phillip


          -----Original Message-----
          >From: "flo.gehrke" <flo.gehrke@...>
          >Sent: Oct 9, 2009 3:02 PM
          >To: ntb-clips@yahoogroups.com
          >Subject: Re: [Clip] parse words and replace with SQL statement - never mind
          >
          >--- In ntb-clips@yahoogroups.com, MrPhillip <mr.phillip@...> wrote:
          >>
          >> The immediate issue is resolved, but I still am open to suggestions
          >> on a better method to accomplish my goals.
          >
          >
          >Mr. Phillip,
          >
          >I'm not familiar with the background of this job. But turning a string like...
          >
          >60804950270000 EB0227_126
          >6080734236X000 GB0378_3_LOC
          >60804950260000 EB0227_126
          >
          >into...
          >
          >insert into wellmaster.GOM_STD_NAME_OVERRIDE(API, STD_NAME) values('60804950270000', 'EB0227_126');
          >insert into wellmaster.GOM_STD_NAME_OVERRIDE(API, STD_NAME) values('6080734236X000', 'GB0378_3_LOC');
          >insert into wellmaster.GOM_STD_NAME_OVERRIDE(API, STD_NAME) values('60804950260000', 'EB0227_126');
          >
          >could be done with just (one long line)...
          >
          >^!Replace "(^[^\x20]+)\x20(.+)$" >> "insert into wellmaster.GOM_STD_NAME_OVERRIDE(API, STD_NAME) values('$1', '$2');" AWRS
          >
          >Tested with NT Pro 6.12
          >
          >Regards,
          >Flo
          >


          Mr. Phillip Sand Hansel II
          http://philliphansel.org
        • flo.gehrke
          ... Mr. Phillip, Sorry, I can t verify that. Probably, that s the reason why it doesn t work for you. There have been basic changes to the RegEx Machine and
          Message 4 of 5 , Oct 9, 2009
          • 0 Attachment
            --- In ntb-clips@yahoogroups.com, MrPhillip <mr.phillip@...> wrote:
            >
            > ...it does not seem to work for me. I click to run the clip, but
            > nothing happens. Maybe my old version of NT4.82 is a problem?

            Mr. Phillip,

            Sorry, I can't verify that. Probably, that's the reason why it doesn't work for you. There have been basic changes to the RegEx Machine and PCRE since NT 4.82.

            Your interpretation of the RegEx appears quite correct. In my words:

            (^[^\x20]+)\x20 matches all characters from the beginning of the line that are no space and plus the next following space. The first substring in parentheses goes into $1. All characters following the space till the end of line go into $2. The replacement string just outputs $1 and $2 combined with those SQL statements.

            > I tried to use parts of what you said in the Find tool as a
            > regex...(^[^\x20]+) that selects the first 3 characters of
            > the first word (curious?).

            Certainly another v4.82 issue.

            > Maybe it is time for me to send Eric some more money?

            I fully agree with you ;-)

            Regards,
            Flo
          • Alec Burgess
            flo.gehrke (flo.gehrke@t-online.de) wrote (in part) (on 2009-10-09 at ... Mr. Phillip: If you ve got a lot of clips and want to check out possible problems
            Message 5 of 5 , Oct 11, 2009
            • 0 Attachment
              flo.gehrke (flo.gehrke@...) wrote (in part) (on 2009-10-09 at
              18:40):
              > Your interpretation of the RegEx appears quite correct. In my words:
              >
              > (^[^\x20]+)\x20 matches all characters from the beginning of the line
              > that are no space and plus the next following space. The first
              > substring in parentheses goes into $1. All characters following the
              > space till the end of line go into $2. The replacement string just
              > outputs $1 and $2 combined with those SQL statements.
              >
              > > > I tried to use parts of what you said in the Find tool as a
              > > > regex...(^[^\x20]+) that selects the first 3 characters of
              > > > the first word (curious?).
              >
              > Certainly another v4.82 issue.

              Mr. Phillip:

              If you've got a lot of clips and want to check out possible problems
              with conversion to v5 (now v6) get the Lite version until you satisfy
              yourself you want to change to the Pro version :-) :-$

              If you want to try it with ver 4.x you might be able to get it to work
              by fixing (ie. back-porting) Flo's clip as follows:
              NTB 5 syntax (group expression) *was* {group expression}
              -- ie. curly brace instead of round brace
              NTB 5 syntax $1 $2 etc replacements was \1 \2 etc

              wrt. your original clip: (not tested)

              ; put cursor back at line start
              ^!SetCursor ^%row%;1
              ; and select to end of line
              ^!Select EOL
              ; delete the line
              ^!Keyboard BACKSPACE

              I basically try to avoid *all* use of ^!keyboard commands by the
              (usually) available ^!Menu commands eg. ^!Menu Edit/Delete might replace
              ^!Keyboard BACKSPACE as is. I'm not sure whether the cursor would be
              positioned exactly where you want it afterwards.

              If its "off by one" you might correct it by adding a ^!MoveCursor +/-nnn
              after the ^!Menu Edit/Delete.

              --
              Regards ... Alec (buralex@gmail & WinLiveMess - alec.m.burgess@skype)
            Your message has been successfully submitted and would be delivered to recipients shortly.