This is a follow-up to the related article, How to add another language to OpenCart, use Google Translate, and error handling tips. In order to add a new language to your OpenCart ecommerce website, you will need to provide a translation of all the products in your store. To do this quickly instead of painfully and manually clicking through each product one-by-one, you can export the product catalog into a spreadsheet and then have Google Translate do the first-pass translation. You can then review the translation and make any minor corrections, reducing your workload by up to 99%.
a. Download and install Export/Import Tool
This extension allows you to export all the categories, products, options, and attributes in your OpenCart site to a spreadsheet. You can then edit it offline and then import the new values to the site.
b. Export all the data that requires translation
Go to OpenCart Admin-System-Export/Import-Export tab and export all the data that requires translation into spreadsheet/s.
c. Go to https://docs.google.com/spreadsheets and upload the spreadsheet/s.
d. For each spreadsheet, click and select “Edit as Google Sheets”. You should see something similar to this, with a column for each of the languages supported for relevant values such as product name and description:
e. Google Sheets has a function named GOOGLETRANSLATE that uses Google Translate to translate the spreadsheet into the desired language. See example below:
The formula used to translate from English to Chinese is:
Translating a blank cell will result in an error so
IF is used to check if the cell is blank, in which case it will skip it.
f. You can copy and paste the formula into all the cells that require this translation.
g. After the translation is done, you would want to remove the formula and just retain the translated text. To do so, copy it and then Click on Edit-Paste Special-Paste Values Only.
h. If you have HTML and CSS in your spreadsheet (for example to format product descriptions), some of the code that’s supposed to be in English may get translated. This will result in display errors if not corrected
To resolve this, export the spreadsheet from Google Sheets into Excel and do a “find and replace” of ” style=*>” with “>”. I haven’t found an easy way of doing this in Google Sheets unfortunately and find that Excel works a lot better for this.
i. After this is done, you can do the actual translation in Sheets as mentioned in Step e above.
j. Once the spreadsheets have been translated, they can be re-imported into OpenCart using Export-Import.