Exporting labels to Excel from a project

Question

I have a small problem - I have to list all the labels of several large shared projects, so labels that are missing a translation are identified updated.

Now what I'm looking is something like Palle Agermark's label export job (http://www.agermark.com/2011/10/export-and-import-labels-for.html) but one that goes through one or more projects and pulls all the label IDs (regardless of label series) and values for three languages to Excel.

Can this be done, any pointers? :)

Answer 1

The simplest solution would be to go to the label source directly.

In AX 2009 the labels are stored in the application folder and named like like axSYSen-us.ald with the following parts:

1. ax - the file prefix
2. SYS - the label name, @SYS123 labels are stored there
3. en-us - the language specifier
4. ald - the label file extension

The files are in text format and can easily be imported to Excel or edited with an text editor.

You could import to Excel, one sheet for each label file, then combine the output in a separate sheet (in the same workbook) using Excel formulas.

In AX 2012 the labels are stored in the model database and the .ald files are created on startup.

Answer 2

This expands the idea of Jan B. Kjeldsen's last comment to get the labels from the project xpo file. The labels are saved in the xpo file in the following XML structure:

<Table:Record name="TmpSysLabel" xmlns:Table='urn:www.microsoft.com/Formats/Table'>
    <Table:Field name="Language">de</Table:Field>
    <Table:Field name="Label">Geplante Produktionsaufträge</Table:Field>
    <Table:Field name="Description"></Table:Field>
    <Table:Field name="LabelId">@SYS119128</Table:Field>
    <Table:Field name="SysLabelApplModule">0</Table:Field>
    <Table:Field name="recVersion">0</Table:Field>
    <Table:Field name="Partition">5637144576</Table:Field>
</Table:Record>

My first idea was to import this XML to Excel, but Excel does not play nice with column names in attributes. So instead I wrote an xsl that transforms the XML into something like the following:

<?xml version="1.0" encoding="UTF-8"?>
<labels>
    <record>
        <Language>de</Language>
        <Label>Geplante Produktionsaufträge</Label>
        <Description />
        <LabelId>@SYS119128</LabelId>
        <SysLabelApplModule>0</SysLabelApplModule>
        <recVersion>0</recVersion>
        <Partition>5637144576</Partition>
    </record>
</labels>

To do this transformation, the original XML has to be cleaned up a bit, replace all occurences of Table:Record with record and all occurences of Table:Field with field. Also add a root XML node labels. It should look like

<labels>
    <record>
        <field name="Language">de</field>
        <field name="Label">Geplante Produktionsaufträge</field>
        <field name="Description"></field>
        <field name="LabelId">@SYS119128</field>
        <field name="SysLabelApplModule">0</field>
        <field name="recVersion">0</field>
        <field name="Partition">5637144576</field>
    </record>
</labels>

Now for the fun part (at least for me because I had not much prior knowledge of xsl syntax), the xsl transformation file. I ended up with the following, which will produce the wanted XML using http://www.freeformatter.com/xsl-transformer.html:

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" omit-xml-declaration="yes" indent="no" />
<xsl:template match="/">
    <labels>
    <xsl:for-each select="Labels/*">
        <record>
        <xsl:for-each select="*">
            <xsl:element name="{@name}">
                <xsl:value-of select="." />
            </xsl:element>
        </xsl:for-each>
        </record>
    </xsl:for-each>
    </labels>
</xsl:template>
</xsl:stylesheet>

The resulting XML can then be imported into Excel using the Excel developer Tools for XML. The result will look like