15 Replies Latest reply: Mar 18, 2016 11:23 AM by Olly Bond RSS

    Combine multiple templates into one

    baghu_m _

      Hi,

       

      I'm using Monarch 11 and need help to save my time.

       

      I've 20 unique reports and built 20 unique models and extracting same details from all different formats.

       

      Say, I need to extract Name, Sex, Age, Dob, Address etc from multiple reports and I should have only one consolidated excel sheet for my further analysis.

       

      Is it possible for me extract the data based on the file name? It means, Monarch should read my file name and apply relevant model to extract the data.

      Once I could read my first file with my first model, it should read the second file / apply second model and so on.

       

      (or)

       

      Is there any way for me to combine multiple models into one?

       

      (Or)

       

      If this is not possible, can I automate this process?

       

      Please advise.

       

      Thanks

      Baghavathy@

        • Re: Combine multiple templates into one
          Joe Berry

          If the reports are identical then multiple reports can be loaded into Monarch and processed with the same Model.  If they are not, I am unaware of anyway to have Monarch pick the model based upon the data.

           

          In v10.5 Monarch had an import feature that would allow you to import calculated fields, filters, sorts, and templates from other models.  I think it was in v11; however, I skipped that version.  It is not currently in v12 or v13.  This could help, but I di not think it would do exactly what I think you want to do.

           

          Part of the process can be automated into a batch file provided your version of Monarch 11 is below v 11.5.  I still use batch files on v10.5 to process multiple models/projects.  It is a tremendous help.

           

          I hope that I helped in some small way.

          • Re: Combine multiple templates into one
            DataExploiter _

            The manual way works. Create a new field called “report” using the function File(), create a filter using the new field “report”, select the appropriate filter and then appropriate model, export results to Excel. Repeat the process for all reports. I doubt you can combine multiple models; yea I’m sure you can’t. Yes it’s possible and easy to automate the entire process. Datawatch sells automator. It used to be called data pump. I’ve used in the past it works with no issues. Lastly I never meet these guys. Olly Bond is an expert who used to monitor and answer questions 24/7 on this board. You can find his email. He also put together a site MONARCHEXPERTS.COM with other former Datawatch consultants. They are from the UK and Europe.  Good luck.

              • Re: Combine multiple templates into one
                Olly Bond

                Hello DataExploiter, Joe and Baghavathy,

                 

                1. Can multiple reports be combined in one model?

                Yes, up to 1024 files, if the reports are all CSV files, or text files where the same templates fit the data. In the tutorial files you can open ClassJan, ClassFeb and ClassMar with Lesson10.xmod, for example.

                 

                2. When working with multiple reports, can the name of the input file be used?

                Yes, using a calculated field based on File(). Rsplit(File();2;"\";1) is a useful way to just get the filename and not the path.

                 

                3. Can the data from multiple models be exported to one Excel file?

                Yes, using the "append data to existing table" option in the export dialog.

                 

                4. Can this be automated?

                Yes, either using desktop automation (available in Monarch Pro up until version 11.4, and since then only available through a higher licence fee), or using DataPump / Automator - the server versions of Monarch.

                 

                Hope this helps,

                 

                Olly

                  • Re: Combine multiple templates into one
                    baghu_m _

                    Dear Grant,

                     

                    Thanks for your response and please review my below answers.

                     

                    I have no idea how different your input reports are or will be in the future.

                     

                    ----I'm getting reports from different hospitals and each hospital sends me a unique format and as i'll be getting reports from 20 different hospitals, i'll have 20 different formats. But, I don't anticipate the format will get changed frequently from each hospital. Also, I'm trying to extract the demographics from each report (from each hospital), the number of fields in my excel sheet will always be the same. But, if i'm adding a new hospital in my list (say 21st hospital), I may probably need to build a new model for the 21st format.

                     

                    Is this a one-off exercise or something that will be repeated regularly - perhaps with constantly changing formats for the input reports?

                     

                    ----Hope my first point answers your second question too.

                     

                    It might be worth spending a little time considering whether the inputs reports (or maybe some groups of them) are sufficiently similar that they might be processed with a single model - something that is flexible enough to find and extract the data even if the source report file format varies from file to file. It all depends on what the variations are.

                     

                    ---It cannot be processed with single model as each format is unique from each hospital and i need to certainly build a unique model.

                     

                    It may not be remotely possible of course .... but in some situations looking at the challenge that way, if it can be achieved, might save more work in the future.

                    • Re: Combine multiple templates into one
                      baghu_m _

                      Dear Olly,

                       

                      Please review my below answers and thank you very much for your suggestions.

                       

                      1. Can multiple reports be combined in one model?

                      Yes, up to 1024 files, if the reports are all CSV files, or text files where the same templates fit the data. In the tutorial files you can open ClassJan, ClassFeb and ClassMar with Lesson10.xmod, for example.

                       

                      ---I understand that Monarch will allow me to extract report from multiple reports. My situation cannot be addressed as my input records are pdf files, unique formats and my records are generated from different models.

                       

                      2. When working with multiple reports, can the name of the input file be used?

                      Yes, using a calculated field based on File(). Rsplit(File();2;"\";1) is a useful way to just get the filename and not the path.

                       

                      ---Yes. I've used this function to extract only the file name. But, my original question was whether I can instruct Monarch to search for a specific file name and apply a specific model. Say, Monarch should automatically identify the files which starts with the word "Monday" and apply the specific model called 'Monday model". The process can get continued further and Monarch can look for the file name "Tuesday" and apply the associated model called "Tuesday model" and so on and so forth. As I've mentioned earlier, I've got 20 reports from 20 hospitals and each file name contains follows certain specific naming conventions, viz "today'sdate"+"hospital name"+"batch number" and for this report, I've created a model which follows the same naming convention.

                       

                      3. Can the data from multiple models be exported to one Excel file?

                      Yes, using the "append data to existing table" option in the export dialog.

                       

                      --Yes. Olly I'm aware of this function. But, my question was whether we can apply all models at one go and create one excel sheet rather than appending the data every time when we extract data from each report.

                       

                      4. Can this be automated?

                      Yes, either using desktop automation (available in Monarch Pro up until version 11.4, and since then only available through a higher licence fee), or using DataPump / Automator - the server versions of Monarch.

                       

                      --I'm not aware of this and this is a great piece of information. Can you please let me know how we can automate this process through Datapump / Automator. I don't have this product, but i can buy it as long as it serve my purposes.

                       

                      Thanks all.

                       

                      Hope this helps,

                       

                      Olly

                        • Re: Combine multiple templates into one
                          Olly Bond

                          Hello Baghavathy

                           

                          I'm sure that Datawatch sales will reach out to you about Automator.

                           

                          The functionality you look for in point 2 is out of the box in Automator - a process can run which monitors a folder, and using wildcard trapping like Monday*.txt can find any file matching that, and run a project which applies the Monday model. Another process can look for Tuesday*.txt and so on.

                           

                          Where the files can't be distinguished by filename, there are options - using a process to triage the incoming reports and to route them to subfolders which are monitored by appropriate processes. Steve Caiels from Datawatch's services team built a solution like this for another client a while back.

                           

                          You can do automation on the desktop using batch files or COM, but the Automator product does back it easy - there's no programming required, and you get built in verification, scheduling and distribution, so the output data can be emailed or copied wherever needed.

                           

                          Best wishes,

                           

                          Olly

                            • Re: Combine multiple templates into one
                              baghu_m _

                              Thanks Olly.

                               

                              I appreciate your support.

                               

                              Regards

                              baghavatby

                              • Re: Combine multiple templates into one
                                albert harari

                                Olly,

                                 

                                i have automator v12.2 and monarch 12.4.3.

                                 

                                i have two separate projects that run off of two different source files (both csv) and models.

                                 

                                currently they run under two separate monitored processes and produce separate excel exports in two separate emails.

                                 

                                you mentioned in automator these project exports can be combined into one file (or email maybe?)...how do i do that? if i cannot combine into one file, can i get them all to run in one email export?

                                 

                                thanks

                                Albert

                                  • Re: Combine multiple templates into one
                                    Olly Bond

                                    Dear Albert,

                                     

                                    Yes, this is possible - but the monitoring is the tricky bit. Do you want the joint process to run when one input file arrives? That might not work if you need want both files to be there. Does it always happen that one file arrives at say 21h00 and the other at 21h30, or at least that they arrive in the same order? Or do many versions of the file arrive at random times?

                                     

                                    If the files have the same fields in the same order, then you only need one model, then it's easy - one project, with one model, two input files, and a process that's dependent on both input file names being there. In v12, there was a bug in early versions that made it difficult to edit input paths for csv reports, but I think that was fixed by 12.4.3.

                                     

                                    Best wishes,

                                     

                                    Olly

                                      • Re: Combine multiple templates into one
                                        albert harari

                                        Olly,

                                         

                                        thanks for the response.

                                         

                                        Input files are different so two models are required.

                                         

                                        I can switch from monitoring to scheduled so that both files are there and waiting before the process runs. However, how do i get two separate models to export as one?

                                          • Re: Combine multiple templates into one
                                            Olly Bond

                                            Hello Albert,

                                             

                                            Different versions of Automator may behave slightly differently, but the easiest approach would be to append the export of the second project to the Excel file you create in the first project. So project A uses ClassJan.prn and Lesson10.xmod and makes a worksheet called Data in Music.xlsx. Then project B  opens ClassFeb.prn, uses Lesson11.xmod, and exports the data either appending it to the bottom of the Data worksheet, or creating a new worksheet in the same spreadsheet. As long as only project B is defined with an export distribution, then you should get the result you want.

                                             

                                            If you want to send an email with two different XLSX files attached to it, it's going to be slightly trickier - I think you might need to do some scripting and call the pumpapi.dll to control that.

                                             

                                            Hope that helps,

                                             

                                            Olly

                              • Re: Combine multiple templates into one
                                Grant Perkins

                                Good advice provided so far.

                                 

                                One other idea to add.

                                 

                                I have no idea how different your input reports are or will be in the future.

                                 

                                Is this a one-off exercise or something that will be repeated regularly - perhaps with constantly changing formats for the input reports?

                                 

                                It might be worth spending a little time considering whether the inputs reports (or maybe some groups of them) are sufficiently similar that they might be processed with a single model - something that is flexible enough to find and extract the data even if the source report file format varies from file to file. It all depends on what the variations are.

                                 

                                It may not be remotely possible of course .... but in some situations looking at the challenge that way, if it can be achieved, might save more work in the future.

                                 

                                 

                                Grant