13 Replies Latest reply: May 15, 2014 9:56 AM by Lynette _ RSS

    Automated Exporting

    Tim Racht

      We have a system which houses our monthly cold reports and we have created models to extract the information to Access/Excel.  Currently we handle this on a manual basis.  Is there a way to automate the downloading of these reports either to Access and/or Excel.  Below is an example of how some of our reports are set up.  Any help is greatly appreciated.

       

      i.e.

      monthly report 10/31/2004 company 1

      monthly report 10/31/2004 company 2

      monthly report 11/30/2004 company 1

      monthly report 11/30/2004 company 2

        • Automated Exporting
          Grant Perkins

          Tim,

           

          You could create a batch script ( I assume you have a windows process scheduler available) to collect the report(s) and run Monarch then export to wherever you need them.

           

          Or you could go a stage further and develop some code to do the same thing but add in some extra sophistication.

           

           

          Or you could consider using Monarch Data Pump which offers the scheduling and distribution options without specific need to cut any code.

           

          Which approach is best for you will depend on needs, volumes and skills available to you. Oh, and budget of course!

           

          Let us know which sounds the most likely approach for you. I expect that some of the guys and gals who are much more into the automation side of things than I am will be able to provide excellent input for which ever route looks most likely.

           

          Grant

           

           

            Originally posted by Tim Racht:

          We have a system which houses our monthly cold reports and we have created models to extract the information to Access/Excel.  Currently we handle this on a manual basis.  Is there a way to automate the downloading of these reports either to Access and/or Excel.  Below is an example of how some of our reports are set up.  Any help is greatly appreciated.

           

          i.e.

          monthly report 10/31/2004 company 1

          monthly report 10/31/2004 company 2

          monthly report 11/30/2004 company 1

          monthly report 11/30/2004 company 2 /b[/quote]

           

          [size="1"][ December 22, 2004, 02:13 PM: Message edited by: Grant Perkins ][/size]

          • Automated Exporting
            Tim Racht

            At this point I do not know which way is best for us.  However, we would like to automate this as soon as possible.  Would I be able to create a batch script and/or develop some code.  I am not a systems person however I do have some limited knowledge using VB.  Would I create the batch sciprt in Monarch or the apllication it is going to like Excel or Access.  You help is greatly appreciated.

             

            What is Monarch Data Pump how would this help us?  What is the cost of this application and how long would it take to get this up and running?

            • Automated Exporting
              RalphB _

              If you are thinking of doing some VB script for your reports, check out Monarch's Progammers Guide at :[url="http://www.datawatch.com/pdf/products/monarch/Monarch_6_Programmers_Guide.pdf"]Programmers Guide[/url] .

               

              I have limited VB knowledge and was able to write some script from that and from help from the members on the message board.

               

              All our mainframe reports that I use are generated each  night are stored in a different directory each night based on the date and other criteria and I was able to write some code to automatically get the right reports, open models and export out to Excel, Access or text files using Excel or Access VB scripts.  With the help of Microsoft's Task Scheduler, I am able to run these scripts overnight before I or anyone comes in.

               

              Ralph

              • Automated Exporting
                Grant Perkins

                Tim,

                 

                I am certainly no programmer but I can put a script together for basic tasks. Whether s .bat based script would provide the controls you need is another matter but it may do.

                 

                If you are running Monarch 7 and have a recent version of Windows it may be worth having a look.

                 

                XP allows you to run .bat files directly with all dos script commands available. Check the Monarch Help files for Autoscript as well for a guide about the Monarch specific commands.

                 

                Windows Scheduler may provide you with enough scheduling capability to get something running quickly.

                 

                VB very likely to be better but maybe take a little longer.

                 

                And then there is Data Pump ...

                 

                 

                 

                Grant

                • Automated Exporting
                  cardealers _

                  Tim,

                   

                  The automating of models and projects is being asked about more and more.  I am including a batch file which automates projects in V7.  Setting it up can be tricky and took me a few trials, but you could change what I am including..  Here it is then, I'll put some comments after it.

                   

                  -


                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Bo_Hammond.prj" c:pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Chad_Eddlemon.prj" c:pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Cole_Price.prj" c:pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_David_Riker_Area.prj" c:pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Dealer_Services.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Ian_Drake.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Joe_Johnson.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_John_Garay_Area.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Keith_Nettleton.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Ken_Terry.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Lee_Mitchel_Area.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Mark_Cortez_Area.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Mike_Fulton.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Pat_Dose.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Pat_Heinsen_Region.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Rodney_Guthrie.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Steve_Chandler_Area.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Todd_Bailey_Region.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Todd_Patrick.prj" pjrtest.xls /t

                  "h:programmonarch.exe" "g:Monarch Model TemplatesDSRC_Trina_Cornelius.prj" pjrtest.xls /t

                  Echo "Processing Complete"

                   

                  -


                   

                  1.  Each statment as to be on a continuous line.

                  2.  One project statement per line.

                  3.  Tell where monarch.exe is, it can be local or on a network.

                  4.  Tell where your projects are, they can be local or on a network.

                   

                  This batch file adds a new sheet to an Excel work book for each project executed.

                   

                  There are other options (the /t at the end of each line) but I don't have my book front of me.

                   

                  Hope this helps

                   

                  Paul

                  • Automated Exporting
                    JC _

                    details: Monarch V5 on Windows2000

                    I'm a pretty experienced Monarch user but a neophyte with batch, scripting, etc. Based on my reading here in these threads, I've tried my first batch routine. Tentative success.

                     

                    backfill: reports in .txt format. I have models which trap data, and I export the table as an Excel spreadsheet, then import into Ms-Access and create finished reports each month. Same .txt formats for different clients each month. What I'd like to do is write a routine to pull one up, invoke the model, export it out, close, pull up the next one, repeat.

                     

                    I've been able to write a .bat and run it through Autoscript which fires off Monarch, gets the first report, model, and exports, but then I can't get it to go to the next one. I thought it was simply a matter of typing a seperate line for the 2nd report, but I get an error saying "cannot find". What am i doing wrong?

                    • Automated Exporting
                      Steve_P _

                      I also am not a "programmer" by title, but I've been enamored with Monarch long enough to think I might be able to help.

                       

                      There is a command that I use in my batch file: Call

                      You use this command to 'call' up multiple batch files, so even if a 'called' batch stops or fails, the original batch file moves on to the next 'call'.  Check this out:

                      Let's say you set up a file, model1.bat that opens up your first text file, model file, and exports your modified report.

                      (you will probably have to change the file names/paths)

                      [font="courier"]"C:Program FilesMonarchprogrammonarch.exe" "C: eportname.txt" "C:Program FilesMonarchModelsModelname.mod" c:OutputFile.xls[/font][/quote]Then you set up a second file, model2.bat that does the same thing for your next, you can do this for as many files as you have (I don't know if there is a limit, but I haven't hit it yet!). 

                       

                      Create a third .bat file with the following code:

                      [font="courier"]call c:model1.bat

                      call c:model2.bat

                      "C:Program FilesMicrosoft Office 97OfficeMSACCESS.EXE" "c:MyDatabase.mdb" /x macroname[/font][/quote]This code will run your two (or however many) models, then open up Access, and open your database.

                      The "/x macroname" will fire off a macro once Access opens (of course, switch "macroname" to the name of your macro!).  All you need to do is set up a macro in Access to import the data (the 'transfertext' or 'transferspreadsheet' functions work good).

                       

                      Easy as that, huh?!    [img]smile.gif[/img]  I know this isn't an Access forum, so if you need help setting up that macro, feel free to send an email (spage@emh.org) - I can try to help!

                       

                      I hope this code is correct!  If not, I trust some helpful on-looker will correct it.

                      • Automated Exporting
                        cardealers _

                        JC,

                         

                        Post your code so I can look at it.  It might be as simple as not being in the folder you think your in once your first batch file finishes.  I might also suggest as I showed in my previous post putting many  lines in one batch file.

                        • Automated Exporting
                          JC _

                          here's what i have. I can get the first report to import, apply the model, go to Table view, and export. But then it stops.

                           

                          Monarch.exe filesmonarch eportspacn66m filesmonarchmodels\_arcptpac.mod /T c:Program FilesMonarchExportpac_mo_cpt.xls

                          filesmonarcy eportspacn66f filesmonarchmodels\_arcptpac.mod /T c:Program FilesMonarchExportpac_yr_cpt.xls

                          • Automated Exporting
                            Steve_P _

                            Try adding the "Monarch.exe" part to your second line (and don't misspell monarch     ).  Also, something about batch rules is that you should put quotes around file paths if you use spaces (like in your export file, you have: ...Program Files... I don't know how much this matters, or if it is just some anal code thing?).

                            Try this:

                            [font="courier"]Monarch.exe filesmonarch eportspacn66m filesmonarchmodels\_arcptpac.mod /T “c:Program FilesMonarchExportpac_mo_cpt.xls”

                            Monarch.exe filesmonarch eportspacn66f filesmonarchmodels\_arcptpac.mod /T “c:Program FilesMonarchExportpac_yr_cpt.xls”

                              /font[/quote]

                            • Automated Exporting
                              Tim Racht

                              I have reviewed the programmers guide however I am still having problems automating some reports.  I am trying some of the examples you gave and replacing our information however the VB debugs an error.  If I can get one to run I should be on my way.

                              Would you put the script/vb code in the scheduled tasks to run?

                              Any more help would be greatly appreciated.

                              Tim

                              • Automated Exporting
                                gorders _

                                Hi.  I have been using batch files to automate Monarch for a few years now.  I run Windows 2000 and just upgraded to Monarch v8.0 Pro from v7 Pro.

                                 

                                In order to run multiple exports in a batch file enter the lines like this...

                                 

                                [font="courier"]"C:Program FilesMonarchProgramMonarch.exe"   /rpt:”C:Program FilesMonarchReportsReport_1.txt” /mod:”C:Program FilesMonarchModels eport_model_1.mod” /exp:”C:Program FilesMonarchExportReport1.xls" /t

                                 

                                "C:Program FilesMonarchProgramMonarch.exe"   /rpt:”C:Program FilesMonarchReportsReport_2.txt” /mod:”C:Program FilesMonarchModels eport_model_2.mod” /exp:”C:Program FilesMonarchExportReport2.xls" /t

                                 

                                 

                                "C:Program FilesMonarchProgramMonarch.exe"   /rpt:”C:Program FilesMonarchReportsReport_3.txt” /mod:”C:Program FilesMonarchModels eport_model_3.mod” /exp:”C:Program FilesMonarchExportReport3.xls" /t

                                /font[/quote]More information on this can be found in Monarch help under "Using Monarch command lines."

                                 

                                Assumptions that you may need to change in the code...

                                -


                                • If the monarch executable is not installed here, "C:Program FilesMonarchProgramMonarch.exe", change the path to where you installed it.

                                • The reports you are reading into Monarch are located here... “C:Program FilesMonarchReports”

                                • The reports are consistently named... Report_1.txt, Report_2.txt and Report_3.txt every month. If not let me know and I can help you use batch commands to accomplish this by copying the original files to temporary files that are consistently named.

                                • The models are on your computer in this folder... “C:Program FilesMonarchModels”

                                • You have a different model for each report named... report_model_1.mod, report_model_2.mod and report_model_3.mod

                                • You want the Table data (/t) for each report to be exported to a new Excel spreadsheet named Report1.xls, Report2.xls and Report3.xls

                                 

                                I always put quotes around file paths because it is required if there are spaces in it anywhere.  If there aren't spaces then it still works correctly anyways.

                                 

                                You might want to look into creating Monarch Projects for each report if you have a recent version of Monarch Pro. I am not sure in the standard Monarch has project capabilities.

                                 

                                I have automated many of our reporting needs which includes e-mailing daily/monthly audit reports to myself and others.  So, please let me know if you have any further questions about Monarch and batch files.

                                 

                                Thanks,

                                Scott L. Gorder

                                EDI & IT Specialist

                                Harvest House Publishers

                                 

                                [size="1"][ April 14, 2005, 12:53 PM: Message edited by: gorders ][/size]

                                • Automated Exporting
                                  Lynette _

                                  Hi,

                                   

                                  Is there any way the script can be written to select all files beginning with "report" (using a wild card like "report*") so that one does not have to define each line based on the file name i.e. report_1/2/3 etc..