Exporting model with package hierarchy to CSV file

[SystemsTinkerer]

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

[qwerty]

Well, that happens when you hammer a 3D structure to a flat plate. Use XMI export to get the structure as well.

q.

[SystemsTinkerer]

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

[qwerty]

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.

[Geert Bellekens]

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

[philchudley]

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

[SystemsTinkerer]

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

[philchudley]

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 Bellekens]

@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

Code: [Select]

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

This includes a bunch of other scripts located in 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

[FlunkStort]

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 Bellekens]

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

Code: [Select]

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

[SystemsTinkerer]

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

[FlunkStort]

@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 Bellekens]

@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.

Code: [Select]

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.

[FlunkStort]

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.