Creating a Monarch Classic Model for Excel Files for Use in Data Prep Studio
Altair EmployeeHi,
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!
Answers
-
James,
Which version of Monarch Complete do you have available for this task?
0 -
Altair Forum User said:
James,
Which version of Monarch Complete do you have available for this task?
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.
0 -
Altair Forum User said:
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.
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
0 -
Altair Forum User said:
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
Please correct me if I'm wrong on this but I believe with the release of V15, you can now use dmod templates in DPS. Not sure if this is getting to the heart of the issue but it's something to note.
0 -
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.
0 -
Altair Forum User said:
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.
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
0 -
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
0 -
Altair Forum User said:
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
From dmod files Filters and Sorts are not brought over to DPS as at V14.3.
However calculated fields generally are but there are some Classic Model capabilities that are not readily available directly in DPS (which sometimes does things in a different way to Classic) and may not appear in the Load Plan. For example if a model uses a feature or function that DPS does not have a means to emulate it may not be carried over - depending on the release version of Monarch since I assume that the less commonly used features and functions of Classic will find their way into DPS over time.
Grant
0 -
Altair Forum User said:
From dmod files Filters and Sorts are not brought over to DPS as at V14.3.
However calculated fields generally are but there are some Classic Model capabilities that are not readily available directly in DPS (which sometimes does things in a different way to Classic) and may not appear in the Load Plan. For example if a model uses a feature or function that DPS does not have a means to emulate it may not be carried over - depending on the release version of Monarch since I assume that the less commonly used features and functions of Classic will find their way into DPS over time.
Grant
Many thanks everyone for all of your help, I'll give these ideas a go
0