One of my end clients recently introduced Sajan TM management system. As a result, instead of Trados TMs and TTX files which were a standard previously, I began to receive jobs as an Excel files, containing source segments and pre-translation (from 80% matches up) in separate columns. Additionally there’s a column with TM match percentage and some additional info/comments. And the Excel has a macro for propagating exact repetitions. Great. My task is to translate what’s missing and make sure the 100% matches strictly follow a mandatory glossary (in a separate Excel file). The general attitude was “we have a great new system, deal with it”. So I did. And since I’m sure I’m not the only one, I decided to share my solution.
Excel is a great tool for spreadsheets, but it really sucks for text editing. And checking for correct translation of glossary terms in Excel would take an eternity. Also formatting tags are just text, easy to miss or damage. It was obvious that I have to find a workaround for working with these files in a decent CAT environment. Fortunately the task came out to be quite simple and preparation takes just about 5-15 minutes, depending on number of sheets – because every chapter of a file for translation is inserted into a separateExcel sheet.
Take a look at the Sajan-exported file (click for full size):
If only 100% matches were pre-translated, I would just copy source and translation into a separate file, create a TM using this tool, then copied source column into target and imported selected columns for translation. However, with non-100% matches the task was more complicated… or so I thought. Eventually I found an easier and less time-consuming solution. The procedure was designed to import text into memoQ, but should work with any tool accepting Trados bilingual DOC format.
The procedure:
- Create a backup copy of the source file.
- Select and copy to new Excel file columns Match %, Basis, Source and Target from all Excel sheets.
- Remove Basis column.
- Move column Match % between Source and Target (see picture below).
- Select all three columns and copy them (Ctrl-C).
- Open Word.
- Click on a triangle below Paste icon and select Paste special.
- Select Non-formatted Unicode text. After pasting, turn on the option to display hidden characters.
- Insert an empty line before pasted text by pressing Enter at the beginning of the first line.
- Press Ctrl-H to open Search & Replace dialog.
- Click More to display additional dialog options.
- Select Use wildcards check box.
- In the Find field enter: ^9([0-9]*)^9
- In the Replace with field enter: <}\1{>
- With cursor in the Replace with field click Format > Style and select tw4winMark.
If you don’t have this style on a style list, open any bilingual Trados .doc or .rtf file and copy a line or two at the beginning of your file. Then try again – tw4winMark style will be on styles list. - Click Replace all.
- In the Find field enter: ^13
- In the Replace with field enter: <0}^13{0>
The field must still have tw4winMark style. - Click Replace all.
- Remove empty first line and any additional text pasted in step 15 (if any).
- Save the file as Word 97-2003 (.doc) or .rtf file.
- Start memoQ (or use other CAT tool).
- Create a project in memoQ, add document saved in step 22.
- Open the file for translation, then click Format > Run regex tagger.
- In the Regular expression field enter: {.*?}
- Click Add.
- Click Run tagger now (click OK in the warning dialog).
- Translate/review, importing Excel glossary into a project term base.
If no review of 100% matches is needed, you can sort the file by match percentage, select all 100% matches and confirm them in one go by pressing Ctrl-Enter. - When finished, open original Excel file again.
- For all sheets select Source column, copy to Target column. Save.
- Open the Excel file in memoQ importing only target columns for each sheet (Add document as).
- Pre-translate file, perform QA.
- Export finished file.
Of course the procedure creates some additional overhead, but at the same time it makes it possible to work with received files in a decent environment with all the features of a modern CAT tool, including QA on tags. Of course you can also save the file as XLIFF to perform an additional QA in an external tool, like Xbench or CheckMate.
