4 Replies Latest reply: May 15, 2014 10:09 AM by warlok RSS

    recycling excel spreadsheet

    warlok

      Hi All,

       

      I have another poser for everyone. This one though, I suspect I figure I'll be stuck trying to do the long hard way.

       

      I have a daily report that's uploaded daily. It's taken and run through Monarch which appends the information into an Excel spreadsheet. The spreadsheet is used as an external lookup to verify information in another report. Any descrepencies need to be checked out. The issue is the information on the one report may show up a few days behind the other one. This is why the Excel spreadshet is appened to and not overwritten.

       

      This works all very well and good, But after a shortime, the excel spreadsheewill be very large, Additionally, the information within it will be no longer needed and invalid after a certain amount of time. What I'm looking to do is to have the data checked again in the spreadsheet and be removed after a predetermined amount of time.

       

      I'd like to include it in the same batch file that runs the reports. I'm guessing it can be done alone or on it's own. (If it's on it's ownI'm sure the MonarchBatchfile could call and run the secondary batch file if needed)

       

      I'm a little unsure on how to attempt this. I'm pretty sure there is no ability for Monarch's batch processing to do this and I suspect it's outside the realm of Monarch really, but I figured I'd ask and see what the expert's say.

        • recycling excel spreadsheet
          Grant Perkins

          Based on your outline I assume you can clearly define the criteria for the historic records to be retained in the previously processed spreadsheet.

           

          In which case a couple of approaches suggested themselves to me.

           

          If there is a clear time element to this you could keep all the daily reports and simply process all of them as far back as you need to go for each daily run.

           

          If you need time plus something else the report date (or the date being reported) offers (perhaps) time and the 'something else' can be a filter to reduce the total number of records.

           

          Alternatively you could, as part of the batch file, prune the Excel File. I'm sure there would be a way to do this with Excel and or VB but you could also do it with Monarch and write back (with Overwrite) the new version and then append to that using your current process.

           

          Does this offer you any useful ideas?

           

           

          Grant

            • recycling excel spreadsheet
              warlok

              Thanks for the reply Grant. I was already leaning towards an automated way of pruning the spreadsheet once the main batch file was run. My criteria hasn't yet been defined, however I'm trying to have it commit over a period of time. i.e. any information older the X days gets dropped from the spreadsheet.

               

              To do the daily work the user will already manually be combining several new reports ( I beleive about 25 of them) into one input file, to do this and sort through them on a daily basis if this was to last a week would end up manually combining 175 files. (Which is a little much to do) An automated drp off would be ideal.

               

              Bu your suggestion have given me something to think about. I've not tried to read inan excel spreadsheet as an input to the process to prune the days off. Something I might be able to jury rig and make work.

               

              Thanks for the input. It's helped a lot.

                • recycling excel spreadsheet
                  Grant Perkins

                  Your pruning could be x-days AND some other criteria if it would help. However I like to keep things simple and unless there is any major advantage for the size of the Excel sheet or the use of it by introducing secondary pruning criteria I would tend to leave it as a date pruning activity.

                   

                  If all the files the are the same format (or can be processed to the same format) it should be possible to automatically create the single input file - especially if all of the files were resident in the same directory. Obviously there could be a naming issue there but that too should be resolvable in a way that allows Monarch, via the batch file, to do most of the work.

                   

                  My batch file involvement is somewhat irregular, to say the least, but I'm sure some of the other regulars here have much more input to offer. I seem to recall that there should be a few posts covering the matter of processing multiple files using a batch file in earlier forum posts. I'll see if I can find them.

                   

                   

                  Grant

                   

                  Here's a reference that seems to relate:

                   

                  http://www.monarchforums.com/showthread.php?3288-Open-multiple-files-in-batch-using-a-wild-card[/URL]