14 Replies Latest reply: May 15, 2014 10:12 AM by Olly Bond RSS

    Parsing Description and Multiple Floating GL#s per row?

    scrtchmstj _

      I am able to export our balance sheet GL specs to a text file but I cannot get Monarch to parse this data in the form I'm needing. Basically, here's what some lines on the original text file might look like


           6  SU 701-221;701-312;A=1;2;3

           7  SU 701-13;701-115;701-500;

           8  LT T=Loan A;A=1X;C=1  

           9  SU 701-316;A=1;2;3;4;5;6;7

          10  LT T=Loan B;A=1X;C=1


      I would like to have it parse this info as such:


      Loan A      701-221

      Loan A      701-312

      Loan A      701-013

      Loan A      701-115

      Loan A      701-500

      Loan B      701-316


      I can't really use the MCR (multi column region) because the GL numbers float. The closest I've been able to get is something like the following (i.e. multiple GLs in multiple columns per row):


      Loan A        701-221  701-312                              

      Loan A        701-13        701-115    701-500               

      Loan B        701-316

        • Parsing Description and Multiple Floating GL#s per row?
          KeyserSoze _

          I was able to create the exact layout that you wanted using some calculated fields, but it only yielded two records; one for each loan. But the problem is that Monarch may not export the calculated fields properly when the calculation uses Chr(10) + Chr(13) for a new hard return between text within a single field.


          So instead a whole new approach is necessary to get six records from your sample.


          I used the Monarch utility to create new lines in the report to make it easier to build a model.


          I replaced all occurrences of the semi-colon character (;)[/NOPARSE] with an ASCII sequence /010/013, being certain to change the option from ANSI to ASCII.


          Then I built a model for this new report file. I added a detail template using the trap NNN-NN, and selected the floating trap option. Next I added a footer template, again using the floating trap option using the word LOAN.


          This creates six records in the format that you want.


          If this is a process that you will perform regularly then you can prepare a batch file to first have the Monarch Utility do the search and replace and then immediately have Monarch open the modified report and the appropriate model.