Docker环境下SQL Server导入带引号及换行的CSV遇错误求助

问题背景 #

• 运行环境：Docker容器内的Linux版SQL Server，无桌面导入工具
• CSV特征：所有字段以双引号包裹，分隔符为分号;；部分字段包含内部双引号、换行符
• 前置操作：已将CSV复制到容器路径/home/20240911_FicheroIncidentesCriticos.csv，创建目标表Incidentes

尝试操作及错误 #

第一次导入尝试 #

执行以下BULK INSERT语句：

BULK INSERT Incidentes
FROM '/home/20240911_FicheroIncidentesCriticos.csv'
WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '\n', FIRSTROW = 2, TEXTQUALIFIER = '"', MAXERRORS = 20);

报错：

Msg 102, Level 15, State 1, Server sql1, Line 3
Incorrect syntax near 'TEXTQUALIFIER'.

移除TEXTQUALIFIER后的错误 #

移除该参数后执行，出现两类错误：

1. RID列类型不匹配：

Msg 4864, Level 16, State 1, Server sql1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 4 (RID).
原因：RID字段被双引号包裹，直接导入会把"R4"作为值，与NVARCHAR(4)的预期值R4不匹配。

2. 换行导致的截断错误：

Bulk load data conversion error (truncation) for row 5, column 1 (MID).
Msg 4864, Level 16, State 1, Server sql1, Line 1
原因：Description字段包含换行符，默认的ROWTERMINATOR = '\n'会把字段内的换行识别为行结束符，导致行拆分错误。

目标表结构 #

COLUMN_NAME            DATA_TYPE  CHARACTER_MAXIMUM_LENGTH
---------------------- ---------- ------------------------
MID                    nvarchar                         30
PID                    nvarchar                         30
RID                    nvarchar                          4
CID                    int                            NULL
IsAType                bit                            NULL
IsBType                bit                            NULL
IsCType                bit                            NULL
Attribute              nvarchar                        100
Description            nvarchar                         -1
Note                   nvarchar                         -1

可行解决方案 #

SQL Server的BULK INSERT不支持TEXTQUALIFIER参数，必须通过**格式文件（Format File）**来处理带引号的字段，同时正确识别包含换行的字段。

步骤1：创建XML格式文件 #

在容器内创建格式文件（比如/home/incidentes_format.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="30"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR='";"' MAX_LENGTH="30"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR='";"' MAX_LENGTH="4"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR='";"' MAX_LENGTH="10"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="5"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR=';' MAX_LENGTH="5"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR=';"' MAX_LENGTH="5"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR='";"' MAX_LENGTH="100"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR='";"' MAX_LENGTH="-1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR='"\r\n' MAX_LENGTH="-1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="MID" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="PID" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="RID" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="CID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="5" NAME="IsAType" xsi:type="SQLBIT"/>
  <COLUMN SOURCE="6" NAME="IsBType" xsi:type="SQLBIT"/>
  <COLUMN SOURCE="7" NAME="IsCType" xsi:type="SQLBIT"/>
  <COLUMN SOURCE="8" NAME="Attribute" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="9" NAME="Description" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="10" NAME="Note" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

格式文件说明：

• 带双引号的字符串字段，用TERMINATOR='";"'匹配";"结束符，自动剥离前后双引号
• CID字段从带引号的字符串自动转换为INT类型
• IsAType/IsBType/IsCType字段未被双引号包裹，设置TERMINATOR=';'匹配分隔符
• Description和Note字段用MAX_LENGTH="-1"支持长文本，Note字段的TERMINATOR='"\r\n'匹配行尾标识，避免字段内换行被误判为行结束

步骤2：执行BULK INSERT #

使用格式文件执行导入：

BULK INSERT Incidentes
FROM '/home/20240911_FicheroIncidentesCriticos.csv'
WITH (
    FORMATFILE = '/home/incidentes_format.xml',
    FIRSTROW = 2,
    MAXERRORS = 20,
    CODEPAGE = '65001' -- CSV为UTF-8编码时添加此参数
);

备选方案：使用OPENROWSET(BULK...) #

如果格式文件仍有问题，可尝试OPENROWSET结合格式文件，更灵活处理特殊字符：

INSERT INTO Incidentes
SELECT *
FROM OPENROWSET(
    BULK '/home/20240911_FicheroIncidentesCriticos.csv',
    FORMATFILE = '/home/incidentes_format.xml',
    FIRSTROW = 2
) AS t;

额外注意事项 #

• 换行符适配：如果CSV是Linux下的\n换行，将格式文件中的"\r\n"改为"\n"
• 编码适配：UTF-8编码的CSV必须添加CODEPAGE = '65001'，避免特殊字符乱码
• 内部双引号：格式文件会自动处理字段内的转义双引号（如"quotes"），识别为单个双引号

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