Committing the cardinal sin…

001Strong words… “Committing the Cardinal Sin“!

I can remember from my early days with SDL many interesting, and often frustrating conversations with the then Product Manager for MultiTerm.  The almost religious use of phrases like “You can’t use spreadsheets for terminology”… “It only takes a few steps to be able to create a simple glossary with MultiTerm”… “You can’t properly export a MultiTerm termbase to Excel”… and many more discussions along these lines.  Well, over the last year or so mainly thanks to the SDL OpenExchange which removes the shackles of being tied to “the way it’s always done” we have seen one tool in particular that has proven this traditional way of thinking wrong.  But not because our friendly product manager was wrong… he was mostly right.  When you think about Terminology Management in the traditional sense then Excel is not really suited to managing concept oriented databases that are designed for the terminology professional.  It has its place, but is definitely prone to error and difficult to manage as the database grows.  But what if you only want a glossary?

The Glossary Converter on the OpenExchange changed all of this and today the conversion between fairly complex databases to Excel for routine maintenance is commonplace, the ability to convert Excel glossaries to MultiTerm is childs play.  There is another tool that provides even more flexibility, again on the OpenExchange, called Excelling MultiTerm which I mean to write about one of these days.  But none of these has the audacity to commit the “Cardinal Sin“… which is of course using the spreadsheet as your termbase with no conversion required at all!

How is this possible?  Well, in case you missed it I wrote briefly about this in my last article.  There are a couple of new APIs that have been made available for developers in the new Studio 2015 SR2 release and one of these is the Terminology API.  In a nutshell this allows a developer to use another source for your terms, in preference to, or in addition to MultiTerm.  So to help developers see how to use this we created the possibility to simply use a spreadsheet… I mean the “royal we” (it was Romulus Crisan really!)

Format of the Spreadsheet

We do need a few rules with our example.  The good thing is a developer could take our code and extend this to more complex scenarios if required, but I think MultiTerm, or another database approach will still be better as the structures gets more complicated.  For our sins however we have set the scope as follows:

  • One column for source terms
  • One column for target terms
  • One column for Approval, Forbidden terms
  • Ability to separate synonyms with whatever you like (default is a pipe symbol)

The reason for this is twofold.  First it’s intended to be an example of how to use the Terminology Provider API for developers to use and learn from, and secondly it’s aimed at the glossary user rather than the complex terminology user.  However, I reckon this simple format will satisfy the needs of a very large number of translators indeed!  I would like to add though that our original simple format and usecase quickly became a lot more sophisticated thanks to our Beta testers, and Nora Diaz in particular… so thank you Nora for the great feedback to shape how this is used in practice!

How do you use it?

The “Add a termbase” feature in Studio has been changed to look and feel a lot more like the “Add a Translation Memory” feature.  So it now looks like this:

002

By default you get the “File-based” and “Server-based” MultiTerm termbases and then after that you can have whatever terminology plugin you can find!  Unsurprisingly there is only one available on the OpenExchange today, because this API has only just been made available, but I expect this to change over the course of the year as more glossary and terminology providers see the opportunity to add their products into Studio for use in interactive translation.  The one that is available today is what we’re talking about, the Excel-based Terminology provider.

So you select this one and will be presented with a few basic options like this:

003

The Has header checkbox allows you to ignore any titles in the first row of the spreadsheet.  If you don’t check this then they will treated as terms in your glossary.  The Is Read Only checkbox is something we put in there just in case you do try to use this in a shared environment, like dropbox for example.  We haven’t tested this at all, but I’m almost certain someone will try it!  So if you do then the idea is you would make the shared Excel glossary a Read Only one using this option on every machine linking to it.  This way we hope it will minimise the danger of corrupting the file.  So you write to your own Excel file and just read the shared one, safe in the knowledge you can’t write to it even if you try!

So it’s pretty straightforward, you just set the source and target columns to match what’s in your spreadsheet, add an approved column, or notes column (it says approved but you could use this for whatever you like), set your languages and if you are using synonyms you specify whatever text separator you used.  So to elaborate on this a little, your file could look like this:

004

An Excel distraction!

If you maintained a spreadsheet in the past with synonyms in different columns then just concatenate them into a new column.  If you’re not familiar with this very neat feature in Excel you could do something like this:

=B2&"|"&C2&"|"&D2

This would allow you to concatenate words in cells B2, C2 and D2 with a pipe symbol between them, like this:

005

So now you would have the correct string containing all synonyms in one cell, E2.  You can then copy the cell down your spreadsheet and do them all at once.  Make sure you then select the column, copy it and paste it in as values so you have the text as opposed to the formula, then finally delete the original three columns so you only have the one with the concatenated synoynms:

