Steps to convert Excel Sheet to XLIFF file

As we usually get i18N translations from 3rd party companies (eg: SDL) in an excel sheet, but AEM accepts only .xliff. So, it is hard to add all the translations in the i18N dictionary manually as we have hundreds of keys.

So, in that case, we can use Microsoft Excel to convert this excel sheet into the xliff format file.

 

In AEM we can import or export one language at a time so whenever we get the excel sheet from SDL, we need to create a new Sheet in the excel with 3 columns 1- id, 2- i18N String 3-Translated language (vi_VI) as shown below.

 

 

Once you create a sheet with these 3 columns, there is an out of the box MS Excel functionality to convert this .xlsx to .xml format. That is called the Developer Tab. By default, if this tab is unavailable you can enable this by using steps mentioned in this article.

 

Once the Developer Tab is enabled follow the below steps to convert.

On the Developer tab, in the XML group, click Source to open the XML Source task pane.

   

On this screen click on the XML Maps option highlighted in the above screenshot.

 

Then here select the existing base.xml file. Which looks like the below screenshot.

base.xml

 

It basically has a header for the xliff file and some examples of how this xml body should be. So, that Excel can read this file and give you the option to replace the values by matching the columns.  

 

So, once you import the file (base.xml) on the right side you would see options (id, source/value, target/value). Now we need to match the rows on the right side with the columns in the excel sheet by dragging on to the excel sheet as shown below.

 

For Example: id to id, source/value to a string, and target/value to i18n translated language.

 

Once the mapping is done, again go to Developer Tab and then Export this document here you can add xliff extension.

 

Now we have successfully converted Excel file to xliff file as shown below.

Output.xml

 

With this output xml file, we still need to do some changes in order to work as an xliff file.

Below are the changes that we need to do on output.xml file

 

1.     Replace <source> with <source xml:lang="en"> ... here en is the i18n key

2.     Replace < target> with <target xml:lang="fi_fi"> ... here fi_fi is the destination translated language.

3.     Finally, we need to replace the <file>, <header>, <?xml>, <xliff> tags with as shown below in the output.xml file.

 

<?xml version="1.0" encoding="utf-8" ?>

<xliff version="1.1">

  <file

    original="/libs/cq/i18n/en_us"

    source-language="en"

    target-language="fi_fi"

    datatype="x-javaresourcebundle"

    tool-id="com.day.cq.cq-i18n"

    date="Fri Aug 07 16:26:27 MST 2020"

  >

    <header>

      <tool

        tool-id="com.day.cq.cq-i18n"

        tool-name="Adobe Granite I18N Module"

        tool-version="5.5.16"

        tool-company="Adobe Systems Incorporated"

      />

    </header>

 

After updating these changes, we will get the working xliff file.

 

So using the OOTB translator we can import this file.

 

AEM i18N Translator URL: Navigate to this URL http://localhost:4502/libs/cq/i18n/translator.html

 

And then you need to select our BW i18N dictionary in the Dictionaries dropdown. Then click on the Import option and click on XLIFF Translations so that you can import the xliff which we created.