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

    Batch File question

    warlok

      I've created a multi Monarch step process that is generated from one Batch file. The middle step, reaqds in the information from a single table excel file (2003 format). Monarch does some work and filters out not needed information from the database. The last step is for me to save over the excel spreadsheet I read in as input. This is where the problem begins. If I change the output report file to be some other named excel spreadsheet it works correctly and generates the output. If I name it the same as the input database it runs with no errors showing, but no change is made to the spreadsheet. I'm guessing that this is not allowed for some reason. Can anyone confrim this for me?

        • Batch File question
          warlok

          I should add the sample line of code I'm trying to use:

           

          "C:\Program Files\Monarch\Program\Monarch.exe" /datasource:"C:\Monarch\CD355-BB-A\CD031.xls" /table:"Data" /prj:"C:\Monarch\CD355-BB-A\CD031\CD031B.xprj" /mod:"C:\Monarch\CD355-BB-A\CD031\CD031B.xmod"  /R  /exp:"C:\Monarch\CD355-BB-A\CD031.XLS" /expfileopt:overwrite

           

          /code

            • Batch File question
              Bill Watson

              you are trying to amend/overwrite the very same records you are using to create the data that will overwrite it? - this seems circular in nature O.o (abandon all hope ye who enter here)

               

              I certainly have projects that use lookups in the same file that the exports write back to (data written to different sheets than the lookups) and they work fine, but I don't think you could write to your actual datasource as it is locked/in use. You similarly you get a warning from monarch if you have an excel datasource/external lookup file open in excel, and try and run your project. Excel (application) has the file locked and monarch can't access it.

               

              Trying to think of a way around this..

               

              in the excel file create a seperate worksheet which contains either a hard copy of the original data or a formula driven (=Originalsheet!A1) pull of the data. Use this sheet as the source for your monarch model. Then you "should" be able two write your output back to "Original" sheet. Make sense?

                • Batch File question
                  warlok

                  Yeah that's the true problem. I'm trying to get Monarch to drop off items from the spreadsheet after a predetermined amount of time. because the spreadsheet is used as an input to another Monarch project this would effectlvely automate the process enough that I can let it go. The alternative is to have the user go into the spreadshet and manually do this step. Which can be a bit of a bother when we're talking several hundred records that need to be purged from the spreadsheet on a daily basis.

                   

                  I was hoping that Monarch would be easily able to do this. It has the filter capability to drop the records. it can read in the determined number of days to drop the records through an external lookup. The only thing holding it back is allowing the file to re-write to itself.

                   

                  I'll giveit a shot and see what happens anyway. It seems like a very simple thing to do. Short of that I guess I will need to find a way to change the batch file to 1) write the copy of the file 2) rename or delete the original file 3) rename copy of file to original name.

                    • Batch File question
                      Grant Perkins

                      Short of that I guess I will need to find a way to change the batch file to 1) write the copy of the file 2) rename or delete the original file 3) rename copy of file to original name.[/QUOTE]

                       

                      Surely this would offer a safer option in case anything goes wrong in the process and your original xl file is trashed?

                       

                      You can add a command line to the batch file to perform the copy over as a final step. It may be worth creating a backup copy of the existing xl file before starting as well and deleting that too after successful completion.

                       

                       

                      Grant

                        • Batch File question
                          warlok

                          Thanks Grant, I'm beginning to think it might be the better way.

                           

                          I always keep backups of what I'm working on at least while I'm testing. This is the first time I'm trying to acocomplish something like this. If I can get this to work I'm a step closer in being ale to do and fix several other projects in the future here I'm sure.