006

IMPORTANT: Make a copy of your original spreadsheet before you start to mess around with it!!

If you don’t use synonyms then don’t worry, you only have to select your columns in Studio exactly as they are.

Back to the app!

Once you have set your settings just select your spreadsheet and click on the submit button:

007

Now you should have added the spreadsheet as if it was a MultiTerm termbase, only less clicks 😉

008

Some interesting things to note.  If the filename is long then it doesn’t fit into the window, just like MultiTerm, and you need to make sure your languages match those you need, just like MultiTerm.  Now you’re ready to go!

I moved these windows next to each other just to show you how they work.  So here you have the new custom termbase Viewer next to the Termbase Recognition window.  It’s also worth noting that we tested with a spreadsheet containing over a million lines in it, something this large the filebased MultiTerm solution would not be able to handle at all… this one has some 90k lines so still pretty sizeable and I’m not noticing any performance degradation at all:

009

The recognition window you’ll be familiar with because this is just how MultiTerm works.  You see the terms that were found in the spreadsheet and you can also view the term details from there in the same way you would using MultiTerm to see the terms in the termbase viewer.  In this case there is little value in using the viewer for more detail because it’s exactly the same as the term recognition window.  However, if you want to edit anything this is where it comes into its own.  There are a number of features around this to be aware of, and all associated with the buttons you can see in the screenshot above:

  • Add: this feature allows you to add new terms whether they are in your translation or not.  So if you just wanted to add something you found while looking up another term on a website perhaps, then you click on Add and just type the new source term into the cell, then press the Tab key (or use the mouse) and you’ll be taken to the target where you can add that too.  Add all the synonyms you wish, and notes or approval status, and then you must click on the Save Entry button.
    So works the same as using the “Quick Add New term” and “Add New term” icons in Studio itself.  Using these will add the terms and save them to the spreadsheet automatically.
    If you keep adding new entries using the Add button in the Viewer and forget to press Save Entry after each one then you will need to use the Sync feature before you finish (see below) or you’ll lose them when you close Studio.
  • Delete: clicking this button will delete the entry you have highlighted in the Viewer.
  • Save Entry: this will save the entire entry you are currently editing, and only that one… you can edit an entry just by double clicking it, or selecting and pressing F2
  • Sync: this will synchronise all the changes you have made in Studio with the spreadsheet.  If you have made changes into the spreadsheet while working (be careful with this, it’s not a great idea – see limitations below) then these will also be synchronised and made available in the Project.  Restarting Studio will also synchronise changes made in the spreadsheet with the Studio Project.

In the translation editor itself you of course get all the usual benefits of being able to see the highlighted terms, and place them using AutoSuggest:

010

So pretty cool addition, and virtually hassle free if all you want is a simple glossary!

Limitations and Important Precautions

Verification: at the moment the API doesn’t extend to being able to make use of the term verification features that MultiTerm offers.  So you cannot use your Excel spreadsheet of terms for verification purposes.

Settings: the settings for the spreadsheets you add can only be accessed when you add a spreadsheet.  This is also a limitation of the current version of the API and available functionality in Studio.  So if you wish to change something you have to remove the spreadsheet and add it again.  During testing I occasionally corrupted the settings in Studio, probably as a result of messing around so much, but if this should happen it’s worth noting that the solution was simple in every case:

  1. remove the spreadsheet from the list and close Studio
  2. delete this file : c:\Users\[USERNAME]\AppData\Roaming\SDL Community\ExcelTerminology\excelTerminology.json
  3. restart Studio and add your spreadsheet back

The Excel file itself: Excel is not a database!  There are no controls over it at all.  So here’s a few precautions worth noting:

  1. take a copy of your Excel Glossary and make sure you always have a backup
  2. don’t open the Excel glossary while working on it in Studio.  We have built in some warnings to remind you if it’;s open during operations more likely to put the file at risk, but better to get into the right habit from the start
  3. don’t share it on a shared drive, or dropbox for example, unless you maintain it in read only mode and don’t be surprised if it gets broken!

I don’t mean these warnings to put you off using this app!  Several of our Beta testers loved it, especially the simplicity of working with it in Studio and for maintenance in Excel.  But it’s important to note that there are limitations with using a simple file like this instead of a database… it’s clever and going to be useful for any developers who want to know how to build a custom terminology provider, but it’s no substitute for a properly managed database with appropriate controls designed to manage access for read/write operations in single user and multi-user environments.

