如何将utf-8数据从sql server 2008导出到excel [英] How to Export utf-8 data from sql server 2008 to excel

问题描述

我尝试通过两个选项从sql server 2008导出utf-8数据到excel。但是data-utf-8不正确

示例我有一个表有Name列（排序规则：SQL_Latin1_General_CP1_CI_AS，nvarchar（3000））

I try to export utf-8 data from sql server 2008 to excel by two option. But data-utf-8 is not correct
Example I have a table has Name column (Collation: SQL_Latin1_General_CP1_CI_AS, nvarchar(3000))

|Name               |
|TrÆ°á»ng ÄH Dược|

如果我使用asp代码在浏览器上显示

If i using asp code to show it on browser with

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

数据将显示正确，如

|Name               |
|Trường ĐH Dược     |

但是当我导出为ex​​cel时：

But when i export to excel with:

选项1我使用

Enable Ad Hoc Distributed Queries

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO


INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\testing.xls;', 'SELECT Name, Email FROM [Sheet1$]')
SELECT Name, Email FROM tblnames
GO

或选项2：我使用

SQL Server Import and Export Wizard

但是这两个选项都没有显示正确的data-utf-8。它仍然像

But both option don't show correct data-utf-8. It still like

 |Name               |
 |TrÆ°á»ng ÄH Dược|

如何将utf-8数据从sql server 2008导出到excel谢谢

How to Export utf-8 data from sql server 2008 to excel thanks

推荐答案

您可以尝试BCP批量导出，但2008R2

You can try BCP bulk export, but 2008R2

SQL服务器不支持代码页65001（UTF-8编码）。 参考

您需要在bcp实用程序中添加-w参数以指定编码为UTF16。

You need to add -w parameter in bcp utility to specify the encoding is UTF16.

DECLARE @cmd varchar(1000)
    SET @cmd = 'bcp "select * from table" queryout "d:\file.csv" -w -T -t; -Slocalhost'
    EXEC xp_cmdshell @cmd

然后尝试打开csv in excel

Then Try open csv in excel

这篇关于如何将utf-8数据从sql server 2008导出到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！