In my previous post I showed how to handle XML comments in SDL Studio. Everything builds on my “standard” XML file with a very simple structure.

As you know life is sometimes not so easy and many people can’t create a XML file from their preferred tools. Most the time we actually get Excel files for translation. Recently there was the demand also to enforce length limitation in Excel sheets.

The good news is that this can be achieved quite easily, if you know how to do it. The ingredients for this are a XML file in the standard you would like to have and the Excel file itself.

The XML file has the known structure and one <Item> line would be enough.

Open the “Developer” tab in Excel.

Click on “Source”. The XML Source window opens on the right. Click “XML Maps…”.

Click “Add…” and choose the sample XML “Sample.xml”. Close with OK.

The XML structure is shown.

Now you need to grab the XML elements in the right window and place it on the corresponding cell in Excel.

So in this sample, drag “Ident” and drop it in cell A1. Grab “maxlength” and drop it in cell B1. Grab “<value> and drop it in cell C1. Grab “Comment” and drop it in D1.

The Excel should look like this:

Go back to the “Developer” tab and press “Export”.

Store the XML with any name you like.

Finished! Almost…

Open the XML to verify the content. The content from the Excel file is now in the XML.

Now you can process the file in Studio with the filter described in the last post.

If required, change the mapping before the import. My sample Excel has a different column for the target. So right-click on “<value> and choose “Remove element”. Then drag “<value>” and drop it on target.

The Excel looks now like this:

Import the translated XML file.

Done!

Here are the sample files: EXCEL_Sample

.over-and-out