4 Replies Latest reply: May 15, 2014 10:02 AM by mcgoffmm _ RSS

    Model for Multiple Line (per record) data file

    mcgoffmm _

      Hi

      I've seen SOME threads about this topic on this board, but havent found anything EXACTLY like this, so I'm asking the question....

       

      I have a file that has records that randomly change in length.  I'm trying to get records that DO have more than 1 line to be "connected" with the line above it when I export the model as a table.  The max lines per record is 2, but records dont always have 2 lines.  The second line is a "free-form note" field that is important for determining information about the record. If the second line for the record exists, it ALWAYS starts with a semi-colon.

      I've provided a sample below (which I hope translates well on the page) to help. 

      Thanks to anyone that has a suggestion

       

      11111111,091108,div,cs,msft     ,       60.50,cash  ,           ,         ,          ,           ,     

      ;QDV  Dividend Transaction Qualified                                                                  

      22222222,091108,sel,cs,ibm      ,     5856.78,cash  ,     50.000,     8.95,  117.3153,           ,

      33333333,091108,div,cs,msft     ,       23.10,cash  ,           ,         ,          ,           ,     

      ;QDV  Dividend Transaction Qualified                                                                  

      44444444,091108,sel,cs,ibm      ,     2337.34,cash  ,     20.000,     8.95,  117.3153,           ,

      55555555,091108,div,cs,msft     ,       95.15,cash  ,           ,         ,          ,           ,     

      ;QDV  Dividend Transaction Qualified                                                                  

      66666666,091108,sel,cs,ibm      ,     9962.79,cash  ,     85.000,     8.95,  117.3153,           ,

      77777777,091108,div,cs,msft     ,       47.30,cash  ,           ,         ,          ,           ,     

      ;QDV  Dividend Transaction Qualified

        • Model for Multiple Line (per record) data file
          Grant Perkins

          Hi mcgoffmm and welcom to the forum.

           

          You seem to have a mix of challenges here which, individually, are not too difficult to address once the way forward is explained but together offer opportunities for several approaches to the problem. It's not easy to make a confident 'best way' recommendation without knowing the full requirement so I'll kick off with a couopld of possibles.

           

          Firstly, observing the commas, it lloks like a good way forward would be to read the file (as a csv file) into Monarch Pro as a DATABASE with fields separated by commas. However the semi-colon is a minor problem with that. We need to make the extra line, when it appears, an additional field at the end of the always existent line.

           

          You could do this using the Monarch Utility (or other favourite text editor application) to remove the CRLF (Carriage Return/Line Feed) at the end of the lines that are followed by a line starting with a ";" and then swap the semi-colon for a comma. That would give a single line (as far as Monarch is concerned)  that could be read as a database with the 'extra' field populated or not according to whether the ; line appears.

           

          Plan B.

           

          If you prefer to read the file as a 'report' (and I am assuming if you are you probably already have a model that works for this at least as far as the first line extraction?) then you need a little trick to get the second line when it exists. Either way you end up the a field which will be populated only when a seciond line exists. This will NOT work if you are using a floating trap on your first line.

           

          The thing to do is create an APPEND template using EXACTLY the same trap as for the first line. The sample data will not matter (unless you are using a floating trap, in which case this will not work anyway).

           

          Paint a field across the line for as wide as the additional descriptive text might be starting AFTER the position of the semi-colon. Right click the fioeld and go to the Advanced properties tab. Set the START FIELD ON preceding string value to ";" (without the quote marks.)

           

          That should give you an append that resets for each new Detail template and is only populated when the line starts with a ;  .

           

          There may be another approach along similar lines that we could use if you are using a floating trap for the details. I will check that if neither of thes ideas work for you.

           

          My personal choice would be to treat the file as a database (so suggestion one) making sure that the field sizes you set in the template are large enough to deal with any varaiation in the field widths for future inputs. If this is a regular process you could establish a small batch process to parse the file into single line records using the Utility and then run the monarch model on it so making the entire process a 'single click' repeatable operation.

           

          Does this help at all?

           

           

          Grant

            • Model for Multiple Line (per record) data file
              mcgoffmm _

              Thanks for getting back to me

               

              1 thing I didnt mention before.  This file is a fixed with file and the space between the commas is consistent in each line for whatever field it is....that didnt translate well when I copied my sample into this forum.

               

              OK so here's what I've done

               

              I've created a 1 line Detail template with a numeric trap as the first character and then selected all of my fields I need

               

              I then created a 2 line Append template (not sure if this is right) where the trap on line 2 is ';' because I got an error thrown at me that I can't create an Append Template with blank traps.  (I replicated my detail trap and fields for the first line of the append template)

              For the second line of the append template, I created a field that stretched the entire length of the file, went to the properties of that field, and said that it should have a ';' preceeding in that line

               

              What I got was all the detail records I was expecting, but EVERY (except for 1, curiously enough) had the append value I was hoping to get for only the records that actually had the "note field"

               

              Did I do something wrong?  Can I give more info to help diagnose?  I appreciate your help on this.

                • Model for Multiple Line (per record) data file
                  Grant Perkins

                  Thanks for getting back to me

                   

                  1 thing I didnt mention before. This file is a fixed with file and the space between the commas is consistent in each line for whatever field it is....that didnt translate well when I copied my sample into this forum.[/quote]

                   

                  That's good. Slightly unusual, but good. I would guess that the gaps, if consistent from one run of the report to the next, define the max field widths.

                   

                   

                  OK so here's what I've done

                   

                  I've created a 1 line Detail template with a numeric trap as the first character and then selected all of my fields I need[/quote]

                   

                  Good ...

                   

                   

                  I then created a 2 line Append template (not sure if this is right) where the trap on line 2 is ';' because I got an error thrown at me that I can't create an Append Template with blank traps. (I replicated my detail trap and fields for the first line of the append template)

                  For the second line of the append template, I created a field that stretched the entire length of the file, went to the properties of that field, and said that it should have a ';' preceeding in that line[/quote]

                   

                  Not quite.

                   

                  For the append you want a ONE line sample for the template. The actual sample data does not matter since your report is, effectively and if I read it right, a fixed width column report but happens to have commas in the line.

                   

                  Trap using the first character is numeric just as for your detail line. Paint a field all the way across the line EXCEPT[/B] for the first character. (Assuming the semi-colon is the first character. If not start the field AFTER the position of the semi-colon.) Then use the field Advanced Properties to set the "[B]Start Field On[/B]" to a semi-colon "[B]on the current line[/B]" (IE NOT the option for the preceding string to be on the previouos line.) If the additional lines can never be more than one line leave the "End on" value to "1 Line".

                   

                  That should do it.

                   

                  What you get is an append that, in effect, 'resets' every time Monarch parses a detail record (because you are using the same trap) and a fill for the field ONLY where a line starting with a semi-colon exists after one detail record and before the next.

                   

                  HTH.

                   

                   

                  Grant