Import of Glossary items from Excel

[Chris Jones]

Enterprise Architect       : Corporate Edition
Program Version       : 10.0.1006 (Build 1006) - Unicode
Database Version      : 4.01
Version Date             : Jan-31-2004

Current level of understanding : New User. 2 days of Training
What is it that I want to do?
I want to load a 600+ Business Glossary of Terms currently held in EXCEL Spreadsheet format, into Enterprise Architect to enable use to apply some Governance around Naming standards and common Terminology. I don’t relish typing them all in by hand.
To test out the technique, I have created a dummy Project and added a few items into the Glossary [Project/Documentation/Glossary]. I then tried exporting as a CSV file and then opening  it as a delimited file using Excel to see what the format was like. My file specification for this is name, type, GUID, notes, CSV_KEY, CSV_PARENT_KEY .
The Headings are the only things that appear. None of the terms I entered appear.
I also tried using the .XMI export, opening it in Excel again and trying to add some new Terms,  but firstly, there are 4 lines per Term, and secondly, if I try to add anything formulaic to help with automating the numbering of the record [ in the Value column] the just appear as text. I have changed the Cell format but nothing changes.

On the plus side I have managed to import and export elements that I have created. Without trying to pre-empt the solution, it may be something to do with Glossary Items being part of the Tool and not created by the modeller.
 
So,… that’s where I am.
Has anyone else tried to do something similar? It feels like a technique that would be useful in a variety of situations to reduce the risk of incorrect Manual entry, and speed up bulk input of large volumes of data, so I expect someone must have trodden this path before.

[qwerty]

You can try this:
- export an existing tiny glossary from EA (Project/Model .../Export Ref...)
- look into the generated XML (it's simple!)
- export your data from Excel so it looks like this XML
- (eventually modify things manually)
- import the XML in EA

You could also write a little script. Just to get acquainted with the API.

q.

[Geert Bellekens]

Hi Chris,

Yes, you are not the first, there are lots of users with the same problem.

You can can try my free Excel to EA importer. I recently added the glossary import feature.

This is a rather basic tool, but since it is all in VBA you can extend it according to your needs.

Geert

[Chris Jones]

Thanks qwerty. A good suggestion.
Tried the technique you suggested and exported as reference data which did give me the simple XML form. I opened it in Excel and got the glossary into it and reformatted it into XML form.
What a faff!
When I try to load this XML form through the reference data import option it asks me to select "One or more datasets to import". There are none to select from.
I have tried the
Import Package from XMI : unknown XMI exporter or version
Also Batch XMI import and CSV import in desparation. Nothing available to select from for the former. The latter clearly is not correct.
I am not a VBA developer so that option is not open to me.
At this rate typing in from scratch seems to be the quicker option.
I must be missing something.

[Chris Jones]

Thanks also Geert. I am arranging to get the download to work as my work station is locked down and I cannot import. I will try tomorrow.

[qwerty]

The XML must have the exact format as that from EA. Make sure it has

Code: [Select]

<RefData version="1.0" exporter="EA.25">as outer section.

q.

[Chris Jones]

Within the Excel Spreadsheet  the second line already contains the text you suggest.

[ I have replaced "<" with "-" and ">" with "=" as the forum will not allow script to be used and I don't seem to be able to paste in an image ]
 
-?xml version="1.0" encoding="windows-1252"?=
-RefData version="1.0" exporter="EA.25"=
      -DataSet name="Project Glossary" table="t_glossary" filter="Term='#Term#'" stoplist=";GlossaryID;"=
            -DataRow=
                  -Column name="Term" value="Customer"/=
                  -Column name="Type" value="Term"/=
                  -Column name="Meaning" value="An individual or organization that has purchased a product or service"/=
                  -Column name="GlossaryID" value="1"/=
            -/DataRow=
…etc.
Opening the same file .XML file with Notepad shows
-?xml version="1.0"?=
-?mso-application progid="Excel.Sheet"?=
-Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40"=
 -DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"=
  -LastAuthor=Chris Jones-/LastAuthor=
……. And later on
-/ExcelWorkbook=
 -Styles=
  -Style ss:ID="Default" ss:Name="Normal"=
   -Alignment ss:Vertical="Bottom"/=
   -Borders/=
   -Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/=
   -Interior/=
   -NumberFormat/=
   -Protection/=
  -/Style=
 -/Styles=
 -Worksheet ss:Name="Glossary"=
  -Names=
   -NamedRange ss:Name="_FilterDatabase" ss:RefersTo="=Glossary!R5C1:R5C11"
    ss:Hidden="1"/=
  -/Names=
  -Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="5339" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15"=
   -Column ss:Index="4" ss:AutoFitWidth="0" ss:Width="499.5"/=
   -Column ss:AutoFitWidth="0" ss:Width="286.5"/=
   -Row ss:AutoFitHeight="0"=
    -Cell=-Data ss:Type="String"=&lt;?xml version=&quot;1.0&quot; encoding=&quot;windows-1252&quot;?&gt;-/Data=-/Cell=
   -/Row=
   -Row ss:AutoFitHeight="0"=
    -Cell=-Data ss:Type="String"=&lt;RefData version=&quot;1.0&quot; exporter=&quot;EA.25&quot;&gt;-/Data=-/Cell=
   -/Row=
   -Row ss:AutoFitHeight="0"=
    -Cell ss:Index="2"=-Data ss:Type="String"=&lt;DataSet name=&quot;Project Glossary&quot; table=&quot;t_glossary&quot; filter=&quot;Term='#Term#'&quot; stoplist=&quot;;GlossaryID;&quot;&gt;-/Data=-/Cell=
   -/Row=
… etc

So it does appear to be passing the correct exporter version.

Although I am ticking the box for Write Log File I can’t find it to provide us with any additional information.

I appreciate your assistance.

Chris

[qwerty]

N.B. Check the Disable Smilies when posting XML

You can not use the Excel export XMI format. You need to create columns that match the format. Use something like the following formulas in column D:

Code: [Select]

="<?xml version=""1.0"" encoding=""windows-1252""?>"
="<DataSet name=""Project Glossary"" table=""t_glossary"" filter=""Term='#Term#'"" stoplist="";GlossaryID;"">"
="<DataRow>"
="<Column name=""Term"" value=""" & A3 & """/>
<Column name=""Type"" value=""" & B3 & """/>
<Column name=""Meaning"" value=""" & C3 & """/>"
... repeat for all definitions
="</DataRow></DataSet></RefData>"
Assuming that in columns A to C you have Term, Type and Meaning starting from row 3 then you need to copy/paste the results in column D into notepad(++) and save it as glossary.xml. You should be able to import that with EA then. Beware of quotes in any of your source columns. Replace them by &quot;

q.

[Chris Jones]

Mijnheer Bellekens, Dank uwel!

Once I had negotiated our firewalls and downloaded your exporter and added my Glossary items, things really started to move. After I had removed all the duplicates [There were quite a few] I have a fully populated Glossary in EA.

Thank you so much.

Thanks also to qwerty for your suggestions.

Chris

[Geert Bellekens]

Graag gedaan

Geert