| Pages in topic: [1 2] > | Bilingual Excel files with max length Thread poster: Sarandil
|
|---|
Hi,
I'm new to Trados, using Trados 2009, and the client that I work with most frequently sends Excel files. These contain columns for "Identifier", "Old Reference", "Reference", translation, "Max Length" and "Change Type" (translation or review). The translation column regularly already has a translation implemented that just has to be updated.
Often the sequence of columns when completed is repeated in a new set of columns for another version of the text, in which some Excel... See more Hi,
I'm new to Trados, using Trados 2009, and the client that I work with most frequently sends Excel files. These contain columns for "Identifier", "Old Reference", "Reference", translation, "Max Length" and "Change Type" (translation or review). The translation column regularly already has a translation implemented that just has to be updated.
Often the sequence of columns when completed is repeated in a new set of columns for another version of the text, in which some Excel cells in the translation column usually has to be localised and others doesn't. The files are sometimes large, up to some 10,000 words, and sometimes tiny, like 100 words, and sometimes come several times a day with a deadline just some 2 hours away.
The project the Excel files are part of is yearly, consists of some 100,000 words and has, more rarely, also Word files and itd files.
My question is: How can I best work with these files using Trados (first and foremost a Translation Memory and a TermBase)? I'm looking for a good routine. I have problems importing the source and target text into Trados from the Excel files in a swift manner, and I have no idea of how I can get it to work with the character max length for each Excel cell that I often have to respect (which is crucial).
I would be extremely thankful for any help that could be offered. Thanks in advance.
Cheers,
Sarandil ▲ Collapse
| | | |
I don't have much experience with Trados + xls, so I won't comment on that.
I do have an idea for the character length restriction. I take it that the files have a column with the character length limit, which is different for each cell. You probably won't be able to apply this limit with Trados, but it should be fairly easy to do in Excel after doing the translation.
Let's suppose your translation is in column A and the character limit is in B. Write this in C1:
Code:
| =IF(LENGTH(A1)>B1,"Too long, "&LENGTH(A1),LENGTH(A1)) |
|
...and copy it to all cells in C. (My Excel is not English, I hope I got the formula right.)
You can also use Format/Conditional formatting to make the cells have a different colour based on whether they exceed the limit or not.
I know this is not as convenient as Trados showing you the limit right away, but I'd be surprised if there was a fast and convenient way of "importing" the character length limit along with each cell.
If you regularly receive translations along with the source texts in Excel and need to import them into a TM efficiently, here are some solutions:
http://www.proz.com/forum/wordfast_support/172297-creating_tm_from_excel_file_in_wordfast.html#1518128 ▲ Collapse
| | | | Adam Łobatiuk 
Poland
Local time: 08:45
Member (2009)
English to Polish
+ ...
First of all, I'm afraid the only way to work in this situation would be to copy the entire source column (is that "Reference"?) to a Word file, translate that while consulting the opened Excel file, and then paste the translations into the Excel file. You can use the old source and translations strings to build a TM and use that.
Do you have SDL Trados 2007 with your Studio 2009? If so, I would recommend using Tag Editor, as it shows you the number of characters in the current tran... See more First of all, I'm afraid the only way to work in this situation would be to copy the entire source column (is that "Reference"?) to a Word file, translate that while consulting the opened Excel file, and then paste the translations into the Excel file. You can use the old source and translations strings to build a TM and use that.
Do you have SDL Trados 2007 with your Studio 2009? If so, I would recommend using Tag Editor, as it shows you the number of characters in the current translation in the status bar. Studio doesn't seem to have that. However, both Studio and Tag Editor have QA modules where you could specify that the translation must not be longer than the source string. It is not the same thing, because source strings could be shorter than the limit, but it would work as a safety measure. If you need more room, you can check the Excel file for the actual limit specified for that string. ▲ Collapse
| | | | Walter Blaser
Switzerland
Local time: 08:45
French to German
+ ...
| There are two types of length check | Aug 21, 2010 |
However, both Studio and Tag Editor have QA modules where you could specify that the translation must not be longer than the source string. It is not the same thing, because source strings could be shorter than the limit, but it would work as a safety measure. If you need more room, you can check the Excel file for the actual limit specified for that string.
Let me precise that Studio QA has two different types of length check functions:
1. Check that the length of the target does not exceed the source by a given percentage (this is what you mentioned)
2. Check that the target segment does not exceed a specified fixed length in characters (regardless of the length of the source).
It is therefore very flexible in that respect, a feature which is very useful when translating strings used in a software application.
The only thing that is still missing is, as you correctly mentioned, the display of the current cursor position in the status bar of Studio's Editor. If you believe this feature should be available, I encourage you to vote for the idea her:
http://ideas.sdl.com/ideas/detail.asp?i=3006
Walter
| | |
|
|
|
Sarandil
Local time: 08:45
TOPIC STARTER | Excel --> Trados importing & length restrictions | Aug 22, 2010 |
Many thanks for taking the time – much appreciated!
First, I use Trados Studio 2009, and I also have Trados Studio 2007 and all the other applications that come with it, like TagEditor, WinAlign and so on. Also, to answer Adam's question, "Reference" is the source column, yes.
FarkasAndras: Thanks for the tip with the column cell character length for Excel. Unfortunately, when I tried it in my Excel 2007 I get an error message "The formula you typed contains an error"... See more Many thanks for taking the time – much appreciated!
First, I use Trados Studio 2009, and I also have Trados Studio 2007 and all the other applications that come with it, like TagEditor, WinAlign and so on. Also, to answer Adam's question, "Reference" is the source column, yes.
FarkasAndras: Thanks for the tip with the column cell character length for Excel. Unfortunately, when I tried it in my Excel 2007 I get an error message "The formula you typed contains an error" when I paste it into a cell after double-clicking the cell. When I just click once on the cell I can paste it, but nothing happens (perhaps I need to do something more to get the result show up?).
There is some functionality built in to the files that I receive to provide a pop-up when the limit is exceeded (I'm an utter amateur with Excel), but that rarely works properly.
When I research the Conditional Formatting of Excel I get plenty of functions, but it seems that all of them are dependant on the values of the cells being numbers. Is there a way to make them work for character count?
Through the cross reference supplied I found my way to this topic, which contains some tips on macros and similar stuff to import the Excel material into a TM: http://www.proz.com/forum/sdl_trados_support/20159-importing_a_tm_in_excel_format.html If I got it right it's supposed to work with both source and some target text present in the Excel file from the outset, so hopefully it would be good for me.
Adam: Thanks a lot for the tip to copy/paste with Word (and use Translator's Workbench there with a TM, I suppose). It seems to be a working option in my case, even though I would have to check the Excel file for the max limit, as with any program where the length restriction is not taken into account, I guess. However, I fail to see how the advice to use TagEditor connects up with this, could you please explain?
It sounds interesting with the function that Walter mentioned, to make Trados show a warning when the target text exceeds the source by a given percentage. I'm searching in Trados Studio 2009 --> Tools --> Options --> QA Checker 3.0 --> Length Verification, but I can't see how it's done there. Could you please provide some more info there?
I feel comfortable with the layout and functionality of Trados Studio 2009, so it would be nice to get to work with these files there. By the way, just as the types Walter refered to, they are within the field of software localisation. ▲ Collapse
| | | | | Excel macro & Trados | Aug 22, 2010 |
Walter Blaser wrote:
Let me precise that Studio QA has two different types of length check functions:
1. Check that the length of the target does not exceed the source by a given percentage (this is what you mentioned)
2. Check that the target segment does not exceed a specified fixed length in characters (regardless of the length of the source).
I got this type of Excel file translation jobs from many Japanese electronic device manufacturers (to localize the operation menu screen). They use Excel Macro button to recheck word length after translations by using Excel function expression strings and links to each worksheet. Sophisticated calculation can be designed by yourself. Trados 2006 and 2007 can also suffice this mission in translation steps, not word length counting.
Walter Blaser wrote:
The only thing that is still missing is, as you correctly mentioned, the display of the current cursor position in the status bar of Studio's Editor. If you believe this feature should be available, I encourage you to vote for the idea her:
I support with this idea. I did vote for it.
Best regards,
Soonthon Lupkitaro
| | | | Walter Blaser
Switzerland
Local time: 08:45
French to German
+ ...
| QA functions for length checking | Aug 22, 2010 |
It sounds interesting with the function that Walter mentioned, to make Trados show a warning when the target text exceeds the source by a given percentage. I'm searching in Trados Studio 2009 --> Tools --> Options --> QA Checker 3.0 --> Length Verification, but I can't see how it's done there. Could you please provide some more info there?
The one with the fixed length check is at 'Tools - Options - Verification - QA Checker 3.0 - Length verification'. Check the second option and enter the maximum length, that's it.
The other one you can use if the target length is not fixed, but should not be longer than the source is at: '... Verification - Segments Verification'. Check the option 'Check for target segments which are longer by %' and enter '0' in the percentage field.
Both options work fine, but be aware that they do not issue an immediate acoustical warning while you are typing, the check is done at the time you confirm the segment. You will then see the error sign appearing in the status column with an explanation such as "Target segment is longer by 32 %".
Walter
| | | | | Here's how you do it | Aug 22, 2010 |
Sarandil wrote:
FarkasAndras: Thanks for the tip with the column cell character length for Excel. Unfortunately, when I tried it in my Excel 2007 I get an error message "The formula you typed contains an error" when I paste it into a cell after double-clicking the cell. When I just click once on the cell I can paste it, but nothing happens (perhaps I need to do something more to get the result show up?).
There is some functionality built in to the files that I receive to provide a pop-up when the limit is exceeded (I'm an utter amateur with Excel), but that rarely works properly.
The first line of attack would be to find out why the built-in length tester doesn't work as intended, and to find out if there is a general length rule (translations can't exceed 130% of source etc.)
If there is a general rule that applies to each and every cell in each and every file to be translated, just use that in Studio.
If there isn't and the client-supplied length checker is a dead end, use this xls with my formula:
http://www.mediafire.com/?y2y4f95o88fgqcd
As you can see, the offending cells are red to make them stand out, and they show you how many characters you need to shave off.
Do the translation (with Studio straight from the Excel file after hiding the columns you don't have to translate, or via Word), then copy-paste the translation and the limits into the tester xls, make any necessary corrections and copy-paste to the deliverable file.
Conditional formatting is indeed based on numbers but it's easy enough to produce a number to base it on, as you can see in the xls.
The reason why you couldn't get it to work is probably that you have a Swedish version of Office and you were trying the English formula. The formulas in saved xls files work regardless of what version they were saved with, so my saved Hungarian formulas should work on your Swedish Excel. Simple, eh?
[Edited at 2010-08-22 11:46 GMT]
| | |
|
|
|
Walter Blaser
Switzerland
Local time: 08:45
French to German
+ ...
| It does work as intended | Aug 22, 2010 |
The first line of attack would be to find out why the built-in length tester doesn't work as intended, and to find out if there is a general length rule (translations can't exceed 130% of source etc.)
Didn't you read my post, which I made about two hours before yours?
I explained in detail how exactly this is done in Studio
Walter
| | | |
Walter Blaser wrote: The first line of attack would be to find out why the built-in length tester doesn't work as intended, and to find out if there is a general length rule (translations can't exceed 130% of source etc.) Didn't you read my post, which I made about two hours before yours? I explained in detail how exactly this is done in Studio Walter
I did, and took it into account in my post, which you would know... if you read my post with attention - or the entire thread, for that matter.
The whole point beginning with post 1 is that there is a separate length limit for each cell. There is no general rule you could apply with Studio, or at least this is the scenario I was posting about.
There could be cells in each file that have no limit, and there could be cells with totally arbitrary limits, as is the way in software localization. Different on-screen elements offer differing amounts of space, so length limits could be totally unpredictable.
By the way, the built-in length tester I was talking about is obviously not Studio's length checking feature, but the client's formula in the xls... you seem to be misunderstanding that as well.
| | | |
On the one hand, in English-Hungarian translations, as an example, the target segment is almost always longer than the source segment, so a limit equal to the length of the source segment could be hardly kept by the translator. On the other hand, it is not optimal either, if this limit is allowed for each segment to be exceeded by a given percent, let's say by 20%, because the really allowable number of extra characters in the target segment may change from segment to segment: sometimes only one... See more On the one hand, in English-Hungarian translations, as an example, the target segment is almost always longer than the source segment, so a limit equal to the length of the source segment could be hardly kept by the translator. On the other hand, it is not optimal either, if this limit is allowed for each segment to be exceeded by a given percent, let's say by 20%, because the really allowable number of extra characters in the target segment may change from segment to segment: sometimes only one extra character may spoil the layout of a whole translated page, while at some segments even 30 extra characters may not cause any problems.
Therefore, it would be useful, if the number of allowable extra characters was stated and kept from target segment to target segment, instead of stating and keeping an overall percent.
Hopefully, the method below provides an opportunity (there can be more) to consider these limits until an on the fly character counter will be added to Studio 2009.
Let’s suppose the source segments are in column A, target segments are in column B (some segments have been translated, others not), the maximum number of characters in a target segment is in column C. Write in E1 as many ~ characters as the maximum in column C (considering all segments) and write ß in front of them (without a space). For example, if the maximum number in column C is 10, then write in E1: *ß~~~~~~~~~~*. (* is not part of any expressions mentioned throughout this thread). (Of course, the maximum will be higher, this is for illustration only). The two characters, ß and ~ should be chosen from the characters that are not in the original text (check this).
Now write in D1: *=IF(B1="";LEFT($E$1;C1);B1)*, move the mouse over the right bottom corner of D1, and when the "filling plus" appears, left-click on it twice.
„Winalign” column A and D, upgrade the tm into sdltm, and, in Editor view, open the Excel file containing column A only, with the new sdltm. When the "translations" on the target side appear, the segments that have not been translated appear like this: ß~~~~~~~~~~~~~~
Go to Edit/Replace where „Find what:” *ß*, „Replace with:” *line feed* (copy/pasting „line feed” into the Replace box: from Quickinsert toolbar insert line feed anywhere into the target side, select it, and copy/paste it with Ctrl+C/Ctrl+V, then delete this line feed character from the target side), and finally click on „Find next”, „Replace” then „Replace all”.
During translating, you will always see how long the target segment can be.
When you are ready with the translation, delete excess line feed and ~ characters in two replacement steps: go to Edit/Replace, and first „Find what”: *line feed~*, then „Find what”: *~*, both times „Replacement with” box should be left blank (before the first replacement step delete the content of this box because "line feed" from the previous steps may be left in the box even if it seems to be blank).
Do not forget to confirm every segment that has changed.
As I have mentioned, there can be more ways to achieve the task under discussion. For example, there exist ’Always on top' windows with adjustable transparency. Constructing in this window a character scale with vertical gridlines and lying it on the target side, you can get a useful tool for this or any other applications where there is no ’as you type’ character counting. ▲ Collapse
| | | | | Problems with the method I have suggested | Aug 23, 2010 |
I now realize that the "length" of the characters are different, so for example, "mmmmm" is not equal with "iiiii", although both have 5 letters. If the standard is that the allowable extra length is measured in "spaces" (for the given character set), then using ~ as a filler character is a bad choice. So ~ should be substituted with another character with the length of a "space". Searching.
| | |
|
|
|
Jaroslaw Michalak
Poland
Local time: 08:45
Member (2004)
English to Polish
SITE LOCALIZER | It gets worse... | Aug 23, 2010 |
István Hirsch wrote:
I now realize that the "length" of the characters are different, so for example, "mmmmm" is not equal with "iiiii", although both have 5 letters. If the standard is that the allowable extra length is measured in "spaces" (for the given character set), then using ~ as a filler character is a bad choice. So ~ should be substituted with another character with the length of a "space". Searching.
It is even more complicated than that - if the characters are not represented by bitmap, but are rendered from vector fonts, the differences are quite dynamic and depend on resolution, size, scaling and many other factors.
Even for bitmap fonts my clients use complex Excel formulas that calculate the screen sizes of the strings based on the particular fonts and screen capacities. Naturally, this goes beyond capabilities of any CAT tool...
| | | | | Font spacing | Aug 24, 2010 |
Well, that's all true, but clients tend to just give us character count limits. If they want to enforce real "space taken up" limits, they'll have to come up with software that can enforce them. Until then, we're fine with our primitive tools:)
By the way, István, you'd only need to find and set a monospace font in Studio for your system to work... I'd imagine it has one.
To be honest though, I think that convoluted solution is more trouble than it's worth. For example, it "c... See more Well, that's all true, but clients tend to just give us character count limits. If they want to enforce real "space taken up" limits, they'll have to come up with software that can enforce them. Until then, we're fine with our primitive tools:)
By the way, István, you'd only need to find and set a monospace font in Studio for your system to work... I'd imagine it has one.
To be honest though, I think that convoluted solution is more trouble than it's worth. For example, it "contaminates" TMs with junk material. I'd just do the translation normally, generally trying to be concise, and then run the target text through a tester like the xls I created and posted.
[Edited at 2010-08-24 07:32 GMT] ▲ Collapse
| | | | Sarandil
Local time: 08:45
TOPIC STARTER | Excel --> Trados importing & length restrictions | Aug 25, 2010 |
FarkasAndras wrote:
The first line of attack would be to find out why the built-in length tester doesn't work as intended, and to find out if there is a general length rule (translations can't exceed 130% of source etc.)
Unfortunately, the length restrictions just differ randomly (based on the available space on the screen or in the menu or whatever). I actually have no idea why the built-in length checker of the Excel files I receive doesn't work as it should, possibly it's just poorly configurated by the client (they aren't always very careful with these things, sometimes the length restriction is even inaccurate).
Just to describe the built-in length checker -- it generates a pop-up message titled "String Length Warning" with the message "The string exceeds its maximum length. Please shorten it before proceeding. // Continue? Yes No Cancel Help". With time, I've just come to use these as a guideline to double check what it says in the length restriction field.
Also, with my limited knowledge of Excel, I've always had to check the character count of the individual Excel cell by copy/pasting to Word and run a character count there. But I suppose there has to be a way of counting the cell characters in Excel?
I checked the xls created by FarkasAndras (http://www.mediafire.com/?y2y4f95o88fgqcd), and it seems very useful and handy, so I will definitely go ahead with that, many thanks! If I combine that tool with applying the warning in Studio when the target exceeds the source with 15 % or something, I could be quite well off.
By the way, in Trados Studio 2009 --> Tools --> Options --> QA Checker 3.0 --> Length Verification there is an option "Check if target segments are within file specific limits". Perhaps this could be used for the files in question?
FarkasAndras wrote:
The reason why you couldn't get it to work is probably that you have a Swedish version of Office and you were trying the English formula. The formulas in saved xls files work regardless of what version they were saved with, so my saved Hungarian formulas should work on your Swedish Excel.
I actually use an English Excel 2007, so the English formula should work, but I guess that's a redundant topic now that I have the xls.
It looks interesting with the (for me) very sophisticated method suggested by István, but I will try to get a grip on the xls + Studio warning method to start with. I also have to find out the best and swiftest way to get the pre-filled text in the target column of the Excel sheet into Studio (starting with methods suggested in this thread, as I mentioned in my previous post: http://www.proz.com/forum/sdl_trados_support/20159-importing_a_tm_in_excel_format.html).
Also, an under-signing of FarkasAndras' latest statement on the character count limits -- they are just rough limits from the start, and if we as translators respect them, we've done our part of the job.
| | | | | Pages in topic: [1 2] > | To report site rules violations or get help, contact a site moderator: You can also contact site staff by submitting a support request » Bilingual Excel files with max length | Trados Business Manager Lite | Create customer quotes and invoices from within Trados Studio
Trados Business Manager Lite helps to simplify and speed up some of the daily tasks, such as invoicing and reporting, associated with running your freelance translation business.
More info » |
| | Wordfast Pro | Translation Memory Software for Any Platform
Exclusive discount for ProZ.com users!
Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value
Buy now! » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
Translation news related to SDL Trados
