使用OPENROWSET BULK向SQL Server 2008插入特殊字符时乱码

在Python项目中使用OPENROWSET BULK向SQL Server 2008数据库表插入数据时，生成的CSV文件包含Ñ、á、é等特殊字符，但插入后这些字符出现乱码。

操作步骤 #

1. 生成CSV文件：

dfClientes.to_csv(cvs_path, index=False, sep=';', encoding='utf-8-sig')

2. 复制文件到指定路径：

shutil.copy(cvs_path, ruta_destinoCSV)

3. 执行OPENROWSET BULK插入：

bulk_insert_query = f'''INSERT INTO sgCliente (IdCliente,NomCliente,ApeCliente,NumDocumento,IdTipoDoc,NomTipoDoc,CodTipoDoc,Tip_docu,Sexo,FactorRh,GrupSangre,EstadoCivil,FecNacimiento,eMail,TelMovil,PrimerNombre,SegundoNombre,PrimerApellido,IndCentroExcelencia,IndDatosPersonal,IndHabilitado,IdTercero,CodUES)
SELECT * FROM  OPENROWSET(
BULK '{ ruta_destinoCSV }',
FORMATFILE='{ ruta_destinoXML }',
CODEPAGE = 'UTF-8-SIG',    
FIRSTROW = 2) as t
'''

4. 使用的XML格式文件内容：

<?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=';' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="101" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="101" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="5" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="6" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="7" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="8" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="3" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="9" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="254" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="10" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="254" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="11" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="254" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="12" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="254" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="13" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="8"/>
      <FIELD ID="14" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="15" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="15" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="16" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="17" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="18" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="19" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="50" />
      <FIELD ID="20" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="50" />
      <FIELD ID="21" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="50" />
      <FIELD ID="22" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="50" />
      <FIELD ID="23" xsi:type="CharTerm" TERMINATOR='\r\n' MAX_LENGTH="5" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
   </RECORD>
   <ROW>
      <COLUMN SOURCE="1" NAME="IdCliente" xsi:type="SQLINT"/>
      <COLUMN SOURCE="2" NAME="NomCliente" xsi:type="SQLNVARCHAR"/>
      <COLUMN SOURCE="3" NAME="ApeCliente" xsi:type="SQLNVARCHAR"/>
      <COLUMN SOURCE="4" NAME="NumDocumento" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="5" NAME="IdTipoDoc" xsi:type="SQLTINYINT"/>
      <COLUMN SOURCE="6" NAME="NomTipoDoc" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="7" NAME="CodTipoDoc" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="8" NAME="Tip_docu" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="9" NAME="Sexo" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="10" NAME="FactorRh" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="11" NAME="GrupSangre" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="12" NAME="EstadoCivil" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="13" NAME="FecNacimiento" xsi:type="SQLDATETIME"/>
      <COLUMN SOURCE="14" NAME="eMail" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="15" NAME="TelMovil" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="16" NAME="PrimerNombre" xsi:type="SQLNVARCHAR"/>
      <COLUMN SOURCE="17" NAME="SegundoNombre" xsi:type="SQLNVARCHAR"/>
      <COLUMN SOURCE="18" NAME="PrimerApellido" xsi:type="SQLNVARCHAR"/>
      <COLUMN SOURCE="19" NAME="IndCentroExcelencia" xsi:type="SQLBIT"/>
      <COLUMN SOURCE="20" NAME="IndDatosPersonal" xsi:type="SQLBIT"/>
      <COLUMN SOURCE="21" NAME="IndHabilitado" xsi:type="SQLBIT"/>
      <COLUMN SOURCE="22" NAME="IdTercero" xsi:type="SQLINT"/>
      <COLUMN SOURCE="23" NAME="CodUES" xsi:type="SQLVARYCHAR"/>
   </ROW>
</BCPFORMAT>

现象说明 #

• CSV文件中可正常显示Ñ、á、é等特殊字符
• 插入数据库后这些字符变为乱码

解决建议 #

1. 修改XML格式文件字段类型：
   针对存储特殊字符的字段（如NomCliente、ApeCliente等），将<FIELD>的xsi:type从CharTerm改为NCharTerm，同时移除COLLATION属性（Unicode类型无需指定SQL_Latin1排序规则）。示例：

   <FIELD ID="2" xsi:type="NCharTerm" TERMINATOR=';' MAX_LENGTH="101"/>
   

   所有涉及特殊字符的字段都需执行此修改。

2. 确认数据库表字段类型：
   确保sgCliente表中存储特殊字符的字段类型为NVARCHAR而非VARCHAR，仅Unicode类型能正确存储多语言字符。

3. 调整OPENROWSET代码页参数：
   若上述修改无效，可尝试将CODEPAGE改为65001（UTF-8的代码页编号），替代UTF-8-SIG，部分SQL Server版本对代码页编号兼容性更好：

   CODEPAGE = '65001'
   

4. 验证CSV编码一致性：
   确认生成的CSV确实为UTF-8-SIG编码，可通过文本编辑器查看编码信息，避免文件复制过程中编码被篡改。

内容的提问来源于stack exchange，提问作者ANDREA CAROLINA CAMACHO JULIO