SQL Server中Openrowset的Unicode/排序规则问题

问题背景 #

CSV文件含法语特殊字符内容：

Côté fenêtres,
carré

使用以下OPENROWSET语句导入：

select * from openrowset(BULK 'C:\Import_Orders\Files\PO.csv', 
FORMATFILE = 'C:\Import_Orders\Format\Cust_441211.fmt.txt') as PO

导入后出现乱码：

C+¦t+¬ fen+¬tres,
Carr+¬

环境：SQL Server 2017 (RTM-CU29-GDR) (KB5014553) - 14.0.3445.2 (X64)
格式文件内容：

11.0
8
1       SQLCHAR             0       250      "|"      1       PARTNO    ""
2       SQLCHAR             0       250      "|"      2       CODE   ""
3       SQLCHAR             0       250      "|"      3       PRICEKG ""
4       SQLCHAR             0       250      "|"      4       FOOTKG    ""
5       SQLCHAR             0       250      "|"      5       LENGTH    ""
6       SQLCHAR             0       250      "|"      6       QTY   ""
7       SQLCHAR             0       250      "|"      7       COLOR ""
8       SQLCHAR             0       250      "\r\n"   8       TOTKG ""

解决方案 #

乱码核心原因是字符编码不匹配，CSV大概率为UTF-8格式，而OPENROWSET默认用系统单字节编码解析，导致特殊字符识别错误。按以下步骤修复：

1. 调整CSV文件编码 #

• 若CSV是UTF-8无BOM格式，SQL Server 2017无法自动识别，用记事本打开文件后「另存为」，编码选择「UTF-8」（带BOM版本），覆盖原文件。
• 也可将CSV转成UTF-16编码，后续需配合格式文件调整。

2. 修改格式文件数据类型 #

原格式文件中SQLCHAR对应单字节编码，无法处理多字节UTF-8字符，需替换为SQLNCHAR（Unicode类型），同时调整字段长度（Unicode字符占2字节，原250单字节可设为500双字节，或设为0表示可变长度）：

11.0
8
1       SQLNCHAR            0       500      "|"      1       PARTNO    ""
2       SQLNCHAR            0       500      "|"      2       CODE   ""
3       SQLNCHAR            0       500      "|"      3       PRICEKG ""
4       SQLNCHAR            0       500      "|"      4       FOOTKG    ""
5       SQLNCHAR            0       500      "|"      5       LENGTH    ""
6       SQLNCHAR            0       500      "|"      6       QTY   ""
7       SQLNCHAR            0       500      "|"      7       COLOR ""
8       SQLNCHAR            0       500      "\r\n"   8       TOTKG ""

• 若CSV用UTF-16编码，需在格式文件第一行版本号后加1（指定Unicode），即第一行改为11.0 1。

3. 在OPENROWSET中指定编码参数 #

若用UTF-8带BOM文件，添加CODEPAGE='65001'参数（65001为UTF-8代码页），同时确保目标表字段为NVARCHAR类型：

select * from openrowset(BULK 'C:\Import_Orders\Files\PO.csv', 
FORMATFILE = 'C:\Import_Orders\Format\Cust_441211.fmt.txt',
CODEPAGE = '65001') as PO

• 若用UTF-16编码，无需指定CODEPAGE，但需确保格式文件已设置Unicode标识。

4. 验证目标表字段类型 #

若需将数据插入目标表，确保对应字段类型为NVARCHAR而非VARCHAR——VARCHAR仅支持单字节编码，无法存储Unicode特殊字符。

补充排查点 #

若以上步骤无效，需确认：

• CSV实际分隔符是否与格式文件中的"|"一致（示例CSV用逗号，格式文件用竖线，可能导致列解析错误）
• 格式文件的字段数量是否与CSV实际列数匹配

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