locked
Bing/MS Translator documentation is a total mess RRS feed

  • Question

  • I ahve been using the Google API in Ajax for several years to translate the search field on my website www.netkvik.dk. This was very simple to use. You could learn to do it in munutes. As Google has recently stopped its free service I decided to give the Bing/MS translator a try. I signed up for a Bing AppID and spent a lot of time on trying to figure out why my code doesn't work. Then I stumble on the information in a forum entry that the AppID is no longer working for new users.

    It would have been nice if you had updated the Bing sign up website with that information.

    I also found - after lots of searching - that you now have to sign up for an Azure Marketplace account. I did that and was led to a page where you can access the translator from a form. Not very useful at all, and no information of how to use an API to access it. If you follow the links you get back to the Bing Ajax page! Which is the one that doesn't work.

    After some more searching in the forums I find that you now need an access token. But first you have to register your application. You get to a Register page which asks for ClientID, Name and Redirect URI, but no guidance as to how you fill in those fields. Some more searching reveals that you just invent your own ID and Name and that the Redirect URI is not used (so why make it a required field?).

    I still cannot find any information on how to tie the access token together with the Ajax translation reuest. It all needs to be written in HTML/Javascript and it ought to be really simple. Translation of text on a webpage should not be made complicated.

    Why is there no working example published anywhere? That ought to be the first thing you do when making changes.

    I am rather discouraged by my experience so far, but here is your chance to fix it. Please publish a working example with simple code.

     

    Wednesday, January 4, 2012 1:42 PM

Answers

  • I'm working on getting documentation cleaned up.

    How to sign up for Translator in the Windows Azure Marketplace here:

    http://www.netnavi.tv/2012/04/22/getting-started-with-microsoft-translator-in-the-azure-data-market/

    Some Code Samples:

    C# Asynchronous and Synchronous examples in here:

    http://social.msdn.microsoft.com/Forums/en-US/microsofttranslator/thread/02054a7c-090c-46a0-a8d1-2d57e236eb4d

    And VB on this thread:

    http://social.msdn.microsoft.com/Forums/en-US/microsofttranslator/thread/3f9a270e-3360-4aa1-9eb8-669145298d45

    Hope this helps...


    //Laurence Moroney: www.netnavi.tv // Author of 'Introducing Silverlight' series and much more // @lmoroney

    • Proposed as answer by sysmod Wednesday, May 2, 2012 9:27 AM
    • Marked as answer by Laurence Moroney Friday, May 25, 2012 9:33 PM
    Wednesday, April 25, 2012 2:05 PM

