使用BULK INSERT导入CSV时最后一行列数不足的报错解决方案咨询

我明白你的困扰——每次手动修改CSV末尾的逗号太麻烦了，咱们直接从SQL端搞定这个问题。下面给你几个逐步推进的解决方案：

方案1：明确指定字段和行终止符（优先尝试） #

你之前试过设置ROWTERMINATOR但没成功，大概率是没和FIELDTERMINATOR配合使用。SQL Server的BULK INSERT有时候需要明确的规则来解析CSV，尤其是最后一行没有结尾逗号的情况。

修改你的BULK INSERT语句如下：

DROP TABLE IF EXISTS #temp_FeirasLivres
CREATE TABLE #temp_FeirasLivres (
 ID INT null,
 LONG BIGINT null,
 LAT BIGINT null,
 SETCENS BIGINT null,
 AREAP BIGINT null,
 CODDIST INT null,
 DISTRITO NVARCHAR(100) null,
 CODSUBPREF INT null,
 SUBPREFE NVARCHAR(200) null,
 REGIAO5 NVARCHAR(200) null,
 REGIAO8 NVARCHAR(200) null,
 NOME_FEIRA NVARCHAR(200) null,
 REGISTRO NVARCHAR(50) null,
 LOGRADOURO NVARCHAR(100) null,
 NUMERO NVARCHAR(200) null default('S/N'),
 BAIRRO NVARCHAR(50) null default(''),
 REFERENCIA NVARCHAR(100) null
)

BULK INSERT #temp_FeirasLivres
FROM 'DEINFO_AB_FEIRASLIVRES_2014.csv'
WITH (
 FORMAT = 'CSV',
 FirstRow = 1,
 FIELDTERMINATOR = ',', -- 明确指定字段分隔符为逗号
 ROWTERMINATOR = '\r\n' -- Windows系统换行符，如果是Linux生成的CSV改成'\n'
);

原理：通过明确告诉SQL Server字段用逗号分隔、行用换行符结束，它就不会依赖最后一个逗号来判断行的结束，哪怕最后一行没有结尾逗号，也能正确识别最后一列的内容。

方案2：使用格式文件（更精准的列映射） #

如果方案1还是不行，咱们可以用XML格式文件来定义每一列的解析规则，完全规避逗号缺失的问题。

步骤1：创建格式文件（比如命名为FeirasLivres.fmt） #

把下面的内容保存成XML文件，放在和CSV同一个目录下：

<?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="10"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100"/>
  <FIELD ID="15" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="200"/>
  <FIELD ID="16" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50"/>
  <FIELD ID="17" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100"/> <!-- 最后一列用换行符作为终止符 -->
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="LONG" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="3" NAME="LAT" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="4" NAME="SETCENS" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="5" NAME="AREAP" xsi:type="SQLBIGINT"/>
  <COLUMN SOURCE="6" NAME="CODDIST" xsi:type="SQLINT"/>
  <COLUMN SOURCE="7" NAME="DISTRITO" xsi:type="SQLNVARCHAR" LENGTH="100"/>
  <COLUMN SOURCE="8" NAME="CODSUBPREF" xsi:type="SQLINT"/>
  <COLUMN SOURCE="9" NAME="SUBPREFE" xsi:type="SQLNVARCHAR" LENGTH="200"/>
  <COLUMN SOURCE="10" NAME="REGIAO5" xsi:type="SQLNVARCHAR" LENGTH="200"/>
  <COLUMN SOURCE="11" NAME="REGIAO8" xsi:type="SQLNVARCHAR" LENGTH="200"/>
  <COLUMN SOURCE="12" NAME="NOME_FEIRA" xsi:type="SQLNVARCHAR" LENGTH="200"/>
  <COLUMN SOURCE="13" NAME="REGISTRO" xsi:type="SQLNVARCHAR" LENGTH="50"/>
  <COLUMN SOURCE="14" NAME="LOGRADOURO" xsi:type="SQLNVARCHAR" LENGTH="100"/>
  <COLUMN SOURCE="15" NAME="NUMERO" xsi:type="SQLNVARCHAR" LENGTH="200"/>
  <COLUMN SOURCE="16" NAME="BAIRRO" xsi:type="SQLNVARCHAR" LENGTH="50"/>
  <COLUMN SOURCE="17" NAME="REFERENCIA" xsi:type="SQLNVARCHAR" LENGTH="100"/>
 </ROW>
</BCPFORMAT>

步骤2：使用格式文件执行BULK INSERT #

修改你的BULK INSERT语句，指定格式文件路径：

BULK INSERT #temp_FeirasLivres
FROM 'DEINFO_AB_FEIRASLIVRES_2014.csv'
WITH (
 FORMATFILE = 'FeirasLivres.fmt', -- 替换成你的格式文件实际路径
 FirstRow = 1
);

