BULK INSERT的ROWTERMINATOR失效，SQL Server导入CSV异常求助

CSV数据示例 #

"Phone Numbers","Opstype"
"9827259163","D"
"9827961481","D"
"9827202228","A"
"9827529897","A"
"9827700249","A"

执行的BULK INSERT语句 #

BULK INSERT dbo.tbl_dnd FROM "C:\DND_1.csv" WITH (DATAFILETYPE='char',FIRSTROW=2,FIELDTERMINATOR='","',ROWTERMINATOR='"\n"');

导入后的错误数据示例 #

注：导入后仅得到50万条记录，仅为预期100万条的一半；且Opstype字段包含多余引号和其他手机号数据，未仅显示单个字母。

问题原因与解决方法 #

1. 核心错误点 #

• 行终止符配置错误：Windows系统下CSV的换行符是\r\n，你设置的ROWTERMINATOR='"\n"'无法正确识别行结束位置，导致SQL Server将两行数据合并为一行，直接造成记录数减半，同时把下一行的手机号错误合并到当前行的Opstype字段。
• 字段引号未处理：当前的FIELDTERMINATOR='","'只能分隔字段间的符号，但每个字段开头的引号和最后一个字段结尾的引号未被去除，导致Opstype字段残留引号。

修正方案 #

方法一：使用FIELDQUOTE参数（SQL Server 2017及以上版本推荐）

直接指定字段的包围引号，让SQL Server自动处理引号的去除：

BULK INSERT dbo.tbl_dnd 
FROM "C:\DND_1.csv" 
WITH (
    DATAFILETYPE = 'char',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\r\n',
    FIELDQUOTE = '"' -- 指定字段的包围引号
);

方法二：使用格式文件（兼容旧版本）

创建格式文件dnd_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="15"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR='"\r\n' MAX_LENGTH="2"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Phone Numbers" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="2" NAME="Opstype" xsi:type="SQLVARCHAR"/>
 </ROW>
</BCPFORMAT>

执行导入：

BULK INSERT dbo.tbl_dnd 
FROM "C:\DND_1.csv" 
WITH (
    DATAFILETYPE = 'char',
    FIRSTROW = 2,
    FORMATFILE = 'C:\dnd_format.xml'
);

验证效果 #

修正后可正确导入全部100万条记录，Opstype字段仅显示"A"或"D"，无多余字符。

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