Translating multilingual Excel files in memoQ

Some weeks ago on a Friday, in the late afternoon, I received one of those typical Friday project inquiries: a request for a fast response on whether I would like to translate some 15 to 20 Excel files distributed in three folders, with file names redundant between the folders and over half the 50,000 source words already translated. My translations were, of course, to take the previous work into consideration and remain consistent with it. No translation memory resources were available. Fortunately for my blood pressure, I was offline that afternoon until after business hours. When I saw the request later that evening, I considered what sane approach there might be to such a project, and when none occurred to me at the time, I wrote a note to the project manager requesting more information about the source data, received no response and forgot the whole business as the usual Friday nonsense.

About a week later, while I was engaged in something completely different, it occurred to me that it would have been a fairly straightforward matter to translate the remaining text scattered through those files and build a reference translation memory from the existing translations. In fact, I could even use the available translations from other languages as references in a preview. How? By using a multilingual filter option that Kilgray added to memoQ version 6.2 (with build 15).

Finding that option is not exactly intuitive. I had heard about it but had not followed the discussions closely in the online lists, nor could I remember it from the online demonstrations I had viewed in recent months. But I knew that it worked with Excel files, so I started to import such a file and looked for the proper settings to import the source and target columns. And found nothing.

Fortunately, I used to be a software developer, so I put on my old developer's thinking cap and considered how I might best mystify users with a new feature. Aha! Name the feature something completely different! So I looked again at the list of import filters for my Excel file and found a likely candidate, the multilingual delimited text filter. (To use this filter, you must Import with options.)

The first page of the settings dialog for that filter offers Excel as one of the base formats to import:

The columns can be specified by marking the Simple bilingual configuration option, or with somewhat less confusion by examining the options on the Columns tab of the dialog. For the following test file with English source text and German as the desired target language

I used the following settings for the import:

After a little experimentation, I found that I could specify the third language (Portuguese) as a translation even though it was not a language indicated in the project. (Additional target languages are only possible with the PM edition of memoQ, but this information can be designated as a comment if needed in the Translator Pro edition.) This added the Portuguese translations (where available) to the preview in my working window:

Some odd property of the import filter in the version of memoQ tested caused the source text to be copied to the view of unpopulated translations of the project's target language in the preview, but that is of no real consequence. The preview, unlike a typical preview of an Excel file, bears no resemblance to the layout of the source file, but is instead organized by the source text grouped with other specified columns.

Considering how often I have encountered Excel files and other sources structured like this in the past decade, I would say this is probably one of the most useful filters that has been added to memoQ recently. More complex data structures may require cell background colors to be used to exclude unwanted parts of a spreadsheet (colors can be added while configuring the import). It's a shame that the current version of the filter doesn't support ranges or conditions for exclusion, but perhaps that will come later.

Making a translation memory from the existing translations in the file (which were locked and confirmed upon import in the example shown above) is a simple matter of using the command Operations > Confirm and update rows... and selecting the appropriate options. For the example shown here, selecting the locked rows would write all these to the primary translation memory:

Kilgray has a blog post and a recorded webinar (47 minutes long) with further details about using this filter. They state that "This webinar was designed for language service providers and enterprise users managing multilingual projects." However, given the frequency with which many freelancers encounter such formats and their desire to use other language information, comments, context data, etc. in their translations, I think this feature is just as relevant to freelance translators.

Update 2013-07-25: After a series of recent tests involving imports and segmentation, I wanted to see how the multilingual Excel filter would import data in which individual cells contain multiple sentences or line breaks. Theoretically the segments should correspond to the cell structures, but would they in fact? I decided to import one of my Excel files that I use for segmentation demos. To keep all the content of a cell in one segment with the regular Excel filter, I have to use a "paragraph segmentation" ruleset and set "soft breaks" as inline tags in the filter's import settings. But the default settings of the multilingual Excel filter achieve the same result:

This showed me that the "multilingual" filter might in fact save me time and trouble for importing files from certain customers where I want to avoid segmentation inside the cells altogether. And of course, the multilingual filter is an obvious quick way to load data from an Excel file and, as mentioned above for partial data, send it to a TM - a process which used to involve saving as a CSV file from Excel, worrying about saving as UTF-8, etc. That process might not even work with the test file shown here (I'm not really inclined to try it).