带有中文字符的批量插入(bulk insert with chinese character)

带有中文字符的批量插入(bulk insert with chinese character)

我想将包含中文字符的csv数据文件批量插入到sql server 2012.csv数据文件的原始编码为utf8，fmt文件由bcp工具生成。 问题是，如果我通过记事本将csv数据文件的编码从utf8转换为unicode（用记事本打开数据文件，然后选择'另存为'并选择'unicode'格式），我可以批量插入数据到数据库。 如果我保留其原始编码，则不会向数据库插入任何内容。

任何人都知道是否存在任何脚本，如python或powershell，可以自动将数据文件从utf-8编码转换为unicode并保留汉字而不改变。 谢谢。

I want to bulk insert a csv data file, which contains Chinese characters, to sql server 2012. the original encoding of the csv data file is utf8, and fmt file is generated by bcp tool. the question is that if I convert the encoding of csv data file from utf8 to unicode via notepad(open the data file with notepad, then choose 'save as' and select the 'unicode' format), I can bulk insert the data to database. And if I keep its original encoding, nothing will be inserted to database.

any one knows if there exists any script, such as python or powershell, can automate to convert data file from utf-8 encoding to unicode and keep the Chinese characters without changing. thanks.

最满意答案

要将UTF-8文档（可能使用UTF-8 BOM）转换为使用Python的UTF-16-LE：

import io with io.open("my_input_file.txt", "r", encoding="utf-8-sig") as my_input: with io.open("my_output_file.txt", "w", encoding="UTF-16-LE") as my_output: my_ouput.write( u"\uFEFF" ) # write a UTF BOM my_ouput.write( my_input.read() )

To convert a UTF-8 document, possibly with a UTF-8 BOM, to UTF-16-LE using Python:

import io with io.open("my_input_file.txt", "r", encoding="utf-8-sig") as my_input: with io.open("my_output_file.txt", "w", encoding="UTF-16-LE") as my_output: my_ouput.write( u"\uFEFF" ) # write a UTF BOM my_ouput.write( my_input.read() )