6 Replies Latest reply: May 15, 2014 9:59 AM by Grant Perkins RSS

    Question about monarch capabilities

    J _

      I have been pointed to Monarch as a tool to convert the format of some data.  My end goal is to load the data in to a SQL database, and this would be an intermediate step.  The data currently has one field per line beginning with the field name, and the fields can repeat indicated by a semi-colon on the beginning of the next line, and/or some times fields will not exist at all.  I want to end up with all of the fields for a record in a row rather than a column, so that I can import in to a database. 

       

      Is this going to be an easy task for me to accomplish, and is it worth it for me to purchase Monarch, and do I need the standard or professional edition? 

       

      Any direction would be greatly appreciated, as I am not very familiar at all with the product.  I would be happy to provide a sample of the data if that would help my explanation. 

       

      Thanks in advance!

        • Question about monarch capabilities
          Mike Urbonas

          J, if you can post some of your data here on the forum that would be helpful.

           

          Mike

          • Question about monarch capabilities
            J _

            Here is a sample of the data.  It includes four records delimited by a $.  As you will notice the fields may change in each record, and may occur multiple times.  The first word in the line is the field name, and if it is a semi-colon, then it is an additional field from the line above.  Ex. multiple PROD and SHIP fields. 

             

            BX 5981

            FILE Sample Inc.

            DOCTYPE Shipping Notice

            THEIM Y

            UNV N

            AP N

            AR Y

            PROD Kwik

            CO Sample,Inc.

            SHIP Tokyo, Japan

            DATE 1987

            ID 1

            $

            BX 5981

            FILE Core,FL

            DOCTYPE Shipping Notice

            THEIM Y

            UNV N

            AP N

            AR Y

            PROD Sample1

            ; Sample2

            ; Sample3

            ; Sample4

            CO Core,FL

            SHIP Core,FL

            DATE 1989

            ID 5

            $

            BX 5981

            FILE Trucking Company

            DOCTYPE Shipping Notice

            THEIM Y

            UNV N

            AP N

            AR Y

            PROD Mud Compound

            ; Sand

            ; Part

            ; cement

            ; Mud

            CO Trucking

            SHIP Salem, MA

            ; Boston, MA

            DATE 2004

            ID 8

            $

            BX 5981

            FILE Correct Foundry

            CMTS Folder Empty

            ID 7

            $

            • Question about monarch capabilities
              Mike Urbonas

              OK...Since this data is ultimately going to go into an SQL database, you do not need to capture all of the data in one Monarch model template, though I think you could do so.  But by far the quickest way for you to accomplish your goal with Monarch would be to create a few different models as follows:

               

              (1) Create a Monarch model to capture all data that exists in each record, only once (looks like you have a lot of Yes/No "Y"/"N" data for example).

               

              (2) Create two models to grab data that may occur more than once, like the PROD data.  Use one model just to grab the first product data right after the word "PROD", and another model to grab any multiple data after the ;.

               

              (3) Create models that focus on a particular non-recurring datatype, such as FILE or CMTS.

               

              For all of these models I'd be sure to always grab the "BX" number, which looks like your "primary key" of data.

               

              Once you export the data from all of these models (many format options: MDB, XLS, etc.), you can combine them together into one table, if you wish, using Monarch V7 Pro.  Monarch V7 Pro lets you do "lookups" (left outer joins) to combine up to 9 different data sources.  For this reason I would encourage you to buy Monarch Pro.

               

              Bottom line, yes, Monarch can get this data for you and export it in an SQL database-friendly manner.  Datawatch even offers model building services at a nominal cost; see: [url="http://www.datawatch.com/vortexmlsupport/model_building.htm"]http://www.datawatch.com/vortexmlsupport/model_building.htm[/url]

               

              Good luck,

              Mike

              • Question about monarch capabilities
                J _

                Thanks a lot for your reply.  It sounds like it is going to take some work, but I think I will give the product a try. 

                 

                Thanks again!

                • Question about monarch capabilities
                  J _

                  Thanks a lot for your reply.  It sounds like it is going to take some work, but I think I will give the product a try. 

                   

                  Thanks again!

                  • Question about monarch capabilities
                    Grant Perkins

                    J,

                     

                    Does your data get much more complex than your sample - for instance are there a number of other types (names) of fields which may appear sometimes? Or perhaps more of the fields have multiple occurrence extensions?

                     

                    If your example format is pretty much as complex as it gets and with, perhaps, a little work in the area of the multiple line fields dependant on how you want the information to look, it may be possible to run this as a single model 'conversion' using Monarch V7.

                     

                    Should that prove too much of a modelling challenge when working with the full data input, Mike's multi-model provides a solid solution anyway.

                     

                    Good luck.

                     

                    Grant

                     

                     

                      Originally posted by J:

                    It sounds like it is going to take some work, but I think I will give the product a try. 

                     

                    /b[/quote]

                     

                    [size="1"][ September 25, 2003, 09:21 AM: Message edited by: Grant Perkins ][/size]