如何处理将UTF8编码的文件(代码页=65001)导入到SQL SERVER中

在挪威，我们有3个非常令人讨厌的角色，æøaka，他们制造了各种各样的问题。自2008年以来，微软决定不再支持代码页65001。对于使用OPENROWSET(BULK)将UTF-8文件导入到SQL SERVER中并保留æøaka令牌的问题，我找到了一个可管理的解决方案。

我创建了一个PowerShell脚本，该脚本使用StreamReader和StreamWriter将文件从UTF-8转换为默认编码ANSI。

$filename = "C:TestUTF8_file.txt"
$outfile = "C:TestANSI_file.txt"
$reader = new-object System.IO.StreamReader($filename, [System.Text.Encoding]::GetEncoding(65001))
$stream = new-object System.IO.StreamWriter($outfile, $false, [System.Text.Encoding]::Default)

我在同一进程中剥离了文件的第一行，即标题行。

$i=1
while(($line = $reader.ReadLine()) -ne $null) {
    if($i -gt 1) {
        $stream.WriteLine($line)
    }
    $i++
}
$reader.Close()
$stream.Close()

然后，我可以使用OPENROWSET将ANSI文件导入到SQL服务器中，并在此过程中操作数据。使用代码页1252，它等于丹麦_挪威排序规则。

    insert into SomeDatabase.dbo.SomeTable
SELECT [companynumber]
, case [role] when 'Styreformann' then 'Styreleder' when 'Styrets leder' then 'Styreleder' else rolle end as 'role'
, case [representant] when 'Y' then '1' else '0' end as 'representant'
, left((RIGHT('0000'+ CONVERT(VARCHAR,postnr),5)),4) end as 'postnr' 
, income*1000 as income
, null as person2id 
FROM OPENROWSET( BULK 'C:TestANSI_file.txt', 
FORMATFILE = 'C:TestFormatBulkInsert_file.xml'
, CODEPAGE =1252
, ROWS_PER_BATCH = 50000    
) as v 

此方法确保了挪威令牌的正确显示。格式化文件如下所示：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR=';"' />
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR='";"' />
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR='";"' />
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR='";' />
    <FIELD ID="5" xsi:type="CharTerm" TERMINATOR=';' />
    <FIELD ID="6" xsi:type="CharTerm" TERMINATOR='
' />
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="companynumber" xsi:type="SQLINT"/>
    <COLUMN SOURCE="2" NAME="role" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="3" NAME="representant" xsi:type="SQLBIT"/>
    <COLUMN SOURCE="4" NAME="postnr" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="5" NAME="income" xsi:type="SQLDECIMAL"/>
    <COLUMN SOURCE="6" NAME="person2id" xsi:type="SQLINT"/>
  </ROW>
</BCPFORMAT>

希望这对其他人有帮助，因为我花了相当多的时间在Google上搜索才找到解决这个问题的方法。

---

解决方案

改为转换为UTF16。这是SQL Server的原生NCHAR格式，并允许完全表示Unicode值。

要执行此操作，您必须在格式化文件中指定SQLNCHAR或SQLNVARCHAR，并注意以下注意事项：

要使格式化文件使用Unicode字符数据文件，所有输入字段都必须是Unicode文本字符串(即固定大小或以字符结尾的Unicode字符串)。

• http://msdn.microsoft.com/en-us/library/ms178129.aspx

另一种方法是将其作为二进制数据加载，并使用CONVERT函数将其从VARBINARY转换为NVARCHAR(即UTF-16)，然后转换为所需的代码页，即VARCHAR。