5 Replies Latest reply: Apr 11, 2016 10:02 AM by Olly Bond RSS

    Add new or Update existing records in Access table when exporting

    Fred Pinto

      I'm using Monarch to extract retail selling from various sources (excel, PDF and csv files) to an Access database.  I'm using a runtime parameter to enter the date of the selling import and then creating a project to batch all the exports.  If I run it more than once it appends to the table instead of updating existing records. Does anyone know the proper way to set this up?

       

      I'm currently running Monarch 10.5 but I am in the process of upgrading to the latest version.

       

      Thanks for your help.

        • Re: Add new or Update existing records in Access table when exporting
          Olly Bond

          Hello Fred,

           

          Monarch won't update records in a table - export options are just append or overwrite.

           

          But you can handle this using two projects - one to append the updated records, then another to open the Access table and filter or summarise the data to just show the latest record by account.

           

          As you mentioned batch files, a word of warning. Batch automation was removed from the standard licence in 11.5, and is being discontinued in one month's time. Datawatch would appear to be encouraging us to deploy the desktop installation of their server product, Automator, as a replacement.

           

          Best wishes,

           

          Olly

            • Re: Add new or Update existing records in Access table when exporting
              Fred Pinto

              Hi Olly and thank you. 

               

              I am using what you suggested to remove the duplicates and I think it's going to work out.


              In regards to the batching, since I am still currently using 10.5, what do you think is the best way to batch the exports and output all the data to one database?  I thought I was going to be able to use project exports but since I am grabbing data form multiple documents and using different models I can't run them as multiple exports in one project.  Is the correct way to write batch files that will run one at a time and in the correct order to extract the data from each file?  Is there documentation anywhere that can help me to write the batch files?

               

              Best,

               

              Fred

                • Re: Add new or Update existing records in Access table when exporting
                  Olly Bond

                  Hello Fred,

                   

                  The most helpful resource for Monarch automation was www.******************** - the site created by the late Sandy Cavalaris, who posted here as DataKruncher. Sadly, it's now offline.

                   

                  I'm no programmer, and in my day job am lucky enough to have access to DataPump / Automator, but the syntax for batch files is essentially:

                   

                  C:\Program Files\Monarch\monarch.exe /project:project1.xprj /pxall

                  C:\Program Files\Monarch\monarch.exe /project:project2.xprj /pxall

                   

                  This will open project1 and perform all the project exports defined, then project2 and so on. More granular options are available to call a report, a model and perform a specific export to table or summary.

                   

                  There's a programmers guide for Monarch which shipped, I think, with the CD, and details the complete syntax and options.

                   

                  Best wishes,

                   

                  Olly