问题描述
我正在尝试使用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
另请参见 http://msdn.microsoft.com/en-us/library/ms188289.aspx
这篇关于使用BCP将数据导入SQL Server的同时保留重音符号,亚洲字符等的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!