13 Replies Latest reply: May 15, 2014 10:03 AM by WinFXGuy _ RSS

    How to spilt the file with Monarch 10?

    WinFXGuy _

      Hi All,

       

      I am new to Monarch, so please bear with me

       

      I am trying to split this file based on the following creteria.

       

      [B]1SRTS03[/B]  indicates the beginning of a record. I need to capture all the lines starting from this tag until this tag reappears again.  This tag always occurs at the same location in the file.

       

      I need to split the file based on School.  That means I need to create seperate files based on the School number.  This school number always occurs at the same location.  Also if possible export these files as PDF.

      /LIST

       

      Is this even possible with Monarch 10?

       

      Any help is appreciated.

       

      Regards

        • How to spilt the file with Monarch 10?
          WinFXGuy _

          Anybody, any feed back please?

          • How to spilt the file with Monarch 10?
            Grant Perkins

            Hi WinFXGuy and welcome to the forum.

             

            If I understand you right you main objective at this time is to just split the report by school, leaving the format as it is. Is that correct?

             

            That should be possible but I wanted to make sure that you had no different extraction plan before suggesting a solution to your question.

             

            Grant

              • How to spilt the file with Monarch 10?
                WinFXGuy _

                Yes - I want to leave the format as it is.  Basically, the end result should be a file for each school. Also, there could be more than one occurance of school number in the original report file.

                  • How to spilt the file with Monarch 10?
                    Grant Perkins

                    Yes - I want to leave the format as it is. Basically, the end result should be a file for each school. Also, there could be more than one occurance of school number in the original report file.[/quote]

                     

                    OK, looking at the report in Monarch terms, if we were attempting to extract data from it, I see a list of schools in School number order and for some schools a student detail page.

                     

                    Each school gets one report page UNLESS it has more than one student listed in which case each student gets a report page.

                     

                    Your requirement is to extract, keeping the format of the report just as it is, a separate file for each school. This extract may be for one page or could be for several pages. If multiple pages are likely they will be sequential pages from the report.

                     

                    Does that completely describe the task?

                     

                     

                    Grant

                      • How to spilt the file with Monarch 10?
                        Grant Perkins

                        Just looking at the sample the page numbering suggests that the report either has a bug or is an amalgamation of separate reports in the first place. Is that likely?

                         

                        Any way to go back to the source and start from there?

                         

                        The way things are structured at the moment the best I can see is an export through a summary but is means dropping the first line of the report because we need to re-page it (using a Page Header template) and I see no easy way to extract the School number/name to use in the output file name, at least not using a one step process.

                         

                        So yes, can do something with Monarch but there may be other approaches, possibly including using a macro in a text editor, if all you want to do is break the report back out into the separate reports it seems to have been.

                         

                        On the other hand if you wished to extract the data and present it in a different form with some extra analysis I would say that Monarch would be the perfect tool for the job.

                         

                        I may have overlooked something so if anyone else has any ideas it would be good to hear them.

                         

                        Sorry it's not a more positive suggestion.

                         

                        Grant

                          • How to spilt the file with Monarch 10?
                            Joe Berry

                            I can get all lines identified by school using a two step process; however, before I post the "how to" I would like to know if it is a reasonable solution.  In order to start creating files by school, my method would require creating filters and exports for each school.  If there are alot of schools then this would take alot of work.  If there are ony a few this would work.

                             

                            Let me know the specifics.

                             

                            Joe Berry

                              • How to spilt the file with Monarch 10?
                                CAgee _

                                Having registered as a Version 10.00 user, how can I now Post My Own Questions?

                                  • How to spilt the file with Monarch 10?
                                    Grant Perkins

                                    Having registered as a Version 10.00 user, how can I now Post My Own Questions?[/quote]

                                     

                                     

                                    Select a forum - eg Monarch (this one).

                                     

                                    Go to the screen where all the threads are listed. (which is where you probably will be already).

                                     

                                    Near the top left there is a "New Thread" button. Click on that and away you go ...

                                     

                                    HTH.

                                     

                                     

                                    Grant

                                  • How to spilt the file with Monarch 10?
                                    WinFXGuy _

                                    I can get all lines identified by school using a two step process; however, before I post the "how to" I would like to know if it is a reasonable solution.  In order to start creating files by school, my method would require creating filters and exports for each school.  If there are alot of schools then this would take alot of work.  If there are ony a few this would work.

                                     

                                    Let me know the specifics.

                                     

                                    Joe Berry[/QUOTE]

                                     

                                    Thanks Joe and Grant.

                                     

                                    Joe, I don't think your solution would work, because there are lot of schools and school list may change over time.  It is not uncommon that some schools may be closed, merged or new schools may be opened within the district.  I am actually looking for a complete automatic process.

                                     

                                    Grant, we don't have any control over the source as we receive these files from somewhere else.  The only option is to work with what we get from state.

                                     

                                    Well, looks like Monarch may not be the right product for this task.

                                     

                                    Thanks

                                      • How to spilt the file with Monarch 10?
                                        Joe Berry

                                        I remembered a new feature in v10 that I had not used yet.  I tested it on the data that you posted and found that it does what you want it to do as I understand it.

                                         

                                        The first model would define each line in the detail template as a report line.  The template would not trap any characters in the line, but would define all the line as one field – ReportLine.  Next define an Append trap with the line that contains the school info.  Trap it using “School:”.  Define the school name/number as “School”. 

                                         

                                        Now comes the tricky part of the process.  Since the append line starts later in the report than the first detail line, this will leave the lines above without a school and will not assign the proper school to the first three lines of the next school.  We will fix that with a couple of tricks and the second model. 

                                         

                                        In this model, define a calculated field for the first three lines of the report as “HeaderLine”.  I used the following to define HeaderLine:

                                        If(

                                           Left (ReportLine,5)="1SRTS" .Or.

                                           Left (ReportLine,5)=" COMP" .Or.

                                           Search_XL("X01  R E S P",ReportLine,1)

                                        ,1,0)

                                         

                                        Next define a calculated field for School named D-School to only be assigned to lines that have a HeaderLine value of 0:

                                        If(HeaderLine=0,School,"")

                                         

                                        Next define a calculated field that is equal to the record number, DtlRecNo:

                                        RecNo()

                                         

                                        Define a sort based upon DtlRecNo, descending.  Hide the fields School and HeaderLine.  Export D-School, ReportLine, and DtlRecNo to a text file with Fixed Length.

                                         

                                        Import this text file into Monarch and define D-School and mark the Copy Value from Previous Record radio button.  Also define the ReportLine and the DtlRecNo.  Now sort on the DtlRecNo, ascending.  This should put the school data with the correct report lines.

                                         

                                        Now create a summary using D-School as the Key Field and Hide the key column.  Next use ReportLine as the Item Field.  Now create an export for the summary. Be sure to mark the checkbox "Each distinct value of the first key makes a separate file or table".  You can then export to Excel or Text and have a separate file for each primary key (school).

                                         

                                        I hope this helps.  You can totally automate this using batch files.  Just read Chapter 9 of the Help.

                                         

                                        Joe

                                        • How to spilt the file with Monarch 10?
                                          Grant Perkins

                                          If you really must come up with a way to reverse an earlier step in the process then try this;

                                           

                                          Use the Monarch Utility (or some editor with a FAST string replacement facility) to re-set the page breaks that seem to have gone missing at some point. More specifically only the page breaks for Page 1 lines.

                                           

                                          In the utility copy a sample page 1 line and paste it as the string to be replaced. Paste it again in the string you pant to output but add "/012" (without the quotes) at the beginning of the line. This will insert a New Page command.

                                           

                                          Now you can print of export the Report file from Monarch (or anywhere if Printing) as a PDF file. There are free utiilies around that will allow you to split a multipage PDF file into separate single page files.

                                           

                                          There may well be other methods of doing the same thing without having to go to PDFs.

                                           

                                          Not sure what you will want to do about identifying the file and a naming convention for the split files.

                                           

                                          Monarch has some features that seek to deal with these things but currently expects that point to be after extraction and analysis rather than right at the start of the application. ID can be created as part of the analysis - but there is none going on here so far.

                                           

                                           

                                          HTH.

                                           

                                           

                                           

                                          Grant

                                            • How to spilt the file with Monarch 10?
                                              RalphB _

                                              Wow.

                                               

                                              After close inspection of the file and what you want I remembered something I used to do for a client a few years back when we only had Monarch Pro 6.  I no longer use it because we are no longer servicing the client.

                                               

                                              If I remeber correctly, I picked this tip off of a Monarch User Conference CD from 2003 or 2004.  Back then, you didn't need to attend the conference to get the CD.  I learned a lot from those disks.

                                               

                                              Any way, Joe is on the right track.  First step is to select any row as your detail template and hi-lite the entire row.  You do not need to set a trap here.

                                               

                                              Next, select the top 4 rows as a multi-line append template and trap on 1SRTS03 then on the 4th line hi-lite the school number and name it.  This will put the school number on each line that corresponds to it.

                                               

                                              Then in the table window hide the school number and set up a filter for each school number.  This will give you a report for each school.

                                               

                                              You could also set up a run-time parameter for the school and input it each time for each school instead of setting up individual filters.

                                               

                                              HTH.