I'm doing an auto-translation script to convert all the data (mixed language) in my Excel file. My file is composed of 3000-4000 raw data with mixed language and I want to translate all the data to "English" but the problem is there are data that are skipped or didn't translate. And also I found one word "Gracias!" not translated to English.
Below is my script hope anyone can help me to fix my current code.
Thanks.
from openpyxl import load_workbook
from googletrans import Translator
# Load the Excel workbook
wb = load_workbook('Source.xlsx')
sheet = wb.active
translator = Translator()
# Specify the range of cells containing the data you want to translate
start_row = 2 # Assuming row 1 is header
end_row = sheet.max_row
column_to_translate = 14 # Assuming you want to translate the 14th column
# Add the header "Sentiment"
sheet.cell(row=1, column=column_to_translate + 6).value = "En_Translate"
# Translate each cell in the specified range
for row in range(start_row, end_row + 1):
cell_value = sheet.cell(row=row, column=column_to_translate).value
try:
if cell_value is not None:
# Translate the cell value to English
translated_text = translator.translate(cell_value, src='auto', dest='en').text
# Write the translated text back to the cell
sheet.cell(row=row, column=column_to_translate + 6).value = translated_text
else:
# If the cell is empty, set the translated cell to empty as well
sheet.cell(row=row, column=column_to_translate + 6).value = ""
except Exception as e:
print(f"Error translating cell {row}, {column_to_translate}: {e}")
# Save the updated workbook
wb.save('./Output/Output.xlsx')
print("Translation completed and saved!")
I'm expecting to run the code properly so I can work on the next step.
