1. set up anaconda (Python included)
download (for Linux only)
wget https://repo.anaconda.com/archive/Anaconda3-2019.03-Linux-x86_64.sh
install
bash Anaconda3-2019.03-Linux-x86_64.sh
create new env. (With Python3.7)
conda create --name google_translate_py37 python=3.7
2. install dependencies
conda install -y requests xlrd openpyxl pandas
3. run python script
3.1 Google will frequent request with google-translate API
So please set up a proper time interval for each request like this: (set interval time: 3 seconds, and it can be set to a larger value according to your own situation)
image.png
3.2 run
source activate google_translate_py37
python simple_google_translate-20190804.py
- 3.2.1 Ok to request
image.png
image.png
- 3.2.2 Failed due to frequent requests
image.png
[Details]
- Script
import csv
import os
import requests
import sys
#import xlrd
import pandas as pd
import numpy as np
ERR_429 = 'request_err_429'
ERR_UNKNOWN = 'request_err_unknown'
new_xlsx_name = 'google_translate.xlsx'
new_sheet_name = 'google_translate_sheet'
# https://www.jb51.net/article/163320.htm
def showExcelSheetHead(data_frame):
#print('----------------------------------------------------------------------------------------')
data = data_frame.head()
print("[+] showExcelSheetHead:\n{}".format(data))
# https://blog.csdn.net/weixin_43245453/article/details/90747259
def selectExcelSheet(file_path):
print('----------------------------------------------------------------------------------------')
print("[+] selectExcelSheet")
data = pd.read_excel(file_path, None)
#print("sheets\t\t{}".format(data.keys()))
sheets = []
for i, sheet_name in enumerate(data.keys()):
sheets.append(sheet_name)
print("\t[-] sheet {}:\t{}".format(i, sheet_name))
chooice = int(input('Please select a sheet [sheet number] '))
return sheets[chooice]
def selectExcelTitle(file_path, sheet_name):
print('----------------------------------------------------------------------------------------')
print("[+] selectExcelColumn")
sheet_content = pd.DataFrame(pd.read_excel(file_path, sheet_name))
showExcelSheetHead(sheet_content)
print()
titles = []
titles.append( (sheet_content.columns)[0] )
titles.append( (sheet_content.columns)[1] )
for i, title in enumerate(titles):
print('\t[-] title {}:\t{}'.format(i, title))
chooice = int(input('Please select a column (keywords_title) to translate [column number] '))
return titles[chooice], sheet_content
def getExcelShape(data_frame):
return data_frame.shape
def getExcelRows(data_frame):
return data_frame.shape[0]
def getExcelColumns(data_frame):
return data_frame.shape[1]
# http://pytolearn.csd.auth.gr/b4-pandas/40/moddfcols.html
def insertExcelRow(data_frame, row_index, row_list):
# insert a row
data_frame.loc[row_index] = row_list
def insertExcelColumn(data_frame, column_index, column_name, column_list):
# insert a column
data_frame.insert(column_index, column_name, column_list)
def appendExcelColumn(data_frame, column_name, column_list):
# append a column
#data_frame[column_name] = column_list
# append an empty column
data_frame[column_name] = pd.Series()
# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.replace.html
# https://blog.csdn.net/sscc_learning/article/details/76993816
def updateExcelCell(data_frame, row_index, column_name, value):
data_frame[column_name][row_index] = value
# Translate Excel columns
def translateExcelColumns(data_frame, keywords_title):
print('----------------------------------------------------------------------------------------')
print("[+] translateExcelColumns")
global ERR_429
global ERR_UNKNOWN
global new_xlsx_name
global new_sheet_name
if new_sheet_name == '':
new_xlsx_name = input('Please name the new xlsx [xlsx name] ')
if new_sheet_name == '':
new_sheet_name = input('Please name the new sheet [sheet name] ')
translation_title = 'Google Translate'
appendExcelColumn(data_frame, translation_title, [])
nrows = getExcelRows(data_frame)
print('[*] number of keywords to be translated:\t{}\n\n'.format(nrows))
for i in range(0, nrows):
keywords = data_frame[keywords_title][i]
#print("[-] translate keywords:", keywords)
res = translateLine(keywords)
if res == ERR_429:
print('(T_T) You are requesting "Google Translate" too frequently, please have a rest...')
return
elif res == ERR_UNKNOWN:
print('(T_T) Unknown error happened')
return
updateExcelCell(data_frame, i, translation_title, res)
if i % 10 == 0:
data_frame.to_excel(new_xlsx_name, sheet_name=new_sheet_name)
# simple way: ok
# https://blog.csdn.net/zcoutman/article/details/69062422
def translateLine(keywords):
print("\t[-] translateLine:\t{}".format(keywords))
global ERR_429
global ERR_UNKNOWN
# 1. isnan
if type(keywords) == float:
if np.isnan(keywords):
#print("[-] keywords is nan:", keywords)
return ''
# 2. strip blank space
keywords = str(keywords).strip()
# 3. skip empty keywords
if keywords == None:
#print("[-] keywords is None:", keywords)
return ''
elif len(keywords) == 0:
#print("[-] keywords len is 0:", keywords)
return ''
elif keywords.isspace():
#print("[-] keywords is blank space:", keywords)
return ''
# 4. translate
source_language = 'en'
target_language = 'zh-CN'
content = 'http://translate.google.cn/translate_a/single?client=gtx&sl=' + source_language + '&tl=' + target_language + '&dt=t&q=' + keywords
response = requests.post(content)
#print("[-] response: ", response)
#print("[-] response status: ", response.status_code)
if response.status_code == 200:
res = response.json()
#print("[-] res: ", res)
search = res[0][0][1]
result = res[0][0][0]
print("\t\t\t\t---> {}".format(result))
elif response.status_code == 429:
result = ERR_429
else:
result = ERR_UNKNOWN
return result
def selectExcelFile():
print('----------------------------------------------------------------------------------------')
print("[+] selectExcelFile")
files = []
for i, file in enumerate(os.listdir('./')):
files.append(file)
if file.endswith('.xlsx'):
print('\t[-] file {}:\t{}'.format(i, file))
chooice = int(input('Please select a xlsx file [file number] '))
return files[chooice]
def translateOneWords(keywords):
translateLine(keywords)
def translateExcel():
file_path = selectExcelFile()
print('[*] file_path:\t{}'.format(file_path))
sheet_name = selectExcelSheet(file_path)
print('[*] sheet_name:\t{}'.format(sheet_name))
keywords_title, data_frame = selectExcelTitle(file_path, sheet_name)
print('[*] keywords_title:\t{}'.format(keywords_title))
pd.set_option('mode.chained_assignment', None)
translateExcelColumns(data_frame, keywords_title)
# start from here
if __name__ == '__main__':
#translateOneWords('pancake')
translateExcel()
print('----------------------------------------------------------------------------------------')