It’s also the “Cardinal sin” I’ve wanted to commit for many years!!

So, what’s it called and where can I get it?  I really wanted to call it “Cardinal sin” but opted for the less provocative “TermExcelerator” which is available on the SDL OpenExchange here.

52 comments
  1. Caroline said:

    Hi, this sounds good, but unfortunately, The plugin installer tells me that my Sudio 2015 12.0.4837.1 is not compatible. Do you have any suggestion?

    Like

    • Yep… upgrade to SR2. These are brand new APIs and they are not available with any build earlier than SR2. Go and check your account and you should find the download freely available for you in there.

      Like

    • Caroline said:

      THANK YOU!!! You’re ever so brilliant! I really do owe you much.

      Like

      • I wouldn’t go that far… but thank you very much Caroline. Glad I could help!

        Like

  2. Caroline said:

    thank you… and sorry for this ever so stupid question, considering it was indicated under “technical requirements”…

    Like

    • No worries Caroline… I bet you won’t be the last 😉 At least you proved the plugin installer version control works!!

      Like

      • Caroline said:

        Hi Paul,
        It’s fantastic! Thanks ever so much.

        My Termbase Viewer is not at all like the one shown in your article, though. Am I to configure anything else?

        Another question: if it is possible to aggregate synonymes into one cell with those pipes. Is it also possible to do the contrary? i.e. I received an xls-file from a customer containing many synonymes separated by pipe symbols. I never had time to “clean” this file into different columns for using it properly in Multiterm. Your solution is helping me much now, but I was wondering whether there might be any function for splitting one cell containing “term1|Term2|Term3|…” into different columns. Have you got any idea?

        Thanks again for your tool.

        Like

      • Hi Caroline, I manipulated my screens to do that so I could show more in one screenshot, it’s not the default. So I guess you could try moving your windows around in Studio? This article might be handy if you haven’t done that before.
        To split the cells, yes of course you can do this too. You can do it with formulae in as many columns as you have synonyms using LEFT, RIGHT functions but that can get quite complicated. So the easiest way is to use the “Text to Columns” command. Just select your column of text and then go to “Data->Text to Columns”. You can specify the separator in the wizard and also where you want the columns to be in case you have selected a column in the middle of other columns. This approach is really simple and fast.
        I love Excel!!

        Like

    • Thanks Abdelfatah… I hope the plugin will be useful for you too!

      Like

  3. “The then Product Manager for MultiTerm [said] ‘It only takes a few steps to be able to create a simple glossary with MultiTerm’”

    Pity that those “few steps” were so badly documented and thoroughly hidden in the horror story that goes under the title of MultiTerm Help System (which should be subtitled “Lasciate ogni speranza o voi ch’entrate”).

    Sounds like that former MultiTerm product manager was the kind of person who was utterly certain he knew better than his customers what they wanted wanted or needed.

    But it’s a very good thing that now we have Glossary Converter – Thank you for the article: brillant as usual!

    Liked by 1 person

    • Passing through the gates of MultiTerm did indeed have that effect for many… these days it’s already so much better!

      Like

  4. yujich said:

    Paul, thanks for the article. This is very interesting. Many translators would be interested.
    I hope you could also consider supporting UTX 1.20 in the future.
    http://www.aamt.info/english/utx/
    UTX can be included in an Excel file, but its structure is somewhat different from the pipe-connected Excel format.
    There are advantages of UTX 1.20 over the pipe-connected Excel format.
    For example, UTX can handle “n to m” relationship. Some synonyms can exist on both source and target sides.
    Also, UTX’s “default translation” among synonyms should be helpful for translators. UTX recommends picking one “approved” as the default term. The rest will be “non-standard.” If there’s no default, an arbitrary term will always appear as a translation.

    Like

    • Hi Yuji, I think this would be quite easy for a developer to do now, especially as the source code for the Excel provider is freely available on the SDL Github pages. I don’t think this is something we would do at the moment but there is nothing to stop you doing it yourself, or employing a developer to do it for you.

      Liked by 1 person

      • yujich said:

        Thank you, Paul.

        Like

  5. Christine said:

    Hi Paul!
    Thanks to your article, I discovered that SR-2 actually had been released yesterday 🙂 SDL Marketing has spread the news today.
    You might want to explicitly highlight in your article that “TermExcelerator” requires the brand new Studio 2015 SR-2.

    Kind regards
    Christine

    P.D.: Great term: “TermExcelerator” – perfectly fits with the current “Starkbierzeit” in Bavaria, with all the strong beers called “Maximator”, “Triumphator”, “Salvator” etc. 😉

    Like

    • Thanks Christine… thought you might have discovered this on the Beta too? There were notifications sent out yesterday, I had one to my personal email as I like to see what everyone else gets… maybe your spam filter caught this one 😉 Like the beer analogy!!

      Like

  6. -a said:

    Hi Paul,
    thanks for a good read. Regarding verification – I assume that if you developed a custom verification plugin with the Verification API, you could do the three term checks also Excel-based. Correct?

    Like

    • You know what they say about “assume”! We didn’t look at that too much because our focus was on the Terminology provider and as there is currently no link with this to the built in Terminology Verification (heavily built around MultiTerm) it would mean an additional development task. In theory that could be possible of course and could even make it viable to improve on the existing terminology verification capability.

      Like

  7. This app is great for adding a client’s simple Excel glossary to Studio and returning it updated with the project.

    Slightly off-topic, and you may already know this, Paul, but there’s a really quick way to copy or propagate Excel formulas in a column:
    – Click the cell that contains the formula (E2 in your example).
    – Double click the bottom right-hand corner of the cell (where there’s a little green square).
    Done!

    And if you want to auto fill any neighbouring cells, just drag that little green square to include the cells you want (across and/or down).

    Like

    • Yep, know that one. But I prefer to use the keyboard so I select all the cells by holding down the shift key and then just press Ctrl+d which fills them all the same way as your mouse work. Excel works so fast on my laptop I find it harder to control what I want sometimes!!

      Liked by 1 person

  8. Thinking about the simplicity of this app, am I right in thinking that it would work for people who are using Studio Starter and those who’ve downloaded a Studio demo? In other words, is it independent of Multiterm? That would make it even more useful.

    Liked by 1 person

    • Hi Emma, that’s a really good idea I hadn’t thought of. I don’t think it will help Starter as this doesn’t support OpenExchange apps at all, but it might be an app we could provide to anyone testing the demo. I’ll look at that… great idea! Thank you.

      Liked by 3 people

  9. AbdulRahiem Dubelaar said:

    Good afternoon Paul, it really sounds great, although I am fairly conversant with MultiTerm nowadays. I just have two questions: (1) Can I have three languages in a single spreadheet, and (2) Will a pair work in both directions?
    I have read the limitations and precautions (above) and, unless I have not read carefully, there is no word about it working for only two languages and in only one direction.

    Like

    • Afternoon Abdul. You can have as many languages as you like, but can only use three columns when you use it in Studio as mentioned. This is simple implementation intended to be more of an example for developers who want to create more complex terminology providers to use in Studio. But it does also serve a useful purpose for anyone with simple glossary needs using a spreadsheet. It will also work in both directions because you specify the columns for the files you are working on.
      Why not give it a try!

      Like

      • AbdulRahiem Dubelaar said:

        Thank you, Paul. I will certainly give it a whirl, after I will have finished my current job. BR.

        Like

    • Which links Kim… they all seem to work for me? If it’s the OpenExchange pages try clearing your cache or a different browser. That site always has problems for some reason.

      Like

  10. Magali said:

    Hi there! My client uses TermExcelerator and is asking me to use it too, but I seem to have a bug with it. Even if I choose the langages (English and French) after I Submit, it still says that the termbase languages are not defined. Therefore, I have no termbase open and furthermore I receive error message when I click on any source segment (so I can’t even look into my TM by selecting text in the source segment 😦 All the problem seems to come from the fact that it doesn’t recognize that I chose langages for the termbase. Thanks in advance for your help!

    Like

    • Hi Magali, this is very odd because there is no language recognition in this plugin at all. Did you also have a MultiTerm termbase in your project with different languages to the project itself?

      Like

  11. Magali said:

    Hi Paul, No, I have no MultiTerm termbase in my projects, I actually tried several times with different projects and different excel documents. I always translate from English to French. Could the problem be from the fact that I have studio in French, therefore when I choose the langages, they appear in French? Thanks!

    Like

    • I don’t know Magali. Can you post this question into the community so we have more opportunity to look at this with screenshots etc?

      Like

  12. It is not clear to me where I will be able to add terms (while in Studio) to such an Excel Termbase once I add it to Studio through the plugin

    Like

    • The same way you always do! Works the same way it does when you use MultiTerm except they are added directly to the spreadsheet when you click “Add term”. You can add/edit in the preview pane as well and this is even easier than when editing terms in MultiTerm.

      Like

      • Thanks. Now I have another question or two. Will it work when the excel file is placed in my Google Drive folder? I want to use the same termbase regardless of which computer I use.

        And another thing is, how do I export a Multiterm termbase that has synonyms both in source and in target for some entries, to such an excel spreadsheet?

        Like

      • Google drive… why don’t you try it! Good to remember that it is only a spreadsheet and therefore no built in mechanisms for control. We did add read/write controls to the app in case you share with others to try and provide more control and prevent file corruption, but if it’s just you and you don’t use the same file at the same time from different machines then I guess it would work. We certainly don’t thoroughly test, or support file sharing scenarios as these are what GroupShare is designed for. So have a go and you can tell me!

        Exporting the termbase… just use the Glossary Converter. Will do it with a drag and drop and you’ll have exactly what you want.

        Like

  13. Unfortunately I get problems and errors when the sheet is saved to Google Drive, in particular Access Denied when I click sync, also errors pop up when editing and saving a term entered in error.

    Like

    • Sounds like a permissions thing… I had a quick play and have the same problems in GoogleDrive. Tested in Dropbox and it all seems to work perfectly. Knew there was a reason I prefer Dropbox 😉

      Like

      • Will try OneDrive next as I already have access with Win10/Office 16 (whichever)

        Like

  14. Hmm, just trying it out, but am getting no hits, and when I try to add a term pair, am getting the following error: “Sequence contains no matching element.”

    Any idea what I am doing wrong?

    Like

    • Better to use the community in the appstore forum Michael… but a hunch would be you have not got matching languages somewhere in the settings. Would be easier to show this in the community.

      Liked by 1 person

  15. rhrs1987 said:

    Hello again Paul. I followed this guide but wasn’t able to commit cardinal sin in Trados 2015. Once all is set up, the excel glossary is enabled in Termbases, but nothing appears during translation. No terms appear in Termbase Viewer. It’s a .xlsx file with three colums (A, B, C and no header. I’m at a loss. Many thanks!

    Like

    • rhrs1987 said:

      I solved it somehow: created a new spreadsheet, copied-pasted columns from original excel termbase with the option you recommended and added to Studio. First time it wouldn’t open. Restarted Studio and voilà. Can’t explain why (?). Thanks for for making a great post!

      Like

      • Glad you resolved it without me having to do anything!!

        Like

  16. Hello Paul. Here I am again with a frustrating issue. I switched to a new computer with Win 10 and installed Trados 2015. I downloaded your exchange app for using an excel termbase. This time, after adding a new termbase, field for Termbase Language is always “(not especified)” and I can’t find a way for Trados to identify it. This is quite frustating since I simply can’t imagine the reason why, given that was working flawlessly in Win 7. Do you have any ideas or suggestions? Am I missing something? As ever, I would very much appreciate your advice on the matter.

    Like

    • Hello… first of all I’d recommend you ask these questions in the SDL Community as you’ll get more help than just me. There’s even a specific forum for apps.
      Next, are you using the latest version of Studio 2015 SR2 12.2.5195.7? Might be that with all the new languages available in W10 that an older version of 2015 might not recognise them all as Studio tries to use the OS for languages. I don’t recall when we enhanced 2015 for W10, but perhaps that’s the reason? The project might be seeing a different language to the one in your termbase so needs some help to set them.

      Like

  17. rhrs1987 said:

    Sorry for the late reply, Paul. I’m using Trados 2015 12.2.5082.0. I uninstalled and reinstalled both Trados and Excelerator. It is working properly now. Once again, can’t explain why. I’m not very fond of Windows, but perhaps that’s just years of being adapted to Win 7. In the project settings windows, it still says “not especified” in the fields for Termbase language, despite tthe plugin properly functioning. I was just about to take this to the forum you suggested and will ask there for future issues. As ever, many thanks.

    Like

  18. Jorge said:

    Hello Paul, do you have plans to upgrade your tool for Studio 2017? Thanks in advance

    Like

  19. Jorge said:

    Hello Paul, please disregard my question above, it seems I was copying the plug-in file into the wrong version folder.
    I will let you know if I have further problems with TermExcelerator. Thanks!

    Like

    • Hi Jorge,

      You do know you don’t have to copy the plugin anywhere? Just double click it and the plugin installer will guide you to the right place. It is of course upgraded for 2017 already.

      Like

  20. Steve Lee said:

    Hi Paul,

    Just dropped by to say you guys are awesome.
    My superiors and co-workers are all new to this game and just couldn’t seem to grasp Multiterm, so I just made their lives easier by installing TermExcelerator and voila! Problem solved, everyone happy.
    Thanks much!

    Cheers,
    Steve

    Like

    • Thanks Steve… glad this was a useful solution for you all.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: