Tag Archives: Bing

Automated Language Translation With Powershell

This is an addition to a previous blog where I used Powershell to update multiple language/culture text resource files within a WPF application. Here is the previous blog.

While I was working on the previous solution using Powershell to generate resource files using data from Excel it occurred to me that I may have situations where I’ve got my default language key/value pairs filled in but I may not have all of my translations completed.  What do I do about those empty cells?  I thought it would be better if the script filled in the blanks for me.  Yes, the translation may not be 100% accurate but it would allow me to test the application and get a good feeling about how the translated text would fit within the given user interface.

I still want the Excel spreadsheet to be the “single source of truth” so I don’t want to automatically update that data.  I want to keep the blanks in it so I know what still needs to be translated and verified.  The only thing I want updated is the resource files. 

To do the translation I looked up how to utilize the Bing Translator’s web services.  You can find out more on the Bing developer’s resources here.  To work with the web services you need an application ID which you can get here.  The Powershell script below uses an embedded application ID to complete the web service call.

Ok, now to the details.  In addition to the code in the previous blog, I added a “Translate-Table” function that looks at the dataset that is created from the Excel spreadsheet.  When it finds a missing value, it makes a call to the Bing Translator and passes in the value from the default language.  When it receives the value back, it inserts the value into the table.  Again, the Excel spreadsheet is not updated.

# function Translate-Table

# Scan the dataset extracted from Excel and look for cells that

#  are blank and therefore need to be translated.  When a blank

#  cell is found, call the Bing (Microsoft) Translator, pass the

#  from/to languages ID's and the terms to Bing and get the 

#  translated text back.  The translated text is then stored in the

#  dataset.

function Translate-Table {

    param ( [Data.DataSet] $excelData, $translationList )

 

    $count = [string]$translationList.Length

    

    foreach ($row in $excelData.Tables[0])

    {

        for ($index = 2; $index -le $count; $index++) {

            

            # We have to check for DBNull here to see if the term is present or not

            if ( $row[$index] -eq [DBNull]::Value ) {

                $fromText = [System.Web.HttpUtility]::UrlEncode($row[1])

                $fromLang = $translationList[0]

                $toLang = $translationList[$index - 1]

    

                [string] $requestString = "http://api.bing.net/xml.aspx?AppId=$appId&Query=$fromText&Sources=Translation&Version=2.2&Translation.SourceLanguage=$fromLang&Translation.TargetLanguage=$toLang"

        

                # Create and initialize the request.

                [System.Net.HttpWebRequest] $request = [System.Net.HttpWebRequest]::Create($requestString)

                    

                # Send the request; display the response.

                [System.Net.HttpWebResponse] $response = [System.Net.HttpWebResponse] $request.GetResponse()

            

                # Load the response into an XmlDocument.

                [System.Xml.XmlDocument] $xmlDocument = New-Object System.Xml.XmlDocument

                $xmlDocument.Load($response.GetResponseStream())

    

                # Add the default namespace to the namespace manager.

                # psbase: this means "return the original object".  This is neccessary because PS sometimes interprets methods and properties

                #  out of objects.  When this is the case, psbase gives you access to the root object and all of the original methods and props

                [System.Xml.XmlNamespaceManager] $nsmgr = New-Object System.Xml.XmlNamespaceManager $xmlDocument.psbase.NameTable

                $nsmgr.AddNamespace("api", "http://schemas.microsoft.com/LiveSearch/2008/04/XML/element")

    

                [System.Xml.XmlNodeList] $errors = $xmlDocument.DocumentElement.SelectNodes("./api:Errors/api:Error", $nsmgr)

    

                if ($errors.Count -gt 0)

                {

                    # There are errors in the response. Display error details.

                    Write-Output "Error retrieving translation data"

                }

                else

                {

                    $root = $xmlDocument.DocumentElement

                    

                    # Add the Translation SourceType namespace to the namespace manager.

                    $nsmgr.AddNamespace("tra", "http://schemas.microsoft.com/LiveSearch/2008/04/XML/translation")

            

                    [System.Xml.XmlNodeList] $results = $root.SelectNodes("./tra:Translation/tra:Results/tra:TranslationResult", $nsmgr)

            

                    $row.item($index) = $results.item(0).InnerText

                }

            }

        }

    }

    

    $excelData

}

 

A couple of additional variables are used to provide the application ID and the list of Bing language specifiers used to tell Bing what languages to translate from and to.

# These are the languages for each of the DataSet Translations - 

# This is for use within the Bing auto translation feature

# If there are cells in the Excel spreadsheet that are empty, they will be filled

#  in with a call to Bing's translation service.

$translationLanguages = @("En", "Es", "Ja")

$appId = "Insert your Bing Application ID Here"

 

Lastly, the call to “Translate-Table” is made immediately before the loop that generates the resource files.

# Perform translations using Bing Translator.  Only empty cells

#  in the non-default language columns will be translated

