Sparx Systems Forum
Enterprise Architect => General Board => Topic started by: SystemsTinkerer on January 11, 2022, 08:17:13 pm
-
Hi,
I have been able to export the model elements, guid, ...etc to a CSV format to view in Excel via Publish Ribbon > CSV import/export HOWEVER it does not export the hierarchy packages so that we have a clear view of what elements are within which package. During export in CSV exchange specification one can choose required fields but it does not include packages.
Any assistance on this front will be really appreciated. Thank you for your consideration.
Best regards,
Oz
-
Well, that happens when you hammer a 3D structure to a flat plate. Use XMI export to get the structure as well.
q.
-
Hi Q.
Many thanks for your response & tip! So would the XMI allow me to analyse the contents for the model in a more user-friendly fashion like in Excel, so that I can use filters to analyse the model and identify redundant blocks...etc?
Cheers,
Oz
-
No, surely not. Unfortunately. XMI has the model along with the structure. But Excel can not deal with structure, only with a 2-dimensional matrix. Think about how you could model a structure in a simple matrix. That's possible, but definitely it gives your more headaches than any help. You might think of exporting some package content into one matrix (spread sheet) and the content of another one in the next. Then you could (manually, via API would be possible as well) setup another matrix that tells how the single sheets are related to each other.
Basically it all depends on what you want to achieve.
q.
-
I would probably write an SQL Search to export the contents and structure.
You can then export the search results to CSV
Since recursion and SQL are not really friend you'll have to figure out the maximum level of nesting you have, and then write that many joins.
Geert
-
Maybe the obvious answer but have you checked:
Preserve Hierarchy
In your CSV Specification
This will add two extra columns to the output CSV_KEY and CSV_PARENT_KEY and output all the packages
Also do not enter anything in Default Types, leave it blank, also, leave the Types field blank hen you export.
Phil
-
QWERTY, Geert & Phil - Many thanks for input & tips - really appreciate it :)
@Geert: I am keen on doing just that & that would be a good long term solution. Do you have any tips for newbies to make a start with EA repository using SQL & JavaScript? A set of simple examples to pick up the fundamentals would a good start.
@Phil: CSV_KEY and CSV_PARENT_KEY, I have tried by selecting the hierarchy option but not tampered with any other setting except for selecting the "available fields" transfer to "file specification", AND the result obtained appears to be in terms of parent & child relationship to every block element, port, part...etc, instead of info on parent/child packages. Where am I going wrong here?
Thanks,
Oz
-
Yes indeed by default all parent child structures will be output when preserve hierarchy is checked.
I have just tried this on one of my SysML example and yes indeed I got all Packages, Blocks, Ports, Parts etc.
Contrary to what I said earlier, you are meant to be able to filter the types exported, by entering a comma separated list in the Types field on the CSV Import / Export dialog, but when I entered:
Package,Class
I only got Packages!
When I entered
Class,Package
I got Class and Packages, so the export worked
So order does matter, of course SysML Block is a stereotyped Class, hence filter on Class, not Block
You can also enter the comma separated list Class,Package in the CVS specification and it will autofill the CSV Import / Export dialog
Hope this works or at least gets you one step further, if not, and if possible a screen shot of your Browser structure might help.
All the best
Phil
-
@Geert: I am keen on doing just that & that would be a good long term solution. Do you have any tips for newbies to make a start with EA repository using SQL & JavaScript? A set of simple examples to pick up the fundamentals would a good start.
It's pretty simple really. As an MVP you could start by creating an SQL Search with the following query
select
p.name as PackageName ,null as PackageLevel2,null as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from (t_package p
inner join t_object o on o.Package_ID = p.Package_ID)
where
p.PackageID = #Package#
union
select
p.name as PackageName ,p2.Name as PackageLevel2,null as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from ((t_package p
inner join t_package p2 on p2.Parent_ID = p.Package_ID)
inner join t_object o on o.Package_ID = p2.Package_ID)
where
p.PackageID = #Package#
union
select
p.name as PackageName ,p2.Name as PackageLevel2,p3.Name as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from (((t_package p
inner join t_package p2 on p2.Parent_ID = p.Package_ID)
inner join t_package p3 on p3.Parent_ID = p2.Package_ID)
inner join t_object o on o.Package_ID = p3.Package_ID)
where
p.PackageID = #Package#
order by 1, 2, 3
this will show the contents of the selected package, and three package levels underneath in the search window.
From there you can export to .CSV
If you want to take it a step further, you can use the same query in code using Repository.SQLQuery.
That will return an xml that you can parse and process further.
To get inspiration on how to use that to export data directly to an excel file, see:
https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/blob/master/Projects/Project%20A/Reports%20and%20exports/UMIG%20CodeList%20Export.vbs (https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/blob/master/Projects/Project%20A/Reports%20and%20exports/UMIG%20CodeList%20Export.vbs)
This includes a bunch of other scripts located in https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/tree/master/Framework (https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/tree/master/Framework)
These are all vbScripts, but you can do exactly the same with JavaScript if you prefer that language.
Geert
-
Geert,
I'm trying to get a list of all objects under a given folder/subfolders. I figured your SQL statement would get me close. I copied it, change #Package# to 13.
When I run the query, I get "Too few parameters. Expected 1."
I know I'm missing something obvious (due to not being fluent in SQL), what is it?
-
Geert,
I'm trying to get a list of all objects under a given folder/subfolders. I figured your SQL statement would get me close. I copied it, change #Package# to 13.
When I run the query, I get "Too few parameters. Expected 1."
I know I'm missing something obvious (due to not being fluent in SQL), what is it?
Sorry about that: typo. PackageID should be Package_ID
This should work
select
p.name as PackageName ,null as PackageLevel2,null as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from (t_package p
inner join t_object o on o.Package_ID = p.Package_ID)
where
p.Package_ID = #Package#
union
select
p.name as PackageName ,p2.Name as PackageLevel2,null as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from ((t_package p
inner join t_package p2 on p2.Parent_ID = p.Package_ID)
inner join t_object o on o.Package_ID = p2.Package_ID)
where
p.Package_ID = #Package#
union
select
p.name as PackageName ,p2.Name as PackageLevel2,p3.Name as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from (((t_package p
inner join t_package p2 on p2.Parent_ID = p.Package_ID)
inner join t_package p3 on p3.Parent_ID = p2.Package_ID)
inner join t_object o on o.Package_ID = p3.Package_ID)
where
p.Package_ID = #Package#
order by 1, 2, 3
Geert
-
Many thanks for your invaluable guidance - Phil & Geert
@Geert - I have my homework cut out - I will go through those resources to get started here.
@Phil - I am not sure where you are specifying the Class, Package precisely - I have included these in the CSV export specification panel, in the field "Default types" I have also added Diagram - since I need diagram field as well. Then I have added all the default fields in the "Select Element Field" window
It would be so much easier if I could share my screenshot with you but this forum interface does allow it. I clicked "insert image" but it did not allow me to upload the image off my pc.
Cheers
-
@Geert, thanks for the quick reply... but I am still getting a "the select statement uses a reserved word or incorrectly spelled argument name. I looked through and didn't see anything. I haven't seen null used as a field name before, but I trust that that works.
Any thoughts?
-
@Geert, thanks for the quick reply... but I am still getting a "the select statement uses a reserved word or incorrectly spelled argument name. I looked through and didn't see anything. I haven't seen null used as a field name before, but I trust that that works.
Any thoughts?
Yes,
I got that error as well after copying my own code from the forum back to EA.
Visually both look the same, but after doing a text compare I noticed that the space at the end of the select had been stripped automatically by the forum.
And apparently MS-Access doesn't like it when there's no space after the select keyword.
select p.name as PackageName ,null as PackageLevel2,null as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from (t_package p
inner join t_object o on o.Package_ID = p.Package_ID)
where
p.Package_ID = #Package#
union
select p.name as PackageName ,p2.Name as PackageLevel2,null as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from ((t_package p
inner join t_package p2 on p2.Parent_ID = p.Package_ID)
inner join t_object o on o.Package_ID = p2.Package_ID)
where
p.Package_ID = #Package#
union
select p.name as PackageName ,p2.Name as PackageLevel2,p3.Name as PackageLevel3
,o.ea_guid as CLASSGUID,o.Object_Type as CLASSTYPE,o.Name as Name,o.Stereotype
from (((t_package p
inner join t_package p2 on p2.Parent_ID = p.Package_ID)
inner join t_package p3 on p3.Parent_ID = p2.Package_ID)
inner join t_object o on o.Package_ID = p3.Package_ID)
where
p.Package_ID = #Package#
order by 1, 2, 3
This code should not have that issue.
Geert
PS. if you get the chance I would move to a real database like SQL Server. .eapx files are actually MS-Access databases and use a horrible SQL syntax.
-
Awesome, that works perfectly! Sorry I took so long. I too wish I could use SQLServer... but the IT Department does not want to waste resources on setting it up and maintaining it. I am optimistic that the v16 SQLite support may help avoid access... but we'll see.
-
but the IT Department does not want to waste resources on setting it up and maintaining it.
So they'd rather let you waste your time? Is that even a call the IT department should be making?
That wouldn't fly with me.
Me thinks they are just lazy and trying to avoid work, and it seems to be working too.
Geert
-
Further to my last message below - here is the screenshot:
Many thanks for your invaluable guidance - Phil & Geert
.................
@Phil - I am not sure where you are specifying the Class, Package precisely - I have included these in the CSV export specification panel,
(https://ibb.co/hD0HsBc)
https://ibb.co/hD0HsBc
-
I don't think you can include diagrams in a CSV export.
You could add them to your query if you join t_diagram on t_diagram.PackageID = t_package.Package_ID
Geert
-
Thanks, Geert - I will give this a go. It's a bit tricky but worth the effort & toil!
Cheers & Best,
Oz