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

reg exp & clip solution

Expand Messages
  • BHCS
    I need some help with the following problem: I get several csv files from employees/contractors periodically in this form (exported from a Palm app)...
    Message 1 of 5 , Oct 31, 2003
    • 0 Attachment
      I need some help with the following problem: I get several csv files
      from employees/contractors periodically in this form (exported from a
      Palm app)...

      "Consult","Talk to Pam re JM >
      1hr","IMCO","Office","07/11/2003","00859.9","00861.6","1.7"
      "Consult","Missed Foster/A&L re software > n/c
      ","CCACPC","IMCO","07/11/2003","00861.6","00876.5","14.9"
      "Trblshoot/repair","Took Caroles sys to shop for repairs/upgrades >
      .75hr","Design Res.","CCACPC","07/11/2003","00876.5","00878.9","2.4"
      "Consult","re Win vs Linux server opts > 2hrs","Mather Agency","Design
      Res.","07/11/2003","00878.9","00879.3",".4"
      "Cinco fillup.","$14.85/9.1g/$1.639/ck","Sunset ARCO","Mather
      Agency","07/11/2003","00879.3","00889.2","9.9"

      Currently I import them into a dbms (Alpha5v5), massage them, generate
      reports, post the reports to a new table & then export to use in an
      accounting/billing prog. This has too many steps. All I really need to
      do is parse them & select each record (line) that meets the criteria &
      write/move the selected to a new file. I think this is a combination of
      reg exp & clip language.

      Does anyone know of a clip already in existence that does something
      similar that I could modify for my use? Specifically, here's what I need
      to do:
      1. Each csv file covers a month of client visits & usually has about
      110+ lines (records)
      2. The 3rd field is the important field that indicates what client was
      visited
      3. I want to search for all lines where the 3rd field = a specific
      value (eg, IMCO in the first line of the abv example)
      3b. Note that the reg exp/clip must only select a line if the value is
      in the 3rd field, making sure to NOT to select if the value is in the
      4th field;
      4. Select all the lines that meet that criteria / cut them /open a new
      file / paste the cut records into this new file
      5. Open a 'Save As' dialog with a suggested filename based on the
      search criteria (eg, IMCO.txt) with the Save To folder the one I put
      these records in (eg, R:\Clients\BillableHrs\)
      6. I don't, however, want it to auto save it as I want to edit the
      file name to include billing period (eg, IMCO_Jan04.txt) before I save it.

      Any help would be appreciated.

      Thanks,

      Bill
    • benb_i26up@xemaps.com
      On Friday, Oct 31, 2003 BHCS wrote regarding reg exp & clip solution ; ... Hi Bill, Something like this may do what you want; H= Extract client data ^!Set
      Message 2 of 5 , Nov 1, 2003
      • 0 Attachment
        On Friday, Oct 31, 2003
        BHCS wrote regarding "reg exp & clip solution";

        > I need some help with the following problem: I get several csv files
        > from employees/contractors periodically in this form (exported from a
        > Palm app)...
        >
        > "Consult","Talk to Pam re JM > 1hr","IMCO","Office","07/11/2003","00859.9","00861.6","1.7"
        > "Consult","Missed Foster/A&L re software > n/c ","CCACPC","IMCO","07/11/2003","00861.6","00876.5","14.9"
        > "Trblshoot/repair","Took Caroles sys to shop for repairs/upgrades > 75hr","Design Res.","CCACPC","07/11/2003","00876.5","00878.9","2.4"
        > "Consult","re Win vs Linux server opts > 2hrs","Mather Agency","Design Res.","07/11/2003","00878.9","00879.3",".4"
        > "Cinco fillup.","$14.85/9.1g/$1.639/ck","Sunset ARCO","Mather Agency","07/11/2003","00879.3","00889.2","9.9"

        Hi Bill,

        Something like this may do what you want;

        H="Extract client data"
        ^!Set %client%=^?[Enter client name]
        ^!Set %tempfile%=^$GetTempFile$
        ^!Jump Doc_Start
        :repeat
        ^!Find "".*",".*","^%client%",".*",".*",".*",".*",".*"" RS
        ^!IfError finish
        ^!AppendToFile "^%tempfile%" ^$GetSelection$^%NL%
        ^!GoTo repeat
        :finish
        ^!Open ^%tempfile%
        ^!SetWizardLabel "Save to R:\Clients\BillableHrs\"
        ^!RenameDoc ^?{As:=^%client%.txt}
        ^!Save As "R:\Clients\BillableHrs\^$GetFileName(^**)$"

        HTH
        Ben B
      • Alan C.
        ... [ . . Bill s VERY nice clip . . ] (bandwidth, please reference Bill s reply in order to see that clip) The fun is in the tinkering for me. I have fun
        Message 3 of 5 , Nov 1, 2003
        • 0 Attachment
          On Sat, 01 Nov 2003 14:11:17 -0800, benb_i26up@... wrote:
          >�On Friday, Oct 31, 2003
          >�BHCS wrote regarding "reg exp &�clip solution";
          >
          >>�I need some help with the following problem: I get several csv files
          >>�from employees/contractors periodically in this form (exported from a
          >>�Palm app)...
          >>
          >>�"Consult","Talk to Pam re JM >�1hr","IMCO","Office","07/11/2003","00859.9","00861.6","1.7"
          >>�"Consult","Missed Foster/A&L re software >�n/c ","CCACPC","IMCO","07/11/2003","00861.6","00876.5","14.9"
          >>�"Trblshoot/repair","Took Caroles sys to shop for repairs/upgrades >�75hr","Design Res.","CCACPC","07/11/2003","00876.5","00878.9","2.4"
          >>�"Consult","re Win vs Linux server opts >�2hrs","Mather Agency","Design Res.","07/11/2003","00878.9","00879.3",".4"
          >>�"Cinco fillup.","$14.85/9.1g/$1.639/ck","Sunset ARCO","Mather Agency","07/11/2003","00879.3","00889.2","9.9"

          >
          [ . . Bill's VERY nice clip . . ] (bandwidth, please reference Bill's reply in order to see that clip)

          The fun is in the tinkering for me. I have fun with the possibilities.

          Mine modified clip does creates one file, a scratch doc, then renames it. Also incorporates a check prior to overwrite in the case that the filename already previously exist.

          I don't mean that my modifications make it any better (what's better likely depends on what a user's unique needs happen to be). My modify just makes it different; that's all.

          If the file name doesn't exist on disk, AppendToFile will create a new file of such name. That's the premise that I utilize; I use a methodology so as to insure that the scratch doc filename (fictitious.not) does not exist, at least it doesn't exist either just prior to or until AppendToFile is used in the clip (you can, however, choose any name that you want for the scratch doc.)

          H="Extract client data"
          ^!Set %scratchdoc%=fictitious.not
          ^!Set %path%="C:\files\docs\"
          ^!Set %client%=^?[Enter client name]
          ^!IfFileExist "^%path%^%scratchdoc%" skip
          ^!GoTo skip
          ^!DeleteFile "^%path%^%scratchdoc%"
          ^!Jump Doc_Start
          :repeat
          ^!Find "".*",".*","^%client%",".*",".*",".*",".*",".*"" RS
          ^!IfError finish
          ^!AppendToFile "^%path%^%scratchdoc%" ^$GetSelection$^%NL%
          ^!GoTo repeat
          :finish
          ^!Open "^%path%^%scratchdoc%"
          ^!SetWizardLabel "Save to ^%path%"
          ^!Set %new_name%=^?{As:=^%client%.txt}
          ^!IfFileExist "^%path%^%new_name%" warn
          ^!RenameDoc ^%new_name%
          ^!GoTo end
          :warn
          ^!Skip A file named ^%path%^%new_name% already exists. Overwrite?
          ^!GoTo end
          ^!DeleteFile "^%path%^%new_name%"
          ^!RenameDoc ^%new_name%
          ; -----<end of clip<-----

          --
          Alan.
        • BHCS
          Thanks to both of you (Ben B & Alan C). Both clips work great, but now I have new questions: - can you make these clips CUT (as in Ctrl+X) the specified client
          Message 4 of 5 , Nov 2, 2003
          • 0 Attachment
            Thanks to both of you (Ben B & Alan C). Both clips work great, but now I
            have new questions:
            - can you make these clips CUT (as in Ctrl+X) the specified client
            that it selects & [currently] copies to the new file? With 100+ lines it
            would be easier to keep track of what clients were done if they got
            removed/cut as one worked their way thru the list of clients seen for
            the month.
            - how to you make the "^!Set %client%=^?[Enter client name]" entry
            case insensitive?

            I've been using NoteTab for 3+ years now, incl. the use of a number of
            the clips (but this is the first time to want a custom clip), & I keep
            getting pleasantly surprised by its power & the knowledge & generosity
            of the user community. Thanks again!

            Bill
          • Alan C.
            ... Glad is handy for you. There s also GetFieldPos or similar (returns a data from a specified field of a delimited line) which could use to take the place
            Message 5 of 5 , Nov 2, 2003
            • 0 Attachment
              On Sun, 02 Nov 2003 01:12:19 -0800, BHCS wrote:
              >�Thanks to both of you (Ben B &�Alan C). Both clips work great

              Glad is handy for you. There's also

              GetFieldPos

              or similar (returns a data from a specified field of a delimited line) which could use to take the place of that regex find command. but then you'd need check if it matched then if so, snag that line

              ^!IfMatch

              Just keep using what you already have (it works good, right?). I just mentioned the above; don't bother to get confused by it.

              >�have new questions:
              >� �- can you make these clips CUT (as in Ctrl+X) the specified client
              >�that it selects &�[currently] copies to the new file?

              please look carefully (compare) at the next two (snippets) (how to add one new line)

              ; 1
              ^!IfError finish
              ^!AppendToFile "^%path%^%scratchdoc%" ^$GetSelection$^%NL%
              ^!GoTo repeat
              :finish

              ; 2
              ^!IfError finish
              ^!AppendToFile "^%path%^%scratchdoc%" ^$GetSelection$^%NL%
              ; add new line the next line deletes the client from source doc
              ^!Replace "^$GetSelection$" >> "^%EMPTY%" IS
              ^!GoTo repeat
              :finish

              ; ----<end of those two----<----------------------------------

              >� �- how to you make the "^!Set %client%=^?[Enter client name]" entry
              >�case insensitive?

              help > help on clip programming > click on the index tab

              then scroll down to: ^!Find (for that matter, you can also scroll to any other of the commands, functions, or pre defined variables, some of which I've referenced elsewhere within this reply)

              where you can peruse Find's "options" option I is what do it. thus:

              ^!Find "".*",".*","^%client%",".*",".*",".*",".*",".*"" RS

              alter it, make it become with 3 options instead of its former 2 - thus (notice I at end)

              ^!Find "".*",".*","^%client%",".*",".*",".*",".*",".*"" RSI

              ; -----end-----

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