10 Replies Latest reply: May 15, 2014 9:54 AM by Darren _ RSS

    Joining two test files and the OpenDatabase Method

    TonyD _

      I have found the only way of joining two text files is to separately export them first as they are both defined by separate models and then to opendatabase and create a join on them which works fine but is there a way of automating this process.

       

      I have found using the OpenDatabase method in VB will allow you to open an external database file such as a mdb (although I have not managed to do this yet). So although you probably can’t perform a join through the above method I can at least perform the join in Excel and then open it in Monarch.

       

      The next problem with this is for some reason in the OpenDatabase method you have to supply the Model? Why? You don’t have to when you manually do it in Monarch.

      So my newly joined table in Access can’t be opened because we don’t have a model!

       

      With the above can anyone detail a way of doing this or supply any info on the Opendatabase method as i am struggling to get it to work.

       

      Many Thanks

      Tony

        • Joining two test files and the OpenDatabase Method
          Darren _

          Tony,

           

          In the first part of your question you ask about automating the process of joining models...Well I have done quite a lot of work on this process - as "demo-ed" at the User Conference in May. The process of linking the multiple models/files together can be easily automated with Monarch Data Pump! You create your separate models, and create the link with the join function. Once you have the foundation down and correct, let the Data Pump take over. It just automates that exact process of finding the text files, applying the models, creating the join and exporting the file to something like MS Word or Excel. I can also create multiple joins for files, linking one file to many other separate ones - as long as you have a singular, key field...Hope this helps on the first part of your question...

           

           

          Originally posted by TonyD:

          I have found the only way of joining two text files is to separately export them first as they are both defined by separate models and then to opendatabase and create a join on them which works fine but is there a way of automating this process.

           

          I have found using the OpenDatabase method in VB will allow you to open an external database file such as a mdb (although I have not managed to do this yet). So although you probably can’t perform a join through the above method I can at least perform the join in Excel and then open it in Monarch.

           

          The next problem with this is for some reason in the OpenDatabase method you have to supply the Model? Why? You don’t have to when you manually do it in Monarch.

          So my newly joined table in Access can’t be opened because we don’t have a model!

           

          With the above can anyone detail a way of doing this or supply any info on the Opendatabase method as i am struggling to get it to work.

           

          Many Thanks

          Tony /b[/quote]

          • Joining two test files and the OpenDatabase Method
            TonyD _

            Thanks Darren for your response.

            I will read up on the Data Pump Program.

             

            Tony

            • Joining two test files and the OpenDatabase Method
              Grant Perkins

              Tony,

               

              Not being a coder I kept back from responding to this question since automation and the OpenDatabase method are not things I have been getting into so far.

               

              However I have played a lot with databases and text files as databases and the linking of them - several in fact using V7 - since the early days of availability.

               

              I presume you have been able to link the files successfully using a manual approach? If you then save the model created as part of that and also save the project, you should have a re-usable process for a future run. The model will contain the definition of the external file(s) as you have defined them for the import and link.

               

              Once the model and project are in place, opening the project will proceed to pick up the specified files from the specified location(s) and build the table defined by the model. I assume that the appropriate method for automation will (or should) achieve the same result. In either case a saved model is the only way I can think of to avoid having to redefine the data structure of the file(s) each time you open the database afresh.

               

              I'm not sure if there is anything in this that you have not already covered for yourself. And of course as I don't code I can't camment on any possiblity of a problem with the methods available. However it did seem worth mentioning just in case it gave you any new ideas to investigate.

               

              Good luck.

               

              Grant

               

              Originally posted by TonyD:

              I have found the only way of joining two text files is to separately export them first as they are both defined by separate models and then to opendatabase and create a join on them which works fine but is there a way of automating this process.

               

              I have found using the OpenDatabase method in VB will allow you to open an external database file such as a mdb (although I have not managed to do this yet). So although you probably can’t perform a join through the above method I can at least perform the join in Excel and then open it in Monarch.

               

              The next problem with this is for some reason in the OpenDatabase method you have to supply the Model? Why? You don’t have to when you manually do it in Monarch.

              So my newly joined table in Access can’t be opened because we don’t have a model!

               

              With the above can anyone detail a way of doing this or supply any info on the Opendatabase method as i am struggling to get it to work.

               

              Many Thanks

              Tony /b[/quote]

              • Joining two test files and the OpenDatabase Method
                TonyD _

                Hi

                 

                Maybe i am getting over complicated with Monarch but i thought the only way i could link two reports which were defined by two seperate models.

                e.g

                 

                Report1

                 

                Customer ID, Address1, Address2, Address3

                 

                Report2

                 

                Customer ID, PostCode, City, Country

                 

                was to export both as mdb files join them in Access on the Customer ID and to open the Access mdb database back up in Monarch.

                 

                This is due to the fact you can only have one model open at one time unless there is another way with Monarch you can join the report files together before you export.

                 

                I am using V6 so i am not sure if V7 has some improvements on this.

                 

                Thanks for your reply

                Tony

                • Joining two test files and the OpenDatabase Method
                  Grant Perkins

                  Tony,

                   

                  If your text files are in .csv or similar format you can read them as if they were a database - as you can read them into Access or Excel also.

                   

                  If they are report format and you need to convert them to a 'database' format using 2 separate models then yes, you're quite right. Run the process, load the report, load the model, export the output in the format of your choosing (for each report) and then create a new Monarch process which loads one or other of the reports, defines it field by field as required, links to the second 'database', defines the link and completese the combined load. I assume there is at least one unique field in each 'database' that can be used to make the join.

                   

                  Save that model. Save the whole thing as a project. You will now be able to open the project directly and it should seek both files at the location you were using, and re-instate the whole thing to where you were. If you update the files it will still work of the names have not been changed.

                   

                  I assume (see previous comments re not being a programmer) that this can be automated givien that permissions and so on are OK within the OS.

                   

                  V6 is restricted to joining 2 files. If you need more you would have to produce your first combination, save that as a new database then use another session to join that to the next file and so on.

                   

                  V7 allows up to 9 joins in a single session. But you would still need to convert a report to a database first.

                   

                  Each stage of the process should be capable of automation but you will need to have a model and project available to use for combining the 2 database files created in the first stages. In other words the whole thing needs to be set up manual first time through to create the final model and project. After that it should be possible to get VB to do it for you.

                   

                  If you want to make the join in Access then you would need to use that access definition to read into Monarch as a single entity (presumably a query?). Alternatively if you created 2 separate mdb's (or other form of output or combination of outputs) you can make the connection using Monarch. Either way you need a model to define the structure of the database as Monarch is to interpret it. Unless anyone else knows another way.

                   

                  Hope this helps to clarify a little.

                   

                  Grant

                   

                   

                  Originally posted by TonyD:

                  Hi

                   

                  Maybe i am getting over complicated with Monarch but i thought the only way i could link two reports which were defined by two seperate models.

                  e.g

                   

                  Report1

                   

                  Customer ID, Address1, Address2, Address3

                   

                  Report2

                   

                  Customer ID, PostCode, City, Country

                   

                  was to export both as mdb files join them in Access on the Customer ID and to open the Access mdb database back up in Monarch.

                   

                  This is due to the fact you can only have one model open at one time unless there is another way with Monarch you can join the report files together before you export.

                   

                  I am using V6 so i am not sure if V7 has some improvements on this.

                   

                  Thanks for your reply

                  Tony /b[/quote]

                  • Joining two test files and the OpenDatabase Method
                    Darren _

                    Tony,

                     

                    Here is what I have done to link in Monarch. I will try to use your data source in my example...

                     

                    I have 3 files that I want to link in Monarch. At the time, I was using Monarch V6 Pro. My data source is 3 ".txt" files from my ERP system. But any file source is OK here. Now, Grant did say that V6 is restricted to link 2 files. But here is my way around it...

                     

                    I create a model for my first data file.  In my report, using the model I end up with:

                     

                    Customer ID, Address1, Address2, Address3.

                     

                    Next I create a second model using my second data file. Noting that at least one field in my data source here is the same as the first data source, and is unique (key field = Customer ID). I end up with:

                     

                    Customer ID, PostCode, City, Country.

                     

                    I then link the two sets of data together and output the result to Excel. I now have:

                     

                    Customer ID, Address1, Address2, Address3, PostCode, City, Country.

                     

                    I then turn around and produce a third report of data (keeping in mind that my one requirement is a field that contains the key, unique item - Customer ID). I then create the model for the third data set...

                     

                    Customer ID, Notes, Comment1, Comment2

                     

                    And then I join it to the above, already 'pre-joined' data set, resulting in...

                     

                    Customer ID, Address1, Address2, Address3, PostCode, City, Country, Notes, Comment1, Comment2.

                     

                    I then output the final data set to Excel. This is how I get around the "only 2 joins" allowed in V6. This process is then automated using Monarch Data Pump. Hope this helps out...  smile.gif[/img] 

                     

                    Darren.

                    • Joining two test files and the OpenDatabase Method
                      TonyD _

                      Hi Grant / Darren

                       

                      Again thanks for your replies.

                       

                      Grant i think you are right you probably can automate the process of reimporting the databases through previously defining a model for the joined databases in Monarch. I am yet to try this but will give this a go. It would be nice if there were more examples or further information on this process. I obviously already have the programmers guide pdf downloaded from the website but have found the opendatabase method in there to not provide a proper worked example, which would be handy.

                       

                      Also Grant you said in your reply,

                       

                      "If you want to make the join in Access then you would need to use that access definition to read into Monarch as a single entity (presumably a query?)"

                       

                      Does this mean i can bring in the Access definition into Monarch (i.e like using it as the model file)if so how do you bring this in with the mdb file?

                       

                      Darren, when you were joining each dataset i take it you had to export each individual report first and then either joined them in Access / Excel and reimport them into Monarch or where you able to some how able to join each report prior to the export to Excel?

                       

                      Thanks

                      Tony

                      • Joining two test files and the OpenDatabase Method
                        Grant Perkins

                        Tony,

                         

                        I can't remember if you advised this in an earlier post but I know you are using V6 and I assume it the the Professional version if you are looking at database functionality.

                         

                        If you manually use the File>>Open Database and choose a database to connect to and that database is an Access or Excel file (or presumably any of the others that are supported) you get the opportunity to pick which table or query (definition) you want to use. Thus if you already have a subset of data or perhaps a query that combines data from different tables or whatever, it should be possible to make use of that without having to re-invent the wheel again using the full data set in Monarch. (My main recent experience is to use Monarch to combine some files to make an Access data set, but I see no reason why it should not work the other way as well!!!)

                         

                        As I mentioned before I am note a programmer so have not looked at the options for automation but I would be quite surprised there was no way to automate what can be done manually for this particular step of the task. However the model file is where I would expect the instructions about the expected database format to be stored.

                         

                        When I get a chance (I am remote from my V6 and V7 installations, just V5 Pro available) I will have a look round and see what I can find that looks to me (in my ignorance of programming!) like useful information. There must be something somewhere.

                         

                        More soon, unless Darren or A. N. Other provides the solution first so that we can both read with interest. (Must get into this programming lark ...)

                         

                        All the best,

                         

                        Grant

                         

                         

                        Originally posted by TonyD:

                        Hi Grant / Darren

                         

                         

                        Also Grant you said in your reply,

                         

                        "If you want to make the join in Access then you would need to use that access definition to read into Monarch as a single entity (presumably a query?)"

                         

                        Does this mean i can bring in the Access definition into Monarch (i.e like using it as the model file)if so how do you bring this in with the mdb file?

                         

                        /b[/quote]

                        • Joining two test files and the OpenDatabase Method
                          Grant Perkins

                          Tony,

                           

                          I did a quick search in the forum and came up with [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000141#000000"]this response[/url] to an earlier question. Have you seen it?

                           

                          There is also a post which refers to SetProjectFile but I'm not sure if that would help you at this point.

                           

                          Grant

                           

                          [size="1"][ May 19, 2006, 12:27 PM: Message edited by: Todd Niemi ][/size]

                          • Joining two test files and the OpenDatabase Method
                            Darren _

                            Tony,

                             

                            When I was joining each dataset, I created the model for one file (the master file) and exported it to Excel / Access / whatever else ya got...Then I created a model for the second dataset or "joining file". But I did not export it. I just joined it directly from Monarch to the master file - which in turn creates a larger master file which contains the two datasets. When you and Grant talk about automating this process, it is automated by my standards 'cause I use Data Pump. I never have to run throught this process again and again. Once I get it right the first time, I just let Data Pump process it over and over...       

                             

                            You may want to check out the article in the current issue of the 'Monarch Report'. The article about Rimex and myself has to do with this exact process that I'm talking about here.

                             

                            [url="http://www.datawatch.com/pdf/products/monarch/Oct03_Report_Final.pdf"]http://www.datawatch.com/pdf/products/monarch/Oct03_Report_Final.pdf[/url]

                             

                             

                            Originally posted by TonyD:

                            Hi Grant / Darren

                             

                            Again thanks for your replies.

                             

                            Grant i think you are right you probably can automate the process of reimporting the databases through previously defining a model for the joined databases in Monarch. I am yet to try this but will give this a go. It would be nice if there were more examples or further information on this process. I obviously already have the programmers guide pdf downloaded from the website but have found the opendatabase method in there to not provide a proper worked example, which would be handy.

                             

                            Also Grant you said in your reply,

                             

                            "If you want to make the join in Access then you would need to use that access definition to read into Monarch as a single entity (presumably a query?)"

                             

                            Does this mean i can bring in the Access definition into Monarch (i.e like using it as the model file)if so how do you bring this in with the mdb file?

                             

                            Darren, when you were joining each dataset i take it you had to export each individual report first and then either joined them in Access / Excel and reimport them into Monarch or where you able to some how able to join each report prior to the export to Excel?

                             

                            Thanks

                            Tony /b[/quote]