#  Note: This DOES NOT modify the Excel Spreadsheet.  It does the

#  translation and updates the resource files ONLY.  This step

#  is NOT required to generate resource files.  It is here to

#  "fill in the blanks" when the source spreadsheet does not 

#  have all cells filled in.  If you do not wish to use it,

#  comment the next line.

$excelData = Translate-Table $excelData $translationLanguages

 

Other than that, the script is the same as the original.  With this addition, you can build your translation table in Excel as the translations become available and still be able to generate your resource files and be able to run and test the application with “reasonable” translations available.

Here is the full script:

# ResourceGeneratorWithBing.ps1

#

# Summary: Script that will extract region-specific text data from an Excel

# spreadsheet and update resource files.  This allows you to create a master

# spreadsheet that contains all of your translated application text data in one

# central location.  The data in the resx files are then slaves to the 

# spreadsheet.  This way you have one source of the "true" text data within

# a multi-language application.

 

# NOTE: This script will not work in 64bit Powershell because of the lack of a

#  64bit OleDb driver for Excel.  It will work on (in fact was developed on) a 

#  64bit machine - you just have to run 32bit Powershell.

#

# Steps to make this work in your application are toward the bottom of this script

 

# function: Get-ExcelLanguageData

# Extract the ID and text values from the spreadsheet

# This data will be directly used to create the region

# specific resource files later

