How to Export utf-8 data from sql server 2008 to e

问题:

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|

If i using asp code to show it on browser with

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

Data will show correct like

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

But when i export to excel with:

Option 1 i using

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

or Option 2: i using

SQL Server Import and Export Wizard

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

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

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

回答1:

You can try BCP bulk export, but 2008R2

SQL Server does not support code page 65001 (UTF-8 encoding). reference

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

Then Try open csv in excel