8 Replies Latest reply: May 15, 2014 9:59 AM by Grant Perkins RSS

    Data wrapping & Variable field lengths

    Deb _

      I have a report I'm trying to pull data from and everything works fine until I run into a date field that wraps.  The data is in a delimited format with variable field lengths.  I have no problem getting to the data on the first line, and I can get to the data on the second line with another field, however, occasionally there is a 3rd line that is prefaced with the same wrap indicator and that field overwrites the data from the second line...

      For example:   ID|1|0099999|0099999||LASTNAMEFIRSTNAMEE||19810       

          ..302|F|||9999 E STREET~CITYST~89999|99|27662              

          ..44||10|1|||053999999||||||||NOT A VETERAN|||NO       

       

      I need to be able to pull the DOB 19810302…instead I get 198144

       

      Do you have any suggestions?

        • Data wrapping & Variable field lengths
          Nigel Winton

          Deb

          Have you tried using the Advance Tab on the Field Properties. There you can set the Field End On Options. This is used when a field wraps and allows you to set how Monarch picks up that field. Monarch will treat wrapped fields as one complete field allowing you to parse that as you require.

           

          You will need to define your trap on the first line of the field only. Do not try to trap any other lines. Then through the Advanced Tab you can set how Monarch decides when the field has finished wrapping. One of the last 2 options usually works unless you know the Line Count will not change each time this field occurs. In your case this would appear to change so you need to experiment with the options to see which one works best for you.

          Let us know how you get on.

           

          Regards

           

          Nigel

          • Data wrapping & Variable field lengths
            Grant Perkins

            Deb,

             

            If you have a PRO version of Monarch the easiest way to deal with this is to read the file as a database and specify the | as the separator character.

             

            If a single record cannot be more than 1000 characters (your sample suggests it probably cannot but I can't be sure) you could also modify the input file to give 1000 character wide single lines with fixed width fields. See the comments below about using the PREP utility to present the records as fixed width fields in lines 256 characters wide (a fixed number of multiple lines) that should make modelling much easier.

             

            If a single record line is longer than 1000 characters there are other things you could do to manipulate it to discard any part of the record you do not require. Or if you have version 8 Monarch you will have a 4000 character record capability anyway.

             

            The | cannot be used a a floating trap character which leaves you with a problem if fields are of variable size, but it would be rather messy anyway try to work with floating traps on large and very variable records.

             

            You could also consider using the MSRP.EXE utility that comes with the Datawatch installation so swap the | for a different separator. This is really only viable if you can get the record onto a single line.

             

            More likely you will be looking at using the PREP.EXE utility to 'prepare' the records in the file by making them more easily accessible for modelling purposes.

             

            Personally I would still go for reading the file as a database (assuming the display is wrapped and the real file only has end of line controls at the end of a record and has not be changed in any other way by some intermediary process) since it is much easier than any other approach.

             

            At the simplest level you can use the database read method to extract a revised 'report' from the file and then save that ready for a different Monarch model to be used for the final data manipulation if you are more comfortable with that approach.

             

            HTH.

             

             

            Grant

            • Data wrapping & Variable field lengths
              Sue DeWitt

              I'm attempting to trap the detail below.  (The appearance below does not look the same as in the report.)  I'm running into problems with the variable line length of the Description.  This variation is pushing the detail of the entered and account amounts to another line.  Any suggestions would be greatly appreciated.                                                                               

              |                    

              Accounting  Supplier                   Document         Doc         Line                                          |   PO               

              Date        Name                       Number           Class       Type            Description                   |   Number           

              -


                -


                -


                -


                -


                -


              -


              -


                                                                                               

              < -


              Entered Curr -


              >          < -


              Accounted Curr -


              >                        

                                          Curr                   DR                   CR                   DR                   CR                   

                                          -


                  -


                   -


                   -


                   -


                                                                                               

              29-OCT-06   Bell Conferencing Inc      123456789        Invoice     Charge                                      Yes                    

                                          CAD                 9.52                                      9.52                                                                               

              29-OCT-06   Bell Conferencing Inc      123456789       Invoice     Charge                                      Yes                    

                                          CAD               228.65                                    228.65                                                                               

              29-OCT-06   Bell Conferencing Inc      123456789        Invoice     Charge          A/C #1-00-xxxx-xx-xxxxxxx                          

                Yes                                                                               

              CAD                 6.16                                      6.16                                                                               

              29-OCT-06   Bell Conferencing Inc      123456789        Invoice     Charge          A/C #1-00-xxxx-xx-xxxxxxx                          

                Yes                                                                               

              CAD             1,727.70                                  1,727.70                                                                               

              31-OCT-06   Bell Conferencing Inc      123456789        Invoice     Charge                                      Yes                    

                                          CAD                58.38                                     58.38

              • Data wrapping & Variable field lengths
                Grant Perkins

                Sue,

                 

                 

                Does it look something like this?

                 

                Originally posted by Sue DeWitt:

                                                              [font="courier"]                                                                |                    

                Accounting  Supplier                   Document         Doc         Line                                          |   PO               

                Date        Name                       Number           Class       Type            Description                   |   Number           

                -


                  -


                  -


                  -


                  -


                  -


                -


                -


                                                                                                 

                < -


                Entered Curr -


                >          < -


                Accounted Curr -


                >                        

                                            Curr                   DR                   CR                   DR                   CR                   

                                            -


                    -


                     -


                     -


                     -


                                                                                                 

                29-OCT-06   Bell Conferencing Inc      123456789        Invoice     Charge                                      Yes                    

                                            CAD                 9.52                                      9.52                                                                               

                29-OCT-06   Bell Conferencing Inc      123456789       Invoice     Charge                                      Yes                    

                                            CAD               228.65                                    228.65                                                                               

                29-OCT-06   Bell Conferencing Inc      123456789        Invoice     Charge          A/C #1-00-xxxx-xx-xxxxxxx                          

                  Yes                                                                               

                CAD                 6.16                                      6.16                                                                               

                29-OCT-06   Bell Conferencing Inc      123456789        Invoice     Charge          A/C #1-00-xxxx-xx-xxxxxxx                          

                  Yes                                                                               

                CAD             1,727.70                                  1,727.70                                                                               

                31-OCT-06   Bell Conferencing Inc      123456789        Invoice     Charge                                      Yes                    

                                            CAD                58.38                                     58.38 /font[/quote][/b][/quote]

                • Data wrapping & Variable field lengths
                  Sue DeWitt

                  Grant, that's essentially what it looks like. 

                   

                  The trap is set on the first line of the detail information as "NN-" in the postions corresponding to the date string such as 29-OCT-06.

                   

                  The field between Description and PO Number is called "Transferred to GL". 

                   

                  In this example, the model does not pick up the 6.16 and 1,727.70 in the 3rd and 4th records of the report.

                   

                  Thanks!

                  • Data wrapping & Variable field lengths
                    Grant Perkins

                    Sue,

                     

                    I'm curious as to why the inclusion of the account number causes the problem it does. The header line with the field names seems to work fine. I am beginning to wonder if there is simply a problem when the report is being printed to the file and if that could be rectified.

                     

                    My plan B is that you look at the possiblity of using the dollar amount line ONLY as the detail and the troublesome line above it as an append.

                     

                    Does the problem line ever have a PO Number (last field?) If so do you need it for you puproses? Is there another way of determining whether the "Yes" field would be yes or no?

                     

                    One way to deal with the anomaly would be to extract the entire troublesome line as a single multiline field and then slice and dice it to get the individual fields from it. Most of it would be OK so only the problem end section to deal with.

                     

                    If you did not need to extract the Yes/No field or the last number field you could just ignore them and therefore the entire intermittent second line.

                     

                    Alternatively you could set up 2 appends using the same trap line. The multiline from which you will simply process the last 2 fields IF THEY EXIST and single first line for which the fields are easily defined when they are all in that line.

                     

                    It would still be worth checking out the apparent print problem first in my opinion.

                     

                    Are there any other occasions when something not in the sample would make this possible solution impractical?

                     

                     

                    HTH

                     

                    Grant

                    • Data wrapping & Variable field lengths
                      Sue DeWitt

                      We've installed an interface that is capturing the input data that feeds our A/P system.  It appears what's happening on those items is that it's including more characters for the "Description" field than what is defined for the report so it's pushing the "Yes" to the next line. 

                       

                      Is it possible there's a hard return built into the report following the "Yes" that is forcing the Currency and Amount fields down one more line? 

                       

                      As far as capturing the "Yes" field, we generally don't need that field unless it would happen to be "No".  (I've never seen it say no on the report but doesn't mean it couldn't happen.)

                       

                      PO Number is needed if it's populated.  This appears on the same line as the Curr/Amts, not the line with date, supplier, etc.

                       

                      If the above information doesn't help solve, I'll probably use the Append idea for the date, supplier, etc and the Cur/Amt for detail lines.

                       

                      Thanks,  Sue

                      • Data wrapping & Variable field lengths
                        Grant Perkins

                        Sue,

                         

                        I suspect you are right about the hard coded new line after the 'YES' field.

                         

                        That the Order Number apears on the second line is good news!

                         

                        So I think I would surely go with the line 2 as detail and line 1 (somtimes 1 and 2) as an append.

                         

                        As a quick and dirty solution for the Yes/No on the second line see if this works for you.

                         

                        When you define the field for the date in the first column make it a multi-line field. It looks like it would be safe to set it a 2 lines for the End field. As the Date had a character month you will need to extract as a character field anyway and then convert to a date if you need a date field, so you may as well capture whatever is on the line below as well. You can use the V8 TextLine function in a calculated field to pick the contents of the second line - blank, Yes or No. (Or even limit that so it only reports blank or No.)

                         

                        It looks like the other fields will extract normally from the template.

                         

                        If you need to make the date a date field you will have to translate the month to a number (lookup table should do it) but I will guess you are probably aware of that already.

                         

                        Does that help?

                         

                         

                        Grant