使用BCP将数据导入SQL Server的同时保留重音符号，亚洲字符等 [英] Using BCP to import data to SQL Server while preserving accents, Asian characters, etc

问题描述

我正在尝试使用bcp将PostgreSQL数据转储导入SQL Server.我已经编写了Python脚本来将定界符切换为'^'，并消除了其他不良格式，但是当导入SQL Server时，我找不到正确的开关来保留字符串的unicode格式.

I'm trying to import a PostgreSQL dump of data into SQL Server using bcp. I've written a Python script to switches delimiters into '^' and eliminate other bad formatting, but I cannot find the correct switches to preserve unicode formatting for the strings when importing into SQL Server.

在Python中，如果我打印出引起麻烦的行，则使用csv模块的行如下所示:

In Python, if I print out the lines that are causing me trouble, the row looks like this with the csv module:

['12', '\xe4\xb8\x89\xe5\x8e\x9f \xe3\x81\x95\xe3\x81\xa8\xe5\xbf\x97']

数据库表只有两列:一列integer，一列varchar.

The database table only has 2 columns: one integer, one varchar.

我创建表的语句(简体)仅:

My statement (simplified) for creating the table is only:

CREATE TABLE [dbo].[example](
    [ID] [int] NOT NULL,
    [Comment] [nvarchar](max)
    )

要运行bcp，我使用以下行:

And to run bcp, I'm using this line:

c:\>bcp dbo.example in fileinput -S servername -T -t^^ -c

它成功导入了大约一百万行，但是我所有的重音字符都坏了.

It successfully imports about a million rows, but all of my accented characters are broken.

例如，将Böhm，Rüdiger"转变为"B + hm，R ++ diger".有没有人有过如何使用bcp正确设置开关或其他提示的经验?

For example, "Böhm, Rüdiger" is turned into "B+¦hm, R++diger". Does anyone have experience with how to properly set switches or other hints with bcp?

编辑:varchar切换为nvarchar，但这不能解决问题.此输出在Python中(使用CSV模块读取):

Edit: varchar switched to nvarchar, but this does not fix the issue. This output in Python (reading with CSV module):

['62', 'B\xc3\xb6hm, R\xc3\xbcdiger']

在目标数据库的SSMS中，

以此显示(匹配分隔符以保持一致性):

is displayed as this in SSMS from the destination DB (delimiters matched for consistency):

select * from dbo.example where id = 62
62;"B├╢hm, R├╝diger"

在pgAdmin中使用原始数据库的地方，我有这个:

where in pgAdmin, using the original DB, I have this:

62;"Böhm, Rüdiger"

推荐答案

您可能需要修改BCP命令以支持宽字符集(请注意使用-w而不是-c开关)

You may need to modify your BCP command to support wide character sets (note the use of -w instead of -c switch)

bcp dbo.example in fileinput -S servername -T -t^^ -w

BCP文档参考

另请参见 http://msdn.microsoft.com/en-us/library/ms188289.aspx

这篇关于使用BCP将数据导入SQL Server的同时保留重音符号，亚洲字符等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！