4 Replies Latest reply: May 15, 2014 9:57 AM by Grant Perkins RSS

    Trapping variable length fields without a floating trap

    Kelly _

      I'm trying to Monarch some files that have variable length fields, and the fields are prefixed by bracketed codes that will become spreadsheet headings. ex: The records won't have every code in every record. [font="courier"]VFV8811  0228810881887Z 1000200608815QMGFV0088000135000000025000288108818879VXBBVXGS CU0001268828818832V12 FCUCVZD881886688ZOBBXLD FOWLKSVVD688358888ZOBBXLD FOWLKSVV2013 VVWSSVWOOD LBBKBBOXVILLS, VBB 37932* VFV8811  0228810881887Z 1000200608815QMGFV0090003660000001880000288108818879VXBBVXGS CU000221188889656VVSZV CU ZOZVVVMOUVHCVZD3880885075VSZSVVX V KIBBGD8852322880VSZSVVX V KIBBGCMZ 8819 BOX 1609XZO XS 09102-1600* VFV8811  0228810881887Z 1000200608815QMGFV0050003688000002132700288108818879VXBBVXGS CU000308865006388CSBBV VZ JSFF CIVVCVZD103188881XVOM IID288108818879XVOM VVVUDSBBV LOXBBVV88020 FSS FSS ZD.BZIDGSVOBB, MO 6308888* VFV8811  0228810881887Z 1000200608815QMGFV01880012370000001988000288108818879VXBBVXGS CU000882712918826VSXCHSZVV CU VV BSBBDCVZD88888897188SZIC HXBBCOCKD8875279SZIC HXBBCOCK12532 ZOVVV GZOVS DZ.VVV LOUIVV, MO 631886* VFV8811  0228810881887Z 1000200608815QMGFV003001211000000079990288108818879VXBBVXGS CU0005111906271FIZVVV KILLSSBBCVZD016 760 910BBICK Z. MOFFIVVD887068888BBICHOLXVV MOFFIVV12881 GZSSBBWXV CHXVVSFLOZIVVVVXBBV, MO 63031 VFV8811  0228810881887Z 1000200608815QMGFV002001229000000300000288108818879VXBBVXGS CU*00060620011886COMZXVVVV BXBBKCVZD188588313HOV BUBBLSVV OF FUBB388399 HIGHWXV 59ZOBSZVVVDXLS, XL 36567D88688397ZOBSZV HSUMXBB315 LIBBDX LBBOFXLLOBB MO 63366* VFV8811  0228810881887Z 1000200608815QMGFV012001220000000160000288108818879VXBBVXGS CU0007026009593BK XMSZ BBVCCVZD0055638888882188JOCOB VZUCKIBBG IBBC.12188 GOLDSBB ZOBBD LBB.ZOCKLSDGS, FL 32955D39320BBXBBCV DXVIVV157988 VVCSBBIC GZSSBB CVCHSVVVSZFISLD, MO 63017 VFV8811  0228810881887Z 1000200608815QMGFV00700208850000000880000288108818879VXBBVXGS CU0008825317588988COXVVV FCU ZXLSIGH*CVZD000519288300250ZOBSZV D WIBBVSZD88788065ZOBSZV D WIBBVSZ108805 BZSCKSBBZIDGS DZVVV XBBBB MO 630788 VFV8811  0228810881887Z 1000200608815QMGFV01500211000000008808890288108818879VXBBVXGS CU00090910361688CVZD20092900IZVVVVXZX KOOZ3188-2688-52883*988316099588:VXBBV:VXBBVXGS CZSDIV UBBIOBB:098855:06:12:D73100000VXBBVXGS CZSDIV UBBIOBB88020 FSS FSS ZD.BZIDGSVOBB, MO 6308888V$88088.90:* VFV8811  0228810881887Z 1000200608815QMGFV00700302100000001881880288108818879VXBBVXGS CU0010288108855885MO COZZ CU VVVL MOCVZD28810882339GXVSWXV MSVZO CZSDIV UBBIOBBD625-688000VXMMIS HXMILVOBB*D8869576VXMMIS HXMILVOBB10 CXZZI DZ.FLOZIVVVVXBBV, MO 63033

      VFV8811  0228810881887Z 1000200608815QMGFV001003888888000006200000288108818879VXBBVXGS CU0011288108855885MO COZZ CU VVVL MOCVZD288108818879VXBBVXGS CZSDIV UBBIOBB88020 FSS FSS ZDBZIDGSVOBB, MO 6308888D288108818879VXBBVXGS CZSDIV UBBIOBB88020 FSS FSS ZDBZIDGSVOBB, MO 6308888*

        /font[/quote]Any ideas?  Thanks!

        • Trapping variable length fields without a floating trap
          Grant Perkins

          Originally posted by Kelly:

          Any ideas?  Thanks! [/b][/quote]Hmm, quite a challenge.


          Not knowing what you may have tried already let me just throw some ideas in the hat for now and see which appeal to you.


          1. Pre-prepare the file into fixed width columns?


          2. Consider the floating trap anyway - but it could get messy and you need to know the MAX number of characters possible in each of the 'floating' fields in order to be sure the trap will work. (In fact I had a quick play with this idea and the variability of the lines seems to suggest it would become impossible to make it work effectively and reliably - which is not really a surprise as it would be pushing the concept of the floating trap well beyond what it was intended for. I'm guessing from your subject title that you have already done the same.)


          3. Take the majority of the row as a single field and slice and dice it as required. Downside - there are quite a few fields to work with ...



          The secondary problem would be getting the fields into their appropriate columns in the spreadsheet.


          It looks like many of them may be consistently positioned across the line (even though some of them float the relative position remains the same).


          However some don't?


          For those you make need to create an intermediate field which includes the bracketed number and then  use that part of the extract to identify which column it belongs to, dropping the code but populating the column with the rest of the content.


          To some extent that will be a problem to address no matter which of the first two suggestions above you would follow.


          If the sample is representative it looks like about half (maybe more) of the fields are relatively consistent in that they do occur and occur at an identifiable relative positions across the row. In that event it should be possible to pull the consistent fields in one and then just slice and dice what remains.


          If the sample is not quite as representative as it seems meaning things are more random it may be as easy to just use slice and dice techniques for all fields (or most of them) since the creation work will become mostly duplication and edits.


          Does this offer anything of potential use?




          Edit to add:


          Actually it gets more complicated since there seems to be no obvious sure and simple way to split the lines to accomodate the 254 char max field width yet avoid making the slice and dice processing more complex.


          Are you using Monarch 6 Pro or standard?


          [size="1"][ April 11, 2007, 08:36 PM: Message edited by: Grant Perkins ][/size]

          • Trapping variable length fields without a floating trap
            Grant Perkins

            Further to the previous - I really hope you have the Pro version. If you read the  input as a Database and specify the field separator as "{" the first 9 fields appear to extract cleanly (at least from the sample). You would need to modify them slightly using calculated fields to strip the column header numbers off the front of the data strings but that is not difficult.


            The last 4 or 5 fields are not consistent. Again calculated fields can be used to populate the columns that you will eventually export based on the 'heading' value at the beginning of the fields. A bit untidy but quite possible since you will know what values to expect for the columns which may appear in the report.



            • Trapping variable length fields without a floating trap
              Kelly _

              Thanks for the good ideas.  I will try some of that stuff.  I am using Monarch 6 Pro.  I'm not against using a floating trap, I just couldn't get it to work.  Thanks again.



              • Trapping variable length fields without a floating trap
                Grant Perkins

                Originally posted by Kelly:

                Thanks for the good ideas.  I will try some of that stuff.  I am using Monarch 6 Pro.  I'm not against using a floating trap, I just couldn't get it to work.  Thanks again.


                Kelly /b[/quote]OK, since you have PRO I think the database route is the clear way to go.


                When you read the file in set the separator character to {  .


                The first fields in the line will show up OK except they will start with the field 'name' (which you will already have set the first time you open the database anyway - be sure to save the model for re-use.)


                For each you can create a calcualted field for a new version. Use the LSPLIT() and split on the } character taking part 2 of the split field.


                The last few fields which can be variable are a bit trickier.


                Effectively you can assume that certain field will exist (I assume there are a limited number of fields possible?) so simply set up a calculated field for each possible field and test the multiple field string to see if it exists.


                Basically anything after the field looks like it might be inconsistent so the logic for each calculated field that might appear there would be


                See if the string exists (using INSTR())

                If it does INSTR will tell you where your search string starts. You can use this value, adjusted to take into account the length of the search string, to find the start position of the data you really need. Take the rest of the field OR a suitable number of characters if you know the field is of limited width.


                If it is always a fixed number of characters you can simply extract the string using SUBSTR().


                If it is a variable length take the extraction and then LSPLIT() this time using the "{" character as the split point and taking the first part of the split.


                In each case the formula will be quite similar so once you have one working you can cut and paste the formula into the next field and make appropriate edits to suit each field.


                (Maybe you know all of this already ... if so I apologise for being so long winded.)


                You are right about the floating trap - very difficult to find a report of this nature which will be suited to the original concept floating traps were intended to address.