Tuesday, June 30, 2015

The New Bilingual Excel File Type in Studio 2015

With the new Bilingual Excel file type in Studio 2015, we can extract text from a specific column for translation and have Studio insert the translated text into a different column in the target file. No more cutting and pasting!

Note: File type and filter are sometimes used interchangeably, so Bilingual Excel file type and Bilingual Excel filter refer to the same thing in this post.

What It Does

Here's a typical use scenario.


The regular Excel file type produces the following Studio file, with all the content extracted for translation.





The resulting target file of the above would be an exact copy of the source file, but with all the text replaced by the translations.

With the new Bilingual Excel file type, however, only the text I need will be extracted for translation.



And the resulting target file will have the translations inserted in Column F, while the rest of the text remains intact, as shown below.



How to Use It

First things first: For any projects created with a pre-2015 version of Studio, the Bilingual Excel file type will need to be added manually by clicking on the "Additional installed File Types exist" link, and checking the new file type checkbox, as shown here.



Are the files in the correct order?
A key thing to keep in mind to make this work is that Studio will process your Excel file with whatever filter it finds first, so if the Bilingual Excel file type comes before the regular Excel file type in the list, the Bilingual Excel file type will be used to process your file, but if it comes after the regular Excel file type, then the regular Excel filter will be used. So, to make sure Studio uses the Bilingual Excel filter, you can either make sure it comes before the regular Excel filter in the list or you can disable the regular Excel file type.

Go to File - Options - File Types





Important note: There are two places where you can make this change.

1. If you make it by going to Options - File Types (as shown in the screenshots above), this change will affect future projects and single-document flows, but not existing projects.

2. If you make it by going to Project Settings - File Types, the change will only affect your active project.

This gives you the ability to enable the Bilingual Excel file type only for a specific project without changing how Studio handles Excel files in general.

Once the file type is enabled, it's time to look at the settings. For my example above, I set the source column to E and the translation column to F and chose to confirm any existing translations that may already be in column F.


After clicking OK, you would process your file as usual. Keep in mind that any changes made to Project Settings or Options will be applied to files that are processed after the changes, not to existing files, so if you've made this change after a file had been added to a project and prepared, you would need to remove the file, add it and prepare it again for the new settings to go into effect.


Limitations
Note: This section has been edited to clarify how cell and text-level formatting are handled.
A limitation of this first version of the Bilingual Excel filter is that text-level formatting (e.g. bold, italics, font, color applied to individual words or phrases) is not recognized. Cell formatting, however, can be preserved by unchecking the "Preserve Target Style" checkbox in the file type settings, as shown below. Note that in this example, column C has been marked as containing comments.


This is the source file to be used in this example. Rows 2 and 5 in Column A have cell formatting applied to them. For the rest of the cells, formatting was applied at text level.



Here's the above file, processed with the bilingual file type and with the settings shown earlier. Note that there are no formatting tags in the source column.

Now let's have a look at the resulting target file.



So, as we can see, formatting that applies to the entire cell is transferred over to the target column, but since we have no tags to help us indicate the formatting of individual words or word groups, text-level formatting cannot be preserved.

Let's compare now how the same text looks when processed through the regular Excel filter.


Here we get full control over text formatting tags, so it's easy to duplicate source formatting.

Another limitation of the bilingual file type is that there are no embedded content settings for this file type. For most regular jobs, however, this may not even be an issue.

In conclusion, choosing the right Excel file type to use will depend on the specific use case at hand. While heavily-formatted files may still call for the regular Excel filter, there will be other files with little or no formatting that will be perfect for the new Bilingual Excel filter. 








3 comments:

  1. Just a quick note: in your example, formatting doesn't work because settings don't allow it. Should you disable the "Preserve target style" checkbox, then the formatting from source cell will also be found on the target cell. However, this happens only if the formatting is done at cell level (for example, if you paste a sentence from Word which is written in bold, then it will not work).

    ReplyDelete
    Replies
    1. Thank you Raul, you're absolutely right. I'll add this clarification to the post with an example, as cell formatting can definitely be preserved.

      Delete
    2. You're welcome, I am glad I could help.

      Delete