function Get-ExcelLanguageData {

    param( [string] $excelFilePath )

    

    # Use OleDb to open the Excel spreadsheet

    $selectStatement = "Select * from [$sheetName] Where TextId <> `"`""

 

    $connection = New-Object Data.OleDb.OleDbConnection "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$excelFilePath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"

    $command = New-Object Data.OleDb.OleDbCommand $selectStatement, $connection

        

    $connection.Open()

        

    $dataAdapter = New-Object Data.OleDb.OleDbDataAdapter $command

        

    $dataSet = New-Object Data.DataSet

        

    [Void] $dataAdapter.Fill($dataSet)

        

    $connection.Close()

        

    return $dataSet

}

 

# function Create-ResxFile

# Use the region-specific language data (the dataset created from Excel) 

#  and modify the EXISTING resource files from my C# project.

function Create-ResxFile {

    param ( [string] $resxFilePath, [Data.DataSet] $excelData, [string] $languageColumn )

    

    $xmlDoc = new-object "System.Xml.XmlDocument"

 

    $xmlDoc.Load($resxFilePath)

  

    $dataNodeList = $xmlDoc.selectNodes("/root/data")

 

    # Remove any existing data nodes since they will all be replaced with the data from excel

    foreach($dataNode in $dataNodeList)

    {

        $dataNode.parentNode.removeChild($dataNode);

    }

 

    # Create each of the "data" elements in XML - this is where the 

    #  text data resides with the resource key

    foreach ($row in $excelData.Tables[0])

    {

        $dataElem = $xmlDoc.CreateElement("data")

        $nameAttr = $xmlDoc.CreateAttribute("name")

 

        $preserveAttr = $xmlDoc.CreateAttribute("xml:space")

        $preserveAttr.Value = "preserve"

 

        $nameAttr.Value = $row['TextId']

 

        $dataElem.Attributes.Append($nameAttr)

        $dataElem.Attributes.Append($preserveAttr)

        $dataElem.InnerXml = $row[$languageColumn]

 

        $rootNode = $xmlDoc.selectSingleNode("/root")

 

        $rootNode.AppendChild($dataElem);

    }

 

    # Save the resource file

    $xmlDoc.Save($resxFilePath)

}

 

 

# function Translate-Table

# Scan the dataset extracted from Excel and look for cells that

#  are blank and therefore need to be translated.  When a blank

#  cell is found, call the Bing (Microsoft) Translator, pass the

#  from/to languages ID's and the terms to Bing and get the 

#  translated text back.  The translated text is then stored in the

#  dataset.

function Translate-Table {

    param ( [Data.DataSet] $excelData, $translationList )

 

    $count = [string]$translationList.Length

    

    foreach ($row in $excelData.Tables[0])

    {

        for ($index = 2; $index -le $count; $index++) {

            

            # We have to check for DBNull here to see if the term is present or not

            if ( $row[$index] -eq [DBNull]::Value ) {

                $fromText = [System.Web.HttpUtility]::UrlEncode($row[1])

                $fromLang = $translationList[0]

                $toLang = $translationList[$index - 1]

    

                [string] $requestString = "http://api.bing.net/xml.aspx?AppId=$appId&Query=$fromText&Sources=Translation&Version=2.2&Translation.SourceLanguage=$fromLang&Translation.TargetLanguage=$toLang"

        

                # Create and initialize the request.

                [System.Net.HttpWebRequest] $request = [System.Net.HttpWebRequest]::Create($requestString)

                    

                # Send the request; display the response.

                [System.Net.HttpWebResponse] $response = [System.Net.HttpWebResponse] $request.GetResponse()

            

                # Load the response into an XmlDocument.

                [System.Xml.XmlDocument] $xmlDocument = New-Object System.Xml.XmlDocument

                $xmlDocument.Load($response.GetResponseStream())

    

                # Add the default namespace to the namespace manager.

                # psbase: this means "return the original object".  This is neccessary because PS sometimes interprets methods and properties

                #  out of objects.  When this is the case, psbase gives you access to the root object and all of the original methods and props

                [System.Xml.XmlNamespaceManager] $nsmgr = New-Object System.Xml.XmlNamespaceManager $xmlDocument.psbase.NameTable

                $nsmgr.AddNamespace("api", "http://schemas.microsoft.com/LiveSearch/2008/04/XML/element")

    

                [System.Xml.XmlNodeList] $errors = $xmlDocument.DocumentElement.SelectNodes("./api:Errors/api:Error", $nsmgr)

    

                if ($errors.Count -gt 0)

                {

                    # There are errors in the response. Display error details.

                    Write-Output "Error retrieving translation data"

                }

                else

                {

                    $root = $xmlDocument.DocumentElement

                    

                    # Add the Translation SourceType namespace to the namespace manager.

                    $nsmgr.AddNamespace("tra", "http://schemas.microsoft.com/LiveSearch/2008/04/XML/translation")

            

                    [System.Xml.XmlNodeList] $results = $root.SelectNodes("./tra:Translation/tra:Results/tra:TranslationResult", $nsmgr)

            

                    $row.item($index) = $results.item(0).InnerText

                }

            }

        }

    }

    

    $excelData

}

 

 

 

# Steps to make this work for your application"

#

# 1: Review the "TextResourceTranslation.xlsx" spreadsheet in this project to 

#     get a good feeling for how this script works with the column/row values

#     within it.

# 2: Create your own translation spreadsheet or modify the one provided

# 3: Substitute location path values below to match your project location

# 4: Substitute appropriate filenames

# 5: Substitute the name of the spreadsheet ($sheetName - this is the name from

#     the Excel workbook. In other words, the name on the tab at the bottom of

#     Excel.  In a new spreadsheet this defaults to "Sheet1".

# 6: Modify the $resxRegions array values.  The first value is the name 

#     of the resource (resx) file.  The second value is the column name of the 

#     appropriate language text within the spreadsheet.

 

 

 

# Root location of the spreadsheet and resx resource files

$projectPath = "D:\_App Dev\TechReady9_DEV320\TechReady9_DEV320"

# Filename of the Excel Translation spreadsheet

$excelDataFile = "TextResourceTranslation.xlsx"

# The name of the Excel sheet with the translation data in it

$sheetName = "TextTranslation`$"    

 

# Array of arrays: (region, Excel column name)

# For this script, the region is also used as the resource file (resx) filename

# Note: if the region is left blank ("") then it will become the default resource file

$resxRegions = @(("", "enUS"), ("es-ES", "esES"), ("ja-JP", "jaJP"))

 

# These are the languages for each of the DataSet Translations - 

# This is for use within the Bing auto translation feature

# If there are cells in the Excel spreadsheet that are empty, they will be filled

#  in with a call to Bing's translation service.

$translationLanguages = @("En", "Es", "Ja")

$appId = "Insert your Bing Application ID Here"

 

# This is the subfolder where the resx files are to be found/stored

$resxSubfolder = "Properties"

 

# This creates the full path for the Excel translation data file

[string] $excelFullPath = [System.IO.Path]::Combine($projectPath, $excelDataFile)

 

# Get the data from Excel and store in a dataset

[Data.DataSet] $excelData = Get-ExcelLanguageData $excelFullPath

 

# Perform translations using Bing Translator.  Only empty cells

#  in the non-default language columns will be translated

#  Note: This DOES NOT modify the Excel Spreadsheet.  It does the

#  translation and updates the resource files ONLY.  This step

#  is NOT required to generate resource files.  It is here to

#  "fill in the blanks" when the source spreadsheet does not 

#  have all cells filled in.  If you do not wish to use it,

#  comment the next line.

$excelData = Translate-Table $excelData $translationLanguages

 

# Loop through the regions and build the resx resource files

#  extracting the data from the Excel spreadsheet 

#  and modifying/adding to the xml in the resource file 

foreach($regionData in $resxRegions) {

 

    [string] $resxFilename = ""

 

    if ($regionData[0] -eq "") {

        $resxFilename = "Resources.resx"

    } else {

        $resxFilename = "Resources.$($regionData[0]).resx"

    }

    

    # Create the full path to the resource file

    [string] $resxFullPath = [System.IO.Path]::Combine($projectPath, $resxSubfolder)

    $resxFullPath = [System.IO.Path]::Combine($resxFullPath, $resxFilename)

    

    # Use the dataset to generate the resource 

    Create-ResxFile $resxFullPath $excelData $regionData[1]

}