9 Replies Latest reply: May 16, 2018 2:40 AM by James U RSS

    Creating a Monarch Classic Model for Excel Files for Use in Data Prep Studio

    James U

      Hi,

       

      Is there any way to create a model in Monarch Classic using source data from an Excel Work Sheet to run as a model in Data Prep Studio? I have tried this several times using a simple model, and it the past have been able to drag the model into Data Prep Studio (when a model was created for .csv files). However, when I try this I receive the error message:

       

      “The following Monarch Files failed to import:

      [1]

      File: *************.dmod

      Error: Content of this model file is not supported in Data Prep Studio”

       

      Are Excel files not supported? I know that a change list in DPS is probably the next option, but I need to automate this process as much as possible.

      My ultimate goal is to save several Monarch Models in DPS as a Workspace, on which excel files can be dragged onto to apply the changes, rather than creating lots of change lists.

       

      Thanks in advance for any help! 

        • Re: Creating a Monarch Classic Model for Excel Files for Use in Data Prep Studio
          Grant Perkins

          James,

           

          Which version of Monarch Complete do you have available for this task?

            • Re: Creating a Monarch Classic Model for Excel Files for Use in Data Prep Studio
              James U

              Hi Grant,

               

              How do I find this out?

               

              Monarch is version 14.3.2.7917 and DPS is version 14.3.2.15407 if that helps.

                • Re: Creating a Monarch Classic Model for Excel Files for Use in Data Prep Studio
                  Grant Perkins

                  Hi James,

                   

                  14.3 is good for this requirement.

                   

                  You mention .csv files. Are csv files also the origin for you Excel Worksheet? (.csv files tend to be a Windows default to use Excel to work with them but there can be better options when Monarch is your IT tool of choice.)

                   

                  In Monarch you treat a csv file (or any other delimited file) as if it is a database OR  you can interpret it as a TEXT file. Hold that thought for a moment.

                   

                  You can create a Monarch Classic model for the csv file (as text - i.e. a report not a database) and then bring that model into Data Prep Studio as a "Monarch File" to give you a dedicate Change List in the Workspace. This is referred to as a "Load Plan". There are a couple of things that are not converted from the Classic model in the release I have (filters for example) but they may not matter for your purposes and in any case they are easily applied using a change list as per normal DPS activity.

                   

                  Load Plans created from Text Files using Monarch Classic models allow drag and drop updates for the data source files.

                   

                  I don't recall trying to drag a dmod file into DPS  - using the option to Open a Monarch File has always been my approach and that, via the Load Plan created, allows the drag and drop functionality of the data source file (if text).  However, having just tried it with a dmod based on a text file treatment of a delimited file it happily created a Load Plan.  I had selected "Add more stuff" at the time.

                   

                  I'm a couple of minor releases behind on my system but just about to do some maintenance  to update that in the next day or so to see if any extension to the drag and drop functionality for "databases" has slipped in in the past 6 months or so.

                   

                  Grant

              • Re: Creating a Monarch Classic Model for Excel Files for Use in Data Prep Studio
                James U

                Sorry I should have stated that the excel files are .xlsx. Also there are multiple worksheets within that file and I am trying to extract just one of them. I could convert them to .csv but it would take a long time to do so.

                  • Re: Creating a Monarch Classic Model for Excel Files for Use in Data Prep Studio
                    Grant Perkins

                    Just lost a longish reply when a lost network connection as I hit the send button.

                     

                    To precis it:

                     

                    With a "database", as far as I have found, you have no drag and drop update functionality although whether that changed in the last 2 minor releases for V14 or now in V15 I have not checked. Yet.

                     

                    You can use "Monarch FIle" to load a dmod (or dprj) and create a Load Plan but I don't think you can drag and drop to the load plan directly. You would need to use Append (from memory).

                     

                    However see Austin's comment above.

                     

                    Your other option is to look at Automator if your objective is simplification of a regular and repetitive action.

                     

                    I'm assuming that the source xlsx Worksheet is consistent and consistently named.

                     

                    HTH.

                     

                    Short of time at the moment to re-create the full previous reply.

                     

                    Grant

                  • Re: Creating a Monarch Classic Model for Excel Files for Use in Data Prep Studio
                    Mo Abdolrahim

                    James,

                    Save the monarch classic in a project file.  then open the project file in the DPS.  Please note that references to the files are stored in the project file.  My test was done in Monarch v14.3.2.  When I opened the model in the DPS, I received the same error, but worked when I opened .dprj file.

                    When I opened the .dprj in the DPS, I noticed that my filters, summaries, and exports did not ported to the DPS.  But formula fields were ported fine.

                    I think you are just better off to open the Excel file in the DSP.

                    Regards

                    Mo