7 Replies Latest reply: May 15, 2014 10:08 AM by pistol654 _ RSS

    Exporting Data Help

    pistol654 _

      Hello,

       

      I have data that I must export to an excel document every day.  I get the data from a report that is produced daily (and the report name changes every day to reflect the day's date).  I would like to create a way (project export maybe?) that I can simplify the export process.  Every day I have to go through all the steps to export and append data to an existing table in an excel file, and it seems like there must be a faster way to do this.  I have tried creating a project file but it does not work because the data source changes every day.  Any ideas or suggestions?

       

      Thanks!

        • Exporting Data Help
          Data Kruncher

          Hello and welcome to the Monarch forum.

           

          The name of the report file changes daily. What is the naming convention of the file name? Is it like "MyFile 07 Jul 2010.txt"?

           

          Is the date in the file name equal to the date that you're running the process, or is it the day prior to your run date, or worse, the last business day prior to the run date?

           

          And does the name of your Excel export need to change every day too?

           

          In the end, all of these approaches and needs can be handled with at short script or batch file, though a "last business day" requirement really throws a wrench in things usually.

           

          I'm confident that we'll be able to assist you with this so that you'll have a nice smooth Monarch process to run every day.

            • Exporting Data Help
              Olly Bond

              Hello Pistol,

               

              You could run a batch file (copy myfile.txt input.txt, delete myfile.txt, then call a Monarch project which has input.txt as its source data.)

               

              But then if you want to filter data according to the report date, you're not going to be able to use functions on File() to retrieve the date.

               

              Does the required date exist in the report file, or can you derive it from Today()?

               

              Best wishes,

               

              Olly

                • Exporting Data Help
                  pistol654 _

                  Thanks for the replies guys!

                   

                  The document name that I am pulling the data from does indeed change, and the change indicates the change in date that the report's data reflects. 

                   

                  Additionally, the data comes in a .prf form so I can't open multiple reports (this would actually save me a lot of time if it was possible).

                   

                  The Excel report that I am exporting to does not change.  It is the same report that I am simply appending data to an existing table. 

                   

                  An example of something I am trying to do is this:

                   

                  I am trying to aggregate a month's worth of data where each day of data is stored in a separate .prf report (b/c there is a lot of other data that is downloaded to us by our processor as well in these reports).  While I understand that I will need to open each .prf file individually and then open the relevant model, I am hoping to simplify the next part by being able to go to "Export Project", select my project, and then all my filtered data in the monarch table is automatically appended to the Excel table (almost like repeating the steps that someone would use a Macro in Excel for).

                   

                  I hope that makes sense.  I am currently interning at this company for the summer, and while I have been using Monarch for about 2 years now, I want to simplify some of the steps not just for myself, but for the company once my internship is over.

                   

                  Any more help you guys can provide would be MUCH appreciated. 

                   

                  Thanks,

                   

                  Pistol

                    • Exporting Data Help
                      Olly Bond

                      Hello Pistol,

                       

                      I've never come across this one before! PRF is essentially a zip format that compresses the report and optionally embeds the relevant model file.

                       

                      You may get some joy by renaming them all to .zip and using a command line utility to unzip the files to the original text report files.

                       

                      You could then use Monarch to open multiple reports together and use the File() and Report() functions to help get what you need.

                       

                      What system is it that delivers you PRFs, BTW?

                       

                      Best wishes,

                       

                      Olly

                        • Exporting Data Help
                          pistol654 _

                          Olly,

                           

                          Thanks for the advice.  I don't want to give out too many details, as the information is sensitive, but the PRF reports we are receiving are from a 3rd party processor that must send as 30-40+ reports a day, so they are compressed as prf files both for ease of transmission as well as storage.

                           

                          I do not understand how to rename the files as .zip files. I am assuming I cannot simply rename it as a .zip.  How does one do that?

                           

                          Thanks,

                           

                          Mike