Tower of Babel and the Translation Workbench

Many of us know the story of the Tower of Babel, but if you don't here is a quick recap:

All of the people in the land gathered to one place and decided to build a large tower in order to make themselves known. But the Lord saw what the people were doing and decided that nothing would be impossible for them to do if they continued to speak a single language so he created a bunch of languages, confusing the people when the spoke and reducing their power as a collective group.

The result? The world is filled with hundreds of languages, and Salesforce Admins everywhere have the pleasure of working with the translation workbench!

If you haven't worked with this tool, it is not easy! In my opinion, it is the single most frustrating feature of Salesforce and is a productivity nightmare when creating new translations. Before going any further, I should preface that I am a translation workbench novice; only in the last few weeks have I really worked with the tool, but my opinion will remain the same until someone can persuade me otherwise!

Salesforce does have some information about the translation workbench published including this somewhat helpful Translation Workbench Export and Import Quick Start Guide. There is some additional information in the Help & Training section, but the content seems to be limited through either resource and not terribly helpful. Additional information on errors and how to fix them would have been fantastic!

Because this was my first attempt at working with this tool, I learned through a process of trial and error, along with some help from a friend. Here are some of the areas that I found to be important.

Don't pull a translation file from your sandbox environment for translation - they Keys will not match up when trying to load the sandbox version of the translation file into production. This is exactly what I did and it required that I export a new file from production and create a VLOOKUP function to pull over the translated values from my original workbook.

You will probably receive a number of errors. My most common errors were Maximum character limit (40) for CustomField translation which means that the translated field label is too long, and Invalid Key which means that for whatever reason, there is at least one key in the KEY column that doesn't match the key in Salesforce and should be verified.

Files need to be imported in a .stf format. Salesforce provides no assistance in their documentation (that I could find) on how to change your Excel file back into a .stf for upload. Below you will find my instructions for converting and saving the file in .stf.
Before you walk through the steps outlined below, you will need to install a free tool called Notepad ++ to proceed. Please do this first, then follow the instructions below.

1. Open your saved Excel translation file
2. Click File | Save As
3. Choose the destination and file name. File type should be Text (Tab delimited) (*txt)
4. Click Save
5. Open Notepad++ and click File | Open and select the file.
6. Click Open
7. Click Encoding | Convert to UTF-8 without BOM
8. Click File | Save As
9. In the File Name section, change the .txt extension to .stf
10. Click Save

Now, you will be able to navigate to the import section of the translation workbench and import the .stf file. Once the import is complete, an email will return informing you if the file was successful or had errors. If errors occur, open your Excel workbook, make changes, then save and start from step two (2) in the above instructions. Rename your files to keep a history of changes if you wish.

After several days and multiple attempts, I am happy to say that my file finally uploaded into the production environment without errors! However, I still have quite a bit of manual work that needs to be done to remove special characters and add those translations that I had to remove from the file for character limit errors.

I would love to hear if you have any other advise or recommendations for making this process easier. Connect with me or comment below!