Why translation does not work for more than one line of text in my file?

Question

import deepl
import pandas as pd

auth_key = "MY_API"  # Replace with your key
translator = deepl.Translator(auth_key)

input_file = "test.xlsx"
output_file = "output.xlsx"

# Read the entire Excel file without headers
df = pd.read_excel(input_file, header=None)

# Translate each cell in the DataFrame
translated_data = df.applymap(lambda text: translator.translate_text(str(text), target_lang="DE"))

# Save the translated data back to an Excel file
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
    translated_data.to_excel(writer, index=False, header=False)

print("Translation completed and saved to output.xlsx")

I tried to translate some text in xlsx file and when i add more data to my input file then program could not handle it.

I got copywriting in html format saved in xlsx file and i would like to translate it and save it as new still with html sings in it.

Maybe there is another, better way to achieve it then Deepl API.

input file: go.wetransfer.com/t-NzftoJCfYx

Answer 1

The following code works for me using (excel_example.xlsx is a sample table I made in numbers and exported to excel, with A1="This is an example sentence", A2="This is another example sentence".):

1. python 3.10
2. deepl v1.15.0
3. pandas v2.1.2

import pandas as pd
import deepl

input_file = 'excel_example.xlsx'
df = pd.read_excel(input_file, header=None)
df.applymap(lambda text: print(text))
# prints the following:
# <stdin>:1: FutureWarning: DataFrame.applymap has been deprecated. Use DataFrame.map instead.
# Table 1
# This is an example sentence.
# This is another example sentence.
#      0
# 0  None
# 1  None
# 2  None
t = deepl.Translator('MY_API_KEY')
res = df.applymap(lambda text: t.translate_text(str(text), target_lang="DE"))
print(res)
# prints the following
#                                      0
# 0                            Tabelle 1
# 1           Dies ist ein Beispielsatz.
# 2  Dies ist ein weiterer Beispielsatz.

If your excel file has HTML tags in the text, you would need to enable HTML tag handling like so:

res = df.applymap(lambda text: t.translate_text(str(text), target_lang="DE", tag_handling='html'))

For any more concrete help (eg if writing back to the excel file doesn't work) we would need a specific error message.