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

parse words and replace with SQL statement

Expand Messages
  • MrPhillip
    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
    Message 1 of 1 , Oct 9, 2009
    • 0 Attachment
      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
    Your message has been successfully submitted and would be delivered to recipients shortly.