All replies

  • I'm in the same position. I posted a query over on the Excel for Developers forum so I now know how to get an Access Token using VBA. But I don't know what to do with the result.

    MS, please publish a working example with VBA code.

     

    Thursday, January 5, 2012 11:48 AM
  • OK, here's an example of Microsoft Translator called from VBA. You need to signed up on Azure Marketplace, the only thing you need to specify is your Client ID which must be unique and the site will give you a Client Secret which you use in GetToken(). Corrections and simplifications welcome.

    Option Explicit

    'Sources of info:
    'http://blogs.msdn.com/b/translation/
    'http://msdn.microsoft.com/en-us/library/hh454950.aspx
    'https://datamarket.azure.com/dataset/1899a118-d202-492c-aa16-ba21c33c06cb

    'references :
    'Microsoft XML, v6.0

    Sub testMicrosoftTranslate()
    MsgBox MicrosoftTranslate("Ma déclaration d'impôts", "", "en") ' "My tax return"
    End Sub

    Function MicrosoftTranslate(sText As String, sLanguageFrom As String, sLanguageTo As String) As String
    Dim ID As String, sURL As String, oH As MSXML2.XMLHTTP, sToken As String
       ID = ""    ' Bing appID deprecated in Dec 2011 in favour of Access Token. Use nothing for legacy appID parameter
       sURL = "http://api.microsofttranslator.com/V2/Ajax.svc/Translate?oncomplete=&appId=" & ID _
                & "&from=" & sLanguageFrom & "&to=" & sLanguageTo & "&text=" & sText
       sToken = GetAccessToken()
       Set oH = CreateObject("MSXML2.XMLHTTP")
       oH.Open "POST", sURL, False
       oH.setRequestHeader "Authorization", "Bearer " & sToken
       oH.send
       MicrosoftTranslate = oH.responseText
       Set oH = Nothing
    End Function

    Function GetAccessToken() As String
    Dim webRequest As MSXML2.ServerXMLHTTP
    Set webRequest = CreateObject("MSXML2.ServerXMLHTTP")
    Dim URI As String
    URI = "https://datamarket.accesscontrol.windows.net/v2/OAuth2-13"

    'get Your Client ID from https://datamarket.azure.com/developer/applications
    Dim clientId As String
    clientId = "Your Client ID"

    'get Your client secret from https://datamarket.azure.com/developer/applications
    Dim clientSecret As String
    clientSecret = "Your client secret"

    Dim sRequest As String
    sRequest = "grant_type=client_credentials" & _
          "&client_id=" & clientId & _
          "&client_secret=" & URLEncode(clientSecret) & _
          "&scope=http://api.microsofttranslator.com"

    Dim mtToken As String
    Set webRequest = New ServerXMLHTTP
    webRequest.Open "POST", URI, False
    webRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    webRequest.send sRequest
    mtToken = webRequest.responseText
    Set webRequest = Nothing

    'Debug.Print mtToken

    Dim arr As Variant, header As String
    header = "{""access_token"":"""
    arr = Split(mtToken, ",")
    'Debug.Print LBound(arr), UBound(arr), arr(0), arr(1), arr(2), arr(3)
    'arr(0)={"access_token":"http%3a%2f%2fschemas.xmlsoap.org%...etc...
    GetAccessToken = Mid$(arr(0), Len(header) + 1, Len(arr(0)) - Len(header) - 1)
    End Function


    Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
    Dim StringLen As Long
     StringLen = Len(StringVal)
     If StringLen > 0 Then
       ReDim result(StringLen) As String
       Dim i As Long, CharCode As Integer
       Dim Char As String, Space As String
       If SpaceAsPlus Then Space = "+" Else Space = "%20"
       For i = 1 To StringLen
          Char = Mid$(StringVal, i, 1)
          CharCode = Asc(Char)
          Select Case CharCode
          Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
          result(i) = Char
          Case 32
          result(i) = Space
          Case 0 To 15
          result(i) = "%0" & Hex(CharCode)
          Case Else
          result(i) = "%" & Hex(CharCode)
          End Select
       Next i
       URLEncode = Join(result, "")
    End If

    End Function

    Thursday, January 5, 2012 3:36 PM
  • Hi John,

    the process of obtaining and using the access credentials is explained here: http://msdn.microsoft.com/en-us/library/hh454950.aspx, part of the Translator documentation on MSDN.

    While the simple appid mechanism that we and others have been using is, well, simple, it isn't very secure. Since we are now charging for the service, security matters, and the mechanism is indeed a little more complicated, and a lot more secure.

    I totally agree that we could do better in posting more samples besides the published C# one. Thanks to everyone who does post a working sample here, because when we do, we can't possibly cover all languages you are interested in.

    Chris Wendt
    Microsoft Translator

    Friday, January 6, 2012 4:51 AM
  • Monday, January 9, 2012 11:45 AM
  • I'm working on getting documentation cleaned up.

    How to sign up for Translator in the Windows Azure Marketplace here:

    http://www.netnavi.tv/2012/04/22/getting-started-with-microsoft-translator-in-the-azure-data-market/

    Some Code Samples:

    C# Asynchronous and Synchronous examples in here:

    http://social.msdn.microsoft.com/Forums/en-US/microsofttranslator/thread/02054a7c-090c-46a0-a8d1-2d57e236eb4d

    And VB on this thread:

    http://social.msdn.microsoft.com/Forums/en-US/microsofttranslator/thread/3f9a270e-3360-4aa1-9eb8-669145298d45

    Hope this helps...


    //Laurence Moroney: www.netnavi.tv // Author of 'Introducing Silverlight' series and much more // @lmoroney

    • Proposed as answer by sysmod Wednesday, May 2, 2012 9:27 AM
    • Marked as answer by Laurence Moroney Friday, May 25, 2012 9:33 PM
    Wednesday, April 25, 2012 2:05 PM
  • Any idea why the Function - MicrosoftTranslate returns a value of ????? when the detected language is Russian?

    I think Russian (ru) is a supported language for the translation? 

    When I use the front end translator through the application it translates the word -

    Современной  (Russian)

    Modern (English)

    Tuesday, April 28, 2015 5:22 PM
  • Hi Terry,

    which sample are you looking at? The ??? looks like a failed conversion from Unicode to windows-1252 or another Latin-1 charset. Try to declare the receiving variable as Unicode and make sure your display displays Unicode.

    The Translator API functions in UTF-8 encoding, exclusively.

    Hope this helps,
    Chris Wendt
    Microsoft Translator

    Tuesday, April 28, 2015 5:41 PM
  • Hi Terry,

    which sample are you looking at? The ??? looks like a failed conversion from Unicode to windows-1252 or another Latin-1 charset. Try to declare the receiving variable as Unicode and make sure your display displays Unicode.

    The Translator API functions in UTF-8 encoding, exclusively.

    Hope this helps,
    Chris Wendt
    Microsoft Translator

    Hi Chris:

    I was using the example as displayed by user  'sysmod' above in the VBA example. 

    Taking your suggestion, I thought I could use the STRCONV function in VBA around the resulting variable but end up in the same spot with ? as the original result. 

    Tuesday, April 28, 2015 7:25 PM
  • "The ??? looks like a failed conversion from Unicode to windows-1252 or another Latin-1 charset. "

    I think so too. If you are looking at the text using debug.print in VBA or using MsgBox it will not convert correctly.

    Store it in a cell and it will look OK.

    Or display it using the messagebox API and that will display Unicode correctly.

    Public Declare Function APIMsgBox Lib "USER32" Alias "MessageBoxW" (Optional ByVal hWnd As Long, Optional ByVal Prompt As Long, Optional ByVal Caption As Long, Optional ByVal BUTTONS As Long) As Long

    APIMsgBox(Prompt:=StrPtr(sPrompt), Caption:=StrPtr(sTitle), BUTTONS:=lButtons)

    Tuesday, April 28, 2015 11:00 PM
  • "The ??? looks like a failed conversion from Unicode to windows-1252 or another Latin-1 charset. "

    I think so too. If you are looking at the text using debug.print in VBA or using MsgBox it will not convert correctly.

    Store it in a cell and it will look OK.

    Or display it using the messagebox API and that will display Unicode correctly.

    Public Declare Function APIMsgBox Lib "USER32" Alias "MessageBoxW" (Optional ByVal hWnd As Long, Optional ByVal Prompt As Long, Optional ByVal Caption As Long, Optional ByVal BUTTONS As Long) As Long

    APIMsgBox(Prompt:=StrPtr(sPrompt), Caption:=StrPtr(sTitle), BUTTONS:=lButtons)

    So using the translate API function as an example.

    Cell A1 has 東京 (Which is Tokyo in English)  and cell B1 has the formula = MicrosoftTranslate(A1)  but still results in ??? as the result. 

    Is there some other Excel setting that I'm missing to allow it to display properly or am I completely using this the wrong way?

    Thanks again for the help.

    Thursday, April 30, 2015 2:19 PM
  • I had time today to browse and finally found the answer thanks to a clue at w3schools that showed me that the browser encodes the Japanese as THREE hex bytes: eg 

    http://www.w3schools.com/tags/html_form_submit.asp?text=%E6%9D%B1%E4%BA%AC

    To do this in VBA, I found

    http://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba

    So here is the changed code:

    <code>

    Private Const CP_UTF8 = 65001
    Private Declare Function WideCharToMultiByte Lib "Kernel32" ( _
        ByVal CodePage As Long, ByVal dwflags As Long, _
        ByVal lpWideCharStr As Long, ByVal cchWideChar As Long, _
        ByVal lpMultiByteStr As Long, ByVal cchMultiByte As Long, _
        ByVal lpDefaultChar As Long, ByVal lpUsedDefaultChar As Long) As Long

    Public Function UTF16To8(ByVal UTF16 As String) As String
    Dim sBuffer As String
    Dim lLength As Long
    If UTF16 <> "" Then
        lLength = WideCharToMultiByte(CP_UTF8, 0, StrPtr(UTF16), -1, 0, 0, 0, 0)
        sBuffer = Space$(lLength)
        lLength = WideCharToMultiByte( _
            CP_UTF8, 0, StrPtr(UTF16), -1, StrPtr(sBuffer), Len(sBuffer), 0, 0)
        sBuffer = StrConv(sBuffer, vbUnicode)
        UTF16To8 = Left$(sBuffer, lLength - 1)
    Else
        UTF16To8 = ""
    End If
    End Function

    'Activecell has Tokyo in 2 Japanese characters

    Sub testMicrosoftTranslate()
    Dim sText As String, sLang As String
    sText = URLEncode(UTF16To8(ActiveCell.Value))
    sLang = MicrosoftTranslatorDetect(sText)
    MsgBox sLang, vbInformation, "Language" ' ja
    MsgBox MicrosoftTranslate(sText, sLang), vbInformation, "Translation " ' Tokyo
    End Sub

    </code>

    Sunday, May 17, 2015 5:38 PM
  • Hi sysmod,

    great answer, thanks a lot. Good to have a VBA expert on the forum.

    Chris Wendt
    Microsoft Translator

    Sunday, May 17, 2015 5:45 PM