13 Replies Latest reply: May 15, 2014 9:56 AM by Cindy Matosky RSS

    Floating trap with multiple occurance on single line

    Cindy Matosky

      I am having trouble working with a floating trap that can occur multiple times on the same line.  I need to capture the codes after the occurance of "NUMBER" in the sample text below.  Any suggestions?

       

       

      OUTSTANDING FIELD SERVICE ACTIONS NUMBER 04S12 STOP LAMP SWITCH NUMBER 04M04 CAUTION: MAY NOT NEED REPAIR; SERVICE ONLY IF CONCERN IS PRESENT NUMBER 04S17 INSTALLATION OF FRONT COIL SPRING PROTECTIVE SHIELDS FOR VEHICLES OPERATED IN CORROSION STATES

        • Floating trap with multiple occurance on single line
          joey

          Can we get a  report with the tags for CODE arround it to preserve formatting?

           

          My first approach would be to determine the maximum number of detail records you can have in a row.  Then, use the LSPLIT function, with NUMBER as the separator.  The first entry would be the second part, the second entry the third part and so on. This would give you all of the records, but the layout would be very bad in the table.  To normalize the data (one record per row), you'd need to go through some more steps.

          • Floating trap with multiple occurance on single line
            Grant Perkins

            Originally posted by Joey:

            Can we get a  report with the tags for CODE arround it to preserve formatting?

             

            /b[/quote]Joey. I just looked at that via a 'reply with quote' edit. Not such a good idea in this case since as Cindy says everything is on one long line. Makes it more difficult to read on screen - one of the exceptions to the rule!

             

             

            Grant

            • Floating trap with multiple occurance on single line
              Grant Perkins

              Cindy,

               

              I'm pretty much with Joey here. Just get the lines you want and then work out the best way to slice and dice them based on "Number", as Joey suggests, or by calculating the position of the sections of the line you need using 'Number' but something other than SPLIT functions. The point is there are several ways of doing much the same thing here. However is you don't know how many Numbers there may be (or rather COULD be maximum) it could get a bit messy.

               

              Another approach would be to prepare the file beforehand and, in effect, split the line to a new line every time the word NUMBER appears. I'm not sure if this would give you everything you need but it can be done using the Monarch Utility (for example).

               

               

              You would get a report that looked like this;

              [font="courier"]OUTSTANDING FIELD SERVICE ACTIONS

              NUMBER 04S12 STOP LAMP SWITCH

              NUMBER 04M04 CAUTION: MAY NOT NEED REPAIR; SERVICE ONLY IF CONCERN IS PRESENT

              NUMBER 04S17 INSTALLATION OF FRONT COIL SPRING PROTECTIVE SHIELDS FOR VEHICLES OPERATED IN CORROSION STATES /font[/quote]Which might make life a bit easier.

               

              Would that be helpful?

               

               

              Grant

              • Floating trap with multiple occurance on single line
                Nigel Winton

                Hi there

                Just to let you know I have sent Cindy a model file that will do the job if she cannot get Grants changes to the file done. It was one of those slicy/dicy ones that was easier to send than recreate here.

                 

                 

                Regards

                 

                Nigel

                • Floating trap with multiple occurance on single line
                  Grant Perkins

                  Nigel - sounds good.

                   

                  Cindy - if you want to play with the Utility and need some specific instructions let me know.

                   

                  You'll want the last option on the first screen of the Utility and then the 'Replace strings' option. The change required is to replace "NUMBER" with "/013/010NUMBER".

                   

                  That inserts a CR/LF before every occurrence of the string "NUMBER". Hopefully that will do the job - as long as there are no other uses of the word number in the string which do not come before a number you need! (If you see what I mean ...)

                   

                  HTH

                   

                   

                  Grant

                  • Floating trap with multiple occurance on single line
                    Cindy Matosky

                    Grant,

                     

                    I have never worked with the Monarch Utility.  I tried to run it and it brings up a Utility wizard that I could not understand how to pick an option to modify a file.  Please help.

                     

                    Thanks.

                    • Floating trap with multiple occurance on single line
                      Grant Perkins

                      Hi Cindy,

                       

                      As you have opened up the possibility to describe the use of part of the Utility in some detail I will write it up in novice user mode for the benefit (hopefully) of others in the future. However I am guessing that parts of this may be well below your existing knowledge level as a programmer so I apologise for that in advance!

                       

                       

                      Grant

                       

                       

                      Monarch Utility

                       

                      NB - if following the instructyions below when advised to enter something in a box or field the string of characters to be entered are enclosed in quote marks. DO NOT ENTER THE QUOTE MARKS - just the string of characters.   :cool: 

                       

                       

                      Very simple once you have seen and tried it. Refer to the context sensitive help as you go as well - very useful.

                       

                      To make changes to 'Prepare' a file to make it usable or just easier to work with select the

                       

                      "Prepare files for Monarch (Replace, Insert Line breaks, EDI)"

                       

                      option by clicking the radio button.

                       

                      Then click on the "Next" button at bottom right of the screen.

                       

                      The next action is to identify the source file you want to modify. (I suggest you practice on a copy of it rather then the original!)  You can use standard windows browser features to identify the file (or files) by clicking on the 'folder' icon on the right of the 'Folder' line.

                       

                      The second part of that window allows you to tell the Utility where to save the result of the process. The default is to overwrite the source file but is is MUCH BETTER just to give it a new name or add something to the original name before saving it.

                       

                      Click the "NEXT" button to continue or the"BACK" button to go back one step.

                       

                      We can now do a number of different things to the selected file but it is quite rare to want to do more than one thing at a time. Indeed the Utility will not allow you to!

                       

                      What we want to do here is change the format of the file to make modelling easier and to do that we can REPLACE certain blocks of characters ("Strings") with something slightly different. So we will use the first option on the "Type"(of change) screen. -

                       

                      "Replace all instances of a string with another string or an empty string."

                       

                       

                      What I suggest we replace (to get the layout I posted and based on you sample line) is the word "NUMBER" with another string of characters that will ensure a new line starts each time the word NUMBER appears.

                       

                      In an electronic print file, the sort that Monarch deals with much of the time, such formatting is provided by invisible characters for 'Carriage Return' (Cr) and 'Line Feed' (Lf).

                       

                      So if we change "NUMBER"  to "CrLfNUMBER" we should get the effect required.

                       

                      "Cr" is character number 010 in the ASCii character set.

                      "Lf" is character number 013 in the ASCii character set.

                       

                      To indicate we are using the numbers as ASCii characters we add a "/" in front of them.

                       

                      So in the box provided for the (String to )'Replace' entry enter "NUMBER".

                       

                      In the box below for 'With' enter "/013/010NUMBER"

                       

                      Click next. Watch what appears. Hopefully the final 'report' of the process will indicate "Success".

                      Then look at your output file.

                       

                      That should be it.

                       

                      How does this read? Is it easy enough to follow?

                       

                      How did you get on?

                       

                       

                      If anyone is interested in seeing the full table of ASCii characters a file (ASCii.txt) can be downloaded from the Monarch Support pages on the Datawatch Web site.

                       

                      (For users of earlier versions of Monarch this part of the Utility is pretty much what the MSRP.EXE utility was provided for. If you still use MSRP it should be fine to continue.)

                      • Floating trap with multiple occurance on single line
                        Cindy Matosky

                        Grant,

                         

                        Since I only had Monarch Pro V8.00, I did not have this last option in the Utility program to modify the files.  I did download the upgrade to V8 and was able to finally find this option to modify the files.

                         

                        My file is in html format.  I did all that you explained at it did not make any changes to the file.  I used the CrLfNUMBER and the text came out with CrLfNUMBER.  I then used the / and ascii codes and nothing appeared to happened when I reopened the file in monarch???

                        • Floating trap with multiple occurance on single line
                          Cindy Matosky

                          Nigel gave me a model that basically found the first occurrence of "NUMBER" in the text, pulled off the number I needed, and then parsed the rest into a new calculated field. 

                           

                          The model then found the next occurrence of "NUMBER" in the new text field, pulled off the number I needed, and then parsed the rest of into another new calculated field.

                           

                          I did this 10 times since I had once occurrence of a record that had 8 numbers in the text.  My only problem is that if there are more than 10 occurrences of "NUMBER", it will not pick up the rest.  It worked this time, but I am concerned about future reports.  (Thank you VERY MUCH Nigel for your help!)

                           

                          Therefore, I was attempting to use the Monarch Utility that Grant has suggested and am having difficulty since I have not used the Utility program before.

                          • Floating trap with multiple occurance on single line
                            Grant Perkins

                            Originally posted by Cindy Matosky:

                            Grant,

                             

                            Since I only had Monarch Pro V8.00, I did not have this last option in the Utility program to modify the files.  I did download the upgrade to V8 and was able to finally find this option to modify the files.[/b][/quote]Ah!

                             

                            OK. I don't have a version installed which is pre 8.02 so could not easily have checked the functionality for the earlier Utility anyway. At least that explains why you would have been puzzled! The MSRP.EXE program was provided back then to do the equivalent job.

                             

                             

                            Originally posted by Cindy Matosky:

                            My file is in html format.  I did all that you explained at it did not make any changes to the file.  I used the CrLfNUMBER and the text came out with CrLfNUMBER.  I then used the / and ascii codes and nothing appeared to happened when I reopened the file in monarch??? /b[/quote]Ah! again.

                             

                            If I remember correctly the changes will not be apparent if Monarch is interpreting an HTML file. Sorry, I didn't know we were dealing with html. That usually presents a whole set of different challenges anyway and often defeats the use of any preparative approaches in my experience. (Not very extended experience I would add, others may well have a better feel for the solution with html.)

                             

                            I guess you are back to Nigel's model at the moment? The idea of extracting as a row/block of data and then slicing and dicing is likely to bbe the best way to go I suspect.

                             

                            HTH.

                             

                             

                            Grant

                            • Floating trap with multiple occurance on single line
                              joey

                              Perhaps you could use the prep utility to insert a <br> (HTML line break) before every occurance of NUMBER.  Or you could search and replace "NUMBER" with "<BR>NUMBER", using either the monarch search and replace utility, or even opening up the html document in notepad.

                              • Floating trap with multiple occurance on single line
                                Cindy Matosky

                                Joey & Grant,

                                 

                                THANKS SO MUCH!!!  [img]smile.gif[/img]   The <BR> worked perfectly!  Very exciting!!! :cool:

                                 

                                Sorry I did not realize that html would have made a difference.  I am new to working with this type of file.

                                 

                                Appreciate all your help.

                                 

                                One other question, is there a way to set up the utility program to work in batch mode so that I do not have to create it with the wizard with each file?

                                 

                                Thanks again.

                                 

                                -Cindy

                                • Floating trap with multiple occurance on single line
                                  Cindy Matosky

                                  Upon further research, I found the answer to my own question.

                                   

                                  From a previous posting, I was redirected the the Monarch Report for July 06 and found the following:

                                   

                                  The layout of the Monarch Utility command line for string replacement is:

                                  monarchu.exe <source file> <target file> /cr <search string> <replace string>

                                   

                                  MonarchU.exe refers to the Monarch Utility executable. /cr means "run string replacement." For more details, please see the help file within Monarch Utility for working with command lines.

                                   

                                   

                                  Thanks again for your help and have a great day!