Exporting Archi models to Excel Spreadsheet - questions

Started by Phil Beauvoir, September 05, 2017, 18:38:47 PM

Previous topic - Next topic

Phil Beauvoir

I notice that some users are exporting Archi models to CSV format in order to get them into MS Excel.

I'm curious to know why.

And what would be the ideal native Excel data layout if you could bypass the CSV export.

/Phil
If you value and use Archi, please consider making a donation!
Ask your ArchiMate related questions to the ArchiMate Community's Discussion Board.

Hervé

Hi Phil,

On my side, I export to Excel spreadsheets because I'm using Archi as a repository but our techical teams are still working with last century tools like Excel spreadsheets so I had to adapt myself.

That's the reason why I developped the form plugin which:
   1 - facilitates the input of all the required elements and relationships properties for my repository
   2 - natively exports the elements and relationships properties to the Excel spreadsheet that is expected by the other teams.

Best regards
Hervé

Phil Beauvoir

#2
Hi Hervé,

I'm not able to get your form plug- in to work. I've copied the jar and conf files. But nothing shows up in Archi. Edit - needed to set prefs.

I can see in your source code that you have the POI plugin, but can't see the code to do the Excel export.

My aim is to create a simple Export to Excel plugin using POI. I've done the work on it, I just need to decide the layout of the Excel workbook and sheets.

One way is to have 3 sheets in the workbook - elements, relations, and properties and set the rows/columns like the three CSV files when doing the CSV export. This would make life easier for folks who export as CSV and then import into Excel. But there may be a better format.
If you value and use Archi, please consider making a donation!
Ask your ArchiMate related questions to the ArchiMate Community's Discussion Board.

Chris Usher

Similar to Hervé.  Publishing lists of entities to Excel is one of my regular outputs.  I have other architects and decision makers that prefer to compare lists of As-is and To-be entities rather than to visualise relationships and interactions in diagram form.

Also, some of our entity attributes are mastered in separate repositories (databases, spreadsheets, SharePoint) and while I load these into the Archimate model as properties, a process of checking for differences and reconciling is necessary, so exporting from Archi and the other data sources in CSV is necessary to do diffs.

Hervé

Hi Phil,

I know that the weakness of my plugin is the configuration file which can be very complex and the library I use to parse it does not always allow to get the line number where the error stands.

I'm currently adding a graphical interface that will allow to create (or update) a form with a real time visualisation of the result.

Because of the back-to-school season, i'm going very fast, but I expect to release a beta version of this graphical editor in about 2 weeks.

The code where I export to Excel is inside the exportToExcel() method of the Formdialog class. the file version that is used in my 1.53.4 version can be found at the following address :
https://github.com/archi-contribs/form-plugin/blob/49ae89eb3b474378c064a4d5043ef451bd4490f3/sources/src/org/archicontribs/form/FormDialog.java

Meanwhile, please feel free to send me your configuration file in a private message or mail and I will help you  :)

Best regards
Hervé

Phil Beauvoir

Hi Hervé,

I installed the Form plugin into a fresh Archi installation and forgot to set the *.conf file. I have figured it out now. ;-)

Thanks for the info.

Phil
If you value and use Archi, please consider making a donation!
Ask your ArchiMate related questions to the ArchiMate Community's Discussion Board.

Jean-Baptiste Sarrodie

Hi,

I think there is no magic export format as this all depends on the real usage behind.

I can see two main use cases though:

1/ Reporting & Data sharing. In this case the main goal is to make the information easily usable by human. In such case I would make sure that each ID is next to or replaced by the concept name. That would make it easy to read the list of relationships and properties. And this would allow users to create pivot tables.

2/ data exchange with other tools (where "other tool" can also be Archi itself). I assume that CSV (or XML) is better when information has just to flow to other tool without a human editing it, so I think that the real use case here would be "edit model information outside Archi". For this I would make sure that (again) labels are next to Ids, but using formula (like VLOOKUP) that allows people to safely edit information.

It might be interresting to look at RightField as this is very related to the latest use-case. RightFiels i a (java, and perhaps eclipse based) tool that allows one to map ontology informations to Excel cells without contraining user to one specific format.

That being said, maybe the real need is in fact to have a user customizable way to export and import information...

Regards,

JB
If you value and use Archi, please consider making a donation!
Ask your ArchiMate related questions to the ArchiMate Community's Discussion Board.

MarcF88

We have used CSV export for bulk verification and modification of properties and documentation of elements. It works a bit faster when all your items are in a list and you can sort and filter.

There are some issues importing and exporting the CSV in Excel, most to do with differences of opinion on quotes and comma's. It works fine all the time with open office though.

Is this really high on the priority list? And if so, what improvements would be required?