原理：格式文件精确定义了每一列的终止符，最后一列直接用换行符作为结束标记，完全不受末尾逗号缺失的影响。

方案3：先导入单行再拆分（终极兜底方案） #

如果前两种方法都不行，咱们换个思路：先把整个CSV的每一行作为字符串导入到临时表，再用SQL拆分每一行的字段。

DROP TABLE IF EXISTS #temp_FeirasLivres
CREATE TABLE #temp_FeirasLivres (
 ID INT null,
 LONG BIGINT null,
 LAT BIGINT null,
 SETCENS BIGINT null,
 AREAP BIGINT null,
 CODDIST INT null,
 DISTRITO NVARCHAR(100) null,
 CODSUBPREF INT null,
 SUBPREFE NVARCHAR(200) null,
 REGIAO5 NVARCHAR(200) null,
 REGIAO8 NVARCHAR(200) null,
 NOME_FEIRA NVARCHAR(200) null,
 REGISTRO NVARCHAR(50) null,
 LOGRADOURO NVARCHAR(100) null,
 NUMERO NVARCHAR(200) null default('S/N'),
 BAIRRO NVARCHAR(50) null default(''),
 REFERENCIA NVARCHAR(100) null
)

-- 先创建一个临时表存每一行的原始内容
DROP TABLE IF EXISTS #temp_CSV
CREATE TABLE #temp_CSV (Line NVARCHAR(MAX));

-- 导入整个CSV的每一行
BULK INSERT #temp_CSV
FROM 'DEINFO_AB_FEIRASLIVRES_2014.csv'
WITH (ROWTERMINATOR = '\r\n');

-- 拆分每一行的字段并插入目标表
INSERT INTO #temp_FeirasLivres (ID, LONG, LAT, SETCENS, AREAP, CODDIST, DISTRITO, CODSUBPREF, SUBPREFE, REGIAO5, REGIAO8, NOME_FEIRA, REGISTRO, LOGRADOURO, NUMERO, BAIRRO, REFERENCIA)
SELECT
    MAX(CASE WHEN rn = 1 THEN TRY_CAST(value AS INT) END) AS ID,
    MAX(CASE WHEN rn = 2 THEN TRY_CAST(value AS BIGINT) END) AS LONG,
    MAX(CASE WHEN rn = 3 THEN TRY_CAST(value AS BIGINT) END) AS LAT,
    MAX(CASE WHEN rn = 4 THEN TRY_CAST(value AS BIGINT) END) AS SETCENS,
    MAX(CASE WHEN rn = 5 THEN TRY_CAST(value AS BIGINT) END) AS AREAP,
    MAX(CASE WHEN rn = 6 THEN TRY_CAST(value AS INT) END) AS CODDIST,
    MAX(CASE WHEN rn = 7 THEN value END) AS DISTRITO,
    MAX(CASE WHEN rn = 8 THEN TRY_CAST(value AS INT) END) AS CODSUBPREF,
    MAX(CASE WHEN rn = 9 THEN value END) AS SUBPREFE,
    MAX(CASE WHEN rn = 10 THEN value END) AS REGIAO5,
    MAX(CASE WHEN rn = 11 THEN value END) AS REGIAO8,
    MAX(CASE WHEN rn = 12 THEN value END) AS NOME_FEIRA,
    MAX(CASE WHEN rn = 13 THEN value END) AS REGISTRO,
    MAX(CASE WHEN rn = 14 THEN value END) AS LOGRADOURO,
    MAX(CASE WHEN rn = 15 THEN value END) AS NUMERO,
    MAX(CASE WHEN rn = 16 THEN value END) AS BAIRRO,
    MAX(CASE WHEN rn = 17 THEN value END) AS REFERENCIA
FROM (
    SELECT 
        Line,
        value,
        -- 给每个字段加行内序号，确保顺序正确
        ROW_NUMBER() OVER (PARTITION BY Line ORDER BY (SELECT NULL)) AS rn
    FROM #temp_CSV
    -- 拆分每一行的逗号分隔字段
    CROSS APPLY STRING_SPLIT(Line, ',')
    -- 跳过空行
    WHERE Line IS NOT NULL AND Line <> ''
) t
GROUP BY Line;

-- 清理临时表
DROP TABLE #temp_CSV;

原理：这种方法完全绕开了BULK INSERT对CSV格式的严格要求，先把所有内容按行导入，再用SQL的字符串拆分功能把每一行拆成对应的列，不管最后有没有逗号都能正确拆分。

内容的提问来源于stack exchange，提问作者Leandro De Mello Fagundes