bcp导出unicode数据 [英] bcp export for unicode data

问题描述

我在sql server中使用bcp命令，借助以下命令将查询生成的数据导出到.csv文件

 xp_cmdshell bcp   select * from table1 QUERYOUT / c / t，-T -S 

工作正常并按预期导出数据但是现在我们有一个包含多语言数据的列，并且在csv文件中使用上面的命令数据导出显示为?????????。



做了一些谷歌搜索后，我发现了一些像-w而不是/ c的其他开关用于unicode字符，但此选项正在创建unicode文件，并且不能正确打开excel（列不用逗号（，）分隔）。我也注意到如果我删除/ t，那么它将列分开并且看起来很好但是这里的列由制表符分隔而不是逗号我不需要。



如果我遗失任何东西，有人可以帮助我吗？

解决方案

请阅读RedDK的评论并按照他提供的链接：

RedDK写道：

可能需要设置-C选项。如果不是ACP，OEM或RAW，则可以输入特定代码页。在TSQL中也没有办法说出来，使用相同的关键字，在字符串的某处？

参见 http://technet.microsoft.com/en-us/library/ms162802.aspx [ ^ ]

看一看其他 [ ^ ]。



相反：

 xp_cmdshell bcp   select * from table1 QUERYOUT / c / t，-T -S 

尝试使用：

 xp_cmdshell bcp   select * from table1 QUERYOUT -w -t，-T -S 

别忘了定义代码页！

I am using bcp command in sql server to export data generated from a query to .csv file with the help of following command

xp_cmdshell bcp "select * from table1" QUERYOUT /c /t, -T -S 

It is working fine and exporting data as expected but now we have a column which contain multilingual data and during exporting with above command data in csv file shows as "????????".

After doing some googling I found some other switch like -w instead of /c to be used for unicode character but this option is creating unicode file and does not open in excel properly(columns are not separated by comma(,)). I also noticed that if I remove /t, then its separating the column and looks fine but here column is separated by tabs not comma which I don't need.

Can anybody help me if I am missing anything?

解决方案

Please, read the comment of RedDK and follow the link provided by Him:

RedDK wrote:

Possibly the -C option needs to be set. Specific codepage can be input, if its not ACP, OEM, or RAW. Isn't there a way to say it in TSQL also, using same keywords, somewhere in the " " string?
See http://technet.microsoft.com/en-us/library/ms162802.aspx[^]

and have a look here[^].

Instead:

xp_cmdshell bcp "select * from table1" QUERYOUT /c /t, -T -S 

try to use:

xp_cmdshell bcp "select * from table1" QUERYOUT -w -t, -T -S 

Do not forget to define codepage!

这篇关于bcp导出unicode数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！