8 Replies Latest reply: May 15, 2014 9:55 AM by Data Kruncher RSS

    7.02: When I export table data can I group it and save as new files?

    Maxer _

      Ok I have a text report that Monarch does a great job of reading an displaying in table view for me.

       

      Then I save / export it as an Excel file.

       

      Now in this excel file I have a column called "Seller"

       

      I could have 15 sellers and 10,000 records in the entire spreadsheet.

       

      Normally I filter in Excel by each seller, and then save just those results in a new Excel sheet.

       

      Can I get Monarch to do this for me?

       

      Can it filter by every seller and then for each group of records with that seller save it as a seperate file?

       

      So if I had 15 sellers I would like Monarch to export: Results_Seller1.xls, Results_Seller2.xls... Results_Seller15.xls

       

      However, the catch is I do not know what the value of Seller will be in advance, so I can't program it to look for a predetermined name, it would have to sort by Seller and then every time the Seller name changes consider that a new group.

       

      (Hopefully my post made sense)

       

      Thanks for the help!

        • 7.02: When I export table data can I group it and save as new files?
          Grant Perkins

          Maxer,

           

          If you knew the names/identities of the sellers you could set them up as filters and then export summaries using all filters.

           

          But I think your last paragraph probably indicates that such an idea will not be possible.

           

          Unless you can pre-process and obtain a list of sellers and then use that to define the filters on the fly using an itereative automation script perhaps.

           

          Or filter for seller1 to seller15 and then assign the real identity values for each generic seller for the particular analaysis. Mush the same idea really but it might be possible to use a slightly different approach.

           

          Whichever way the above looks like at least a 2 stage process.

           

          HTH.

           

           

          Grant

           

          [size="1"][ September 01, 2006, 05:03 AM: Message edited by: Grant Perkins ][/size]

          • 7.02: When I export table data can I group it and save as new files?
            Data Kruncher

            Maxer,

             

            Further to Grant's good advice...

             

            The solution to this problem becomes almost easy (OK, easier to describe than actually build       ) if you create a runtime parameter calculated field in your model. Call it Query_Seller.

             

            Now build a filter in the model named Sellers whose formula is Seller=Query_Seller. Make this the active filter.

             

            Now add a summary for Seller that gives a record count by seller. You won't need the record count for anything, but you've got to summarize something, and that's good as anything. This will give you a list of unique names that you can export to Excel for further processing. Be sure that you define the summary to not use a filters so that you get all the seller names.

             

            Once that's done, I would create an Excel file that, at a click of a button:

            • starts Monarch

            • opens a project file to get your report and model

            • exports the summary to an xls file

            • closes all documents in Monarch

            • opens the summary xls file

            • has code that loops through all the sellers listed in column A and for each seller:

                

            • opens the project file, supplying a runtime parameter with the value of the seller name

                

            • exports the table to "Results_SellerName.xls" where SellerName is the actual name of the seller, like Smith or Johnson.

                

            • closes all documents in Monarch

             

            By searching this forum, you'll find plenty of code examples to help you get this done.

             

            The only other things you might need to learn about are how to loop through cells in Excel lists and how to stop when it's time to stop.

             

            What you've described CAN be done, just not all within Monarch with just a few mouse clicks.

             

            BUT, once you've built this little system, you can reuse it over and over and over....

             

            Have fun!

             

            Edit: Of course, after I post this message I notice the 7.02, meaning that you can't use runtime parameters.   :rolleyes:     redface.gif[/img] 

             

            Is upgrading a possibility?

             

            Kruncher

            • 7.02: When I export table data can I group it and save as new files?
              Nick Osdale-Popa

              Also to note (granted, I'm using 8.01 Pro): In the summary, all I provided was a key field, and no measures and was able to produce a list of unique items. Might give that a try in 7.02, that way you're not dealing with an extra column.

               

              -


               

              Again this only works for the Pro version: In version 7, the way I was able to adjust a filter was to have my VBA routine write to a text file the value I wanted to filter. The model then used a database connection to that text file and the filter was set to the value from the connection. It's easier to set up than to explain.     And with a little DOS magic you can have the unique list of values exported to a text file, then have the batch file read those values, write to the text file for the data connection and run Monarch again with the filters. It's more combination of Monarch Voodoo and Batch File Voodoo. 

              • 7.02: When I export table data can I group it and save as new files?
                Maxer _

                Well that's fine, I can make Access do all the work for me, not a big deal.

                 

                However, currently I have a batch file that just opens Monarch and passes it the report name, the model name, and the output filename and path.

                 

                Then it loops through all 20 or so of my reports.

                 

                However, if I do this in Access then I'm guessing it would make more sense to have access Open Monarch, do it's thing, then once Monarch finishes have Access grab the results and work its magic on them.

                 

                (Sadly upgrading is not an option, getting this version was nearly an act of divine intervention)

                • 7.02: When I export table data can I group it and save as new files?
                  RalphB _

                  I'm like Data Kruncher and Nick, I do my scripting in VB. 

                   

                  But if I'm not mistaken, weren't you able to pass runtime parameters thru to Monarch using the command line in V7?

                  • 7.02: When I export table data can I group it and save as new files?
                    Data Kruncher

                    But if I'm not mistaken, weren't you able to pass runtime parameters thru to Monarch using the command line in V7?

                      /quoteI just didn't think about a command line option. I was looking at the table at the back of the V8 Programmers Manual that indicates that you can only set runtime parameters with V8. We need to make up a similar table similar to this that reflects available command line options in the different versions. Anybody up with access to a  bunch of versions up to it?    

                     

                    Kruncher

                    • 7.02: When I export table data can I group it and save as new files?
                      Nick Osdale-Popa

                      Originally posted by Data Kruncher:

                      We need to make up a similar table similar to this that reflects available command line options in the different versions. Anybody up with access to a  bunch of versions up to it?      

                       

                      Kruncher /b[/quote]I have Windows Versions 1.0 to present. How far back do you want to go?   tongue.gif[/img]   

                      • 7.02: When I export table data can I group it and save as new files?
                        Data Kruncher

                        Can you create a table that mirrors the structure of Appendix B in the v8 programmer's manual? Maybe distribute (by PM requests) as a PDF if you'd be so kind?               :cool:  

                         

                        Did I mention that you're looking a little leaner lately, Marine?          :rolleyes: