Automate Language translations and Save to Excel with Ease!

Automate Language translations

In the modern world of data processing, automation plays a crucial role in simplifying various tasks. One common scenario is translating data from one language to another and organizing it efficiently. In this tutorial, we’ll explore how to use the Python Google Translate package to translate JSON data and save the translated results to separate Excel sheets for each language.

Real-World Use Cases for Translating JSON Data Using Python

Here are some practical use cases where the translation script for JSON data can be incredibly valuable:

1. Multilingual Content Management: For businesses and websites with a global audience, maintaining multilingual content is essential. This script can automate the translation of content, ensuring that users across different language groups can access the information in their preferred language.
2. Global Marketing Campaigns: When launching marketing campaigns in various regions, tailoring the content to the local language can significantly enhance engagement. This script simplifies the process of translating marketing materials while keeping consistency across different languages.
3. Product Localization: For e-commerce platforms, adapting product descriptions and specifications to the local language is crucial. This script can quickly generate translated content for product listings, leading to better customer understanding and engagement.
4. Language Learning Apps: Language learning applications can utilize this script to provide users with real-world examples in different languages. For instance, translating phrases, dialogues, or vocabulary into various languages can enhance the learning experience.
5. Data Analysis and Reporting: If you’re working with international data sets, having translations of text-based information can be insightful for analysis. This script can help you easily create multilingual reports, aiding decision-making processes.
6. Global Collaboration: In projects involving teams from different parts of the world, this script can facilitate communication by translating shared documents and materials, ensuring everyone is on the same page.

Prerequisites

Before we get started, make sure you have the following prerequisites in place:

1. Python Installed: Make sure you have Python installed on your machine. You can download it from the official Python website.
2. Required Libraries: We’ll use the googletrans and xlsxwriter libraries. Install them using the following commands:

pip install googletrans==4.0.0-rc1 xlsxwriter

Setting Up the Project

1. Create a directory for your project and navigate to it.
2. Place the en.json file containing the JSON data in this directory. This JSON file will serve as the input for our translation process.

Translating JSON Data

Let’s start by translating the JSON data using the Google Translate API. Our example JSON data looks like this:

Here we want to translate our product list to other languages like German, Spanish, Hindi etc. so that we can show the translated products on the website

{
  "id": 1,
  "title": "iPhone 9",
  "description": "An apple mobile which is nothing like apple",
  "price": 549,
  "discountPercentage": 12.96,
  "rating": 4.69,
  "stock": 94,
  "brand": "Apple",
  "category": "smartphones"
}

We’ll use the googletrans library to translate the values of the keys except for some keys that show numerical values.

import json
from googletrans import Translator

def translate_json(json_data, target_lang):
    translator = Translator()

    translated_json = []
    for item in json_data:
        translated_item = {}
        for key, value in item.items():
            if key in ["price", "stock"]:
                translated_item[key] = value  # Exclude these keys
            else:
                translated_value = translator.translate(
                    value, src="en", dest=target_lang
                ).text
                translated_item[key] = translated_value
        translated_json.append(translated_item)

    return translated_json

def main():
    input_file = "en.json"

    with open(input_file, "r", encoding="utf-8") as file:
        json_data = json.load(file)

    target_language = "es"  # Replace with your target language code
    translated_data = translate_json(json_data, target_language)

    print("Translated JSON Data:")
    print(json.dumps(translated_data, indent=4))

if __name__ == "__main__":
    main()

Make sure to replace "es" with the language code you want to translate to. This code will translate the values while excluding the price and stock keys.

Saving Translated JSON to Files

Now that we have our translated data, it’s essential to save it to separate JSON files for each language. We’ll use the json.dump() function to achieve this.

def save_to_json(translated_data, target_language):
    output_filename = f"{target_language}.json"

    with open(output_filename, "w", encoding="utf-8") as outfile:
        json.dump(translated_data, outfile, ensure_ascii=False, indent=4)

    print(f"Translated data saved to {output_filename}")

def main():
    # ... (previous code)
    
    save_to_json(translated_data, target_language)

if __name__ == "__main__":
    main()

This code will generate JSON files like es.json, fr.json, etc., for each language.

💁 Check out our other articles😃

 👉  Generate a free Developer Portfolio website with AI prompts

 👉  Creating a Toggle Switcher with Happy and Sad Faces using HTML, CSS, and JavaScript

Automating Excel Sheets

Now, let’s move on to organizing our translated data into Excel sheets. We’ll use the xlsxwriter library to create and manage Excel files.

import xlsxwriter

def save_to_excel(translated_data, target_language):
    excel_file = "products.xlsx"
    sheet_name = target_language

    try:
        book = xlsxwriter.Workbook(excel_file)
    except FileNotFoundError:
        book = xlsxwriter.Workbook(excel_file)

    sheet = book.add_worksheet(sheet_name)
    startrow = 0
    col = 0

    # Write JSON keys in the first row
    json_keys = list(translated_data[0].keys())
    for key in json_keys:
        sheet.write(startrow, col, key)
        col += 1

    # Write translated values in subsequent rows
    for item in translated_data:
        col = 0
        startrow += 1
        for key, value in item.items():
            sheet.write(startrow, col, value)
            col += 1

    book.close()

    print(
        f"Translated data saved to {excel_file}, sheet {sheet_name}"
    )

def main():
    # ... (previous code)
    
    save_to_excel(translated_data, target_language)

if __name__ == "__main__":
    main()

This code will create an Excel file named products.xlsx with separate sheets for each language containing the translated data.

Complete Google Translate JSON to 5 Languages Code

import json
from googletrans import Translator
import xlsxwriter

LANGUAGES = [
    "de",  # German
    "es",  # Spanish
    "hi",  # Hindi
]


def translate_json(json_data, target_lang):
    translator = Translator()

    translated_json = []
    for item in json_data:
        translated_item = {}
        for key, value in item.items():
            if key in ["discountPercentage", "price", "rating", "stock"]:
                translated_item[key] = value  # Exclude these keys
            else:
                translated_value = translator.translate(
                    value, src="en", dest=target_lang
                ).text
                translated_item[key] = translated_value
        translated_json.append(translated_item)

    return translated_json


def save_to_excel(translated_data, target_language, workbook):
    sheet_name = target_language

    sheet_exists = False

    if sheet_name in workbook.sheetnames:
        sheet = workbook.get_worksheet_by_name(sheet_name)
        startrow = sheet.dim_rowmax + 1
        sheet_exists = True
    else:
        sheet = workbook.add_worksheet(sheet_name)
        startrow = 0

    col = 0

    if sheet_exists is False:
        # Write JSON keys in the first row
        json_keys = list(translated_data[0].keys())
        for key in json_keys:
            sheet.write(startrow, col, key)
            col += 1

    # Write translated values in subsequent rows
    for item in translated_data:
        col = 0
        startrow += 1
        for key, value in item.items():
            sheet.write(startrow, col, value)
            col += 1

    # book.close()

    print(
        f"Translation to {target_language} complete. Translated data saved to sheet {sheet_name}"
    )


def main():
    input_file = "en.json"

    with open(input_file, "r", encoding="utf-8") as file:
        json_data = json.load(file)

    workbook = xlsxwriter.Workbook("translatedtexts/products.xlsx")

    for target_language in LANGUAGES:
        translated_data = translate_json(json_data, target_language)
        output_filename = f"translatedtexts/{target_language}.json"

        with open(output_filename, "w", encoding="utf-8") as outfile:
            json.dump(translated_data, outfile, ensure_ascii=False, indent=4)

        save_to_excel(translated_data, target_language, workbook)
        print(
            f"Translation to {target_language} complete. Translated data saved to {output_filename}"
        )

    workbook.close()


if __name__ == "__main__":
    main()

Conclusion

In this tutorial, we explored how to translate JSON data using the Google Translate API and save the translated data to separate JSON files and Excel sheets. This approach can be valuable for automating translation tasks and organizing data efficiently.

By following the steps outlined in this tutorial, you’ve learned how to:

• Use the googletrans library to translate text.
• Save translated data to JSON files.
• Create and manage Excel sheets using the xlsxwriter library.

Feel free to adapt this code to your specific projects and explore additional features of the libraries used. With Python’s powerful capabilities, you can streamline various data processing tasks and make your workflow more efficient.

Happy coding!