使用Bulk Insert配合XML格式文件导入65001编码Unicode文件丢字符问题

现有配置无法正确导入Unicode定长文件的核心原因有两点：

• XML格式文件中使用的CharFixed字段类型、SQLCHAR列类型仅适配单字节ANSI/UTF-8编码，无法识别UTF-16LE（Windows平台Unicode默认编码）的双字节字符，会拆分字符解析导致乱码、字段长度计算错位
• Bulk Insert参数中codepage = 65001是指定按UTF-8编码解析文件，和Unicode定长文件的编码规则不匹配

第一步：替换XML格式文件 #

所有定长字段类型从CharFixed改为NCharFixed（宽字符定长类型），列类型从SQLCHAR改为SQLNCHAR（SQL Server Unicode字符类型）。注意NCharFixed的LENGTH属性按字节计数，UTF-16编码下每个字符占2字节，因此原字符长度全部乘以2；单独拆分换行符识别字段，避免行尾标识和最后一个业务字段混淆。

<?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="NCharFixed" LENGTH="44"/>
  <FIELD ID="2" xsi:type="NCharFixed" LENGTH="8"/>  
  <FIELD ID="3" xsi:type="NCharFixed" LENGTH="6"/>
  <FIELD ID="4" xsi:type="NCharFixed" LENGTH="18"/>
  <FIELD ID="5" xsi:type="NCharFixed" LENGTH="6"/>
  <FIELD ID="6" xsi:type="NCharFixed" LENGTH="120"/>
  <FIELD ID="7" xsi:type="NCharFixed" LENGTH="2"/>
  <FIELD ID="8" xsi:type="NCharTerm" TERMINATOR="\r\n"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="Reserved" xsi:type="SQLNCHAR" LENGTH="22"/>
  <COLUMN SOURCE="2" NAME="DataSupplierNumberConstant" xsi:type="SQLNCHAR" LENGTH="4"/>
  <COLUMN SOURCE="3" NAME="DataTableConstant" xsi:type="SQLNCHAR" LENGTH="3"/>
  <COLUMN SOURCE="4" NAME="DescriptionNumber" xsi:type="SQLNCHAR" LENGTH="9"/>
  <COLUMN SOURCE="5" NAME="LanguageNumber" xsi:type="SQLNCHAR" LENGTH="3"/>
  <COLUMN SOURCE="6" NAME="Description" xsi:type="SQLNCHAR" LENGTH="60"/>
  <COLUMN SOURCE="7" NAME="EraseFlag" xsi:type="SQLNCHAR" LENGTH="1"/>
</ROW>
</BCPFORMAT>

第二步：调整Bulk Insert语句 #

删除原有的codepage参数，添加DATAFILETYPE = 'widechar'指定文件按UTF-16LE Unicode格式解析：

Bulk Insert LanguageDescriptionsTest 
from 'R:\SQLBackupsIncoming\TecDocDataDownload\Temp\030c.dat'
with
(
    firstrow = 1, 
    lastrow = 1,
    DATAFILETYPE = 'widechar',
    formatfile = 'R:\SQLBackupsIncoming\TecDocDataDownload\LanguageDescriptions_FormatFileTest.xml'
)

• 目标表LanguageDescriptionsTest中存储文本的字段（尤其是Description字段）必须使用nchar/nvarchar这类Unicode类型，不能用char/varchar，否则导入后依然会出现非ASCII字符丢失
• 上述配置可直接识别带BOM或无BOM的UTF-16LE格式定长文件，你示例行中的Achsausführung这类带变音符号的字符可完整保留
• 全量导入前建议先导入1-2行验证字段错位情况，确认字段长度匹配后再执行全量操作

内容的提问来源于stack exchange，提问作者Bob Frearson