I am working with many foreign language files. Is there a way to translate columns - in this case Russian to English, but I work with other languages as well. Any thoughts?
In my case either overwrite works or a new column with the translation. Other fields include addresses, phone numbers, urls, descriptions, name and logo.
Are you asking how to make a translator procedure in PanX (including grammar rules instead of just word-for-word substitutes), or how to pass the language content to another app or webpage, trigger that translation, and pull back the result into another field?
Apple’s OS allows for other languages but is not a translator. PanX can retrieve data from a web page and maybe fill the webpage’s From box, trigger the translation, and pull the To box result back to PanX.
Or AppleScript might be able to do the “external to PanX” work; sending the text to the webpage, activating the translation, and pulling the result back. That AppleScript could be embedded in a PanX procedure.
The actual HowTo is out of my realm but that’s what I’d be looking at. Note that you’d either need access to the internet or have a local translator app on your device that is AppleScript-able.
AppleScript might be a good solution. There are several macros for Open Office and Excel that take text in one language from column A, identify source language (B), target language (C) and then provide an English translation in column D. Others translate and replace. These macro may call Google Translate or other services to do this. Here’s an example.
Apple now translates up to 10,000 characters at a time in limited languages for text in Apple products and some third party apps for either copy or replace. However, apart from highlighting each cell individually, this does not work with rows and columns. Also the character limit could be difficult with larger datasets.
What I would like to do is translate one column at a time, create a new column to show the translation side by side.
The Excel macro you posted does use Google Translate. Using that as a starting point, I was able to create a formula that can translate a phrase from Spanish to English. Here you can see this in action to translate adios azul amigos into goodbyte blue friends (yes, it’s nonsense).
Here is a version of this formula with variables for the source and output languages, and the text, so that you can tweak as needed.
let sourceLanguage = "es" // Spanish
let outLanguage = "en" // English
let theText = "adios azul amigos"
let translation = tagdata(url("https://translate.google.com/m?"+
"sl="+sourceLanguage+
"&tl="+outLanguage+"&hl="+outLanguage+
"&q="+encodeurlquery(theText)),{<div class="result-container">},{</div>},1)
To do this I haven’t consulted the documentation for Google Translate - I just looked at the macro you sent it. So I have no idea of what the limits of Google Translate are, and I don’t know what will happen if you repeatedly call this formula hundreds or thousands of times. My guess is that Google will not provide infinite translations for free, but I’ll leave it up to you to find out about that.
To translate an entire column you could either use the code above with a loop, or use the formulafill statement. Either way, this will take some time to do an entire column, since it will need to access the internet for each cell. And as I mentioned above, most likely Google will not let you translate that much data in a short time, unless perhaps there is some paid service they offer.
In case it wasn’t already clear, translation is not a supported feature of Panorama X. I’ve put together this hack to show how an internet translation service could be used, but there are no promises about how fast this works or how much data can be translated, or even if this will continue to work in the future.
This is fantastic. Thanks so much. I will let you know how it goes & test it on a smaller dataset. One question - Does this replace the existing foreign text? (It’s fine if it does in this use case)
In Jim’s example code, the foreign text is in a variable named theText, and the translation ends up in a variable named translation.
When he said “you can tweak as needed,” he meant that you would probably be replacing at least some of his variable names with the names of fields in your database. If you replace theText and translation with the same field name, you will be replacing the existing text with the translation, but if you replace them with separate field names, the original foreign text will remain unchanged in a separate field from the translation.
Thanks Dave! This helps!
My colombian friends tell me that the translations by ChatGPT4 are better than google’s. I set up a simple ChatGPT prompt as follows:
please translate anything I paste to English, if I paste spanish, and translate to spanish if I enter something in English, until I say STOP
and the response was:
ChatGPT
Understood! I’ll translate anything you paste to English if it’s in Spanish, and to Spanish if it’s in English. Just let me know when to stop. Feel free to paste your text.
And it works like a charm. I think that its would be straightforward to have Panorama call the ChatGPT API to do the same thing. You might have to bounce it through Python though .
csw
Thanks Watts. I will give this a try!