5 Replies Latest reply: May 15, 2014 9:54 AM by Mike Urbonas RSS

    automatic trapping (discussion of Monarch V6 Pro with Delimited Files)

    Arne _

      Hi,

      I have to process many lists from SAP, which have a standard layout columns being separated by "|":

      xxxx

      xxx

      xxxx

       

      In modell definition there is the automatic trapping feature, but this seems to check for SPACE to define traps.

       

      Once I defined my traps in table view I can adapt each column to "start at preceeding |", but then I have done all the trapping already manually.

       

      Is there a way to tell Monarch V6.01 PRO to use other characters in trapping???

       

      wkr,

      Arne

       

      [size="1"][ March 07, 2003, 11:03 AM: Message edited by: Mike Urbonas ][/size]

        • automatic trapping (discussion of Monarch V6 Pro with Delimited Files)
          Grant Perkins

          Arne,

           

          Just to be sure I understand your requirement ...

           

          It seems that you have a data output file that has one row of data for each record and separates the different fields in the data row by use the | character (vertical bar). I would guess that the files are, in effect, text files and so you are using these files as REPORTS to read in to Monarch. Am I right so far?

           

          OK. Once you have defined a model then you might be able to re-use if for another v ersion of the same report. However I suspect you are being asked to analyse modified or new version of the 'reports' very often and so old models may not be useful.

           

          I think you want Monarch to automatically identify the data fields (standard facility with some limitations on how it can work) so that it uses the | as the field delimiter for that function.

           

          However, if you can think of the original file from the SAP extract as a DATABASE you can use "Open Database", select the file and then use the "Delimited Text Import" function to specify which character separates the columns. The defaults are not what you want so click the 'Other' option and simply enter the character you want to use. Monarch will then present the fields on screen for you to choose which ones you want to import and some other facilities.

           

          You can save the model, with calculated fields and other features, and re-use it when you have another version of the same output file. Very powerful, very fast. I have used it a lot, nearly always with "|" as the data field separator because it is safer than "," if your data contains text fields and especially address lines.

           

          If this suggestion cannot help you (I recognise that what you have to work with or what you need to output may not quite fit with this suggestion) I do not know of any way to set an alternative to the "|" within Monarch. However you could consider using the MSRP utility on your original file to change the "|" to a space. But you would still need to name the fields (I guess) and the other method provides, in my opinion, a faster way to do that.

           

          I hope this helps.

           

          Good luck.

           

          Grant

           

          Originally posted by Arne:

          Hi,

          I have to process many lists from SAP, which have a standard layout columns being separated by "|":

          xxxx

          xxx

          xxxx

           

          In modell definition there is the automatic trapping feature, but this seems to check for SPACE to define traps.

           

          Once I defined my traps in table view I can adapt each column to "start at preceeding |", but then I have done all the trapping already manually.

           

          Is there a way to tell Monarch V6.01 PRO to use other characters in trapping???

           

          wkr,

          Arne /b[/quote]

          • automatic trapping (discussion of Monarch V6 Pro with Delimited Files)
            Tom Whiteside

            Arne,

             

            I have a similar report in Oracle with 24 columns of purchase order information that uses the “|” to mark field boundaries.

             

            My standard layout is:

             

            xxxxxxx

            xxx

            xxxxx

             

            but it is never:

             

            xxxxxxx

            xxx

            xxxxx

             

            Try this.  Highlight your sample line and set a trap for one field only.  Your first field may be a fixed-width numeric field.  Mine is always a seven digit Transaction ID number.  I would make one trap - - only - - for the full width of my first field.  This would be:  NNNNNNN  (seven numeric N-umlauts).  Then, move your cursor from the first trap line to the very beginning of the second sample line.  From here you can click on the “Auto-Define Fields” icon symbol.

             

            For me, most of my fields get defined properly for width and for data type - - I always check to be sure, and then I have to name them.  For the two or three fields that do not get trapped at all, I can highlight and define these from the sample line.

             

            Monarch seems to get the right idea from seeing just one field trapped to just it’s exact field width.  When it defines the rest of my fields, it may not catch them all, but it never includes any of the “|” delimiters.  Be sure not to trap on any of the “|” characters in your one-field trap.

             

            See if this method is good for you.  It may not work if your standard layout has no spaces at all between fields, for example,  |xxxxxxx|xxx|xxxxx|.

             

            Let me know how this does or doesn’t work in your case.  Feel free to send me a sample, either through private messaging or regular e-mail.  I will be happy to look at your SAP report files.

             

            Good luck!

             

            Tom

            • automatic trapping (discussion of Monarch V6 Pro with Delimited Files)
              Arne _

              Thanks, Grant and Tom, for your help.

              Yes, Grant, your assumption are correct. As we produce many different lists all with max 255 columns and fields separated by "|" your suggestion is an easy way even for one time ad hoc reports. Only issue I have to solve is some columns that seem to be numeric should be kept as text with leading 0. I noted I can not change field type to text after importing via open database.

               

              Tom, you suggestion does not work for me, as some columns are not separated by " " and I don't want to adapt my input.

               

              Thanks to both of you,

              kind regards,

              Arne

              • automatic trapping (discussion of Monarch V6 Pro with Delimited Files)
                Grant Perkins

                Arne,

                 

                You may have worked this out already, but if not one answer is that you can cheat!

                 

                I agree that an option to import the field AND define that it should be text would be the best option. (Also there are some limits on the file extension that will be accepted as a database but renaming the file gets around them)

                 

                However, a quick 'fix' is to make a calculated field to convert the number to text and then use the new field instead of the original field.

                 

                str() should get you there. No cheating so far.

                 

                The leading zeros can be achieved as follows. The example assumes an 8 character database field (such as a Customer Number) which may have some data with less than 8 characters, so leaving spaces at the beginning of the number)

                 

                str(100000000+)

                 

                would create a 9 digit CHARACTER field with format 100001234 for example.

                 

                If we now define we want the calculated field todisplay only the last 8 characters of that data you will get you leading zeros when you need them.

                 

                right(str(100000000+),8)

                 

                is the formula that should make the complete conversion in one pass. You can play with the value 8 in the above. Also the display and data sizes for the field.

                 

                I hope something like this will work for you as well. There are other ways to get there using some of the other calculated field functions if you want to spend some time experimenting.      :cool:   

                 

                Good luck.

                 

                Grant

                 

                   Originally posted by Arne:

                Thanks, Grant and Tom, for your help.

                Yes, Grant, your assumption are correct. As we produce many different lists all with max 255 columns and fields separated by "|" your suggestion is an easy way even for one time ad hoc reports. Only issue I have to solve is some columns that seem to be numeric should be kept as text with leading 0. I noted I can not change field type to text after importing via open database.

                 

                Tom, you suggestion does not work for me, as some columns are not separated by " " and I don't want to adapt my input.

                 

                Thanks to both of you,

                kind regards,

                Arne /b[/quote]

                 

                [size="1"][ January 31, 2003, 02:33 PM: Message edited by: Grant Perkins ][/size]

                • automatic trapping (discussion of Monarch V6 Pro with Delimited Files)
                  Mike Urbonas

                  Originally posted by Arne:

                  Thanks, Grant and Tom, for your help.

                  Yes, Grant, your assumption are correct. As we produce many different lists all with max 255 columns and fields separated by "|" your suggestion is an easy way even for one time ad hoc reports. Only issue I have to solve is some columns that seem to be numeric should be kept as text with leading 0. I noted I can not change field type to text after importing via open database.

                  /b[/quote]Arne,

                   

                  If you could somehow insert quotation marks "" before and after the data you want Monarch to read as character fields, not numeric (such as account numbers of customer codes that start with leading zeroes), then Monarch would then read such columns as character fields.

                   

                  Is there any pattern to how and where your SAP "|" delimited output inserts spaces?  The answer to this question would be critical in determining how and if quotation marks could be inserted properly in some automated way.

                   

                  Mike