SQL Server BULK INSERT的ROWTERMINATOR配置问题求助

问题场景 #

正在实现SQL Server的BULK INSERT自动化流程，需将多个文件导入至对应数据表，但首个测试文件就出现异常：

原始文本文件内容 #

Date | Name | ID | Comment
11/11/2016 10:25:29 | Fake Name1 | aa12345 | Fake comment - stuff
14/11/2016 10:43:22 | Fake Name2 | bb23456 | Fake comment - stuff
14/11/2016 12:06:40 | Fake Name3 | cc34567 | Fake comment - stuff
15/11/2016 10:38:33 | Fake Name4 | dd45678 | Fake comment - stuff

尝试操作及异常表现 #

1. 初始BULK INSERT语句：

BULK INSERT dbo.[FakeTable]
FROM '...\FakeTextFile.txt'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ' | ',
    ROWTERMINATOR = '\r\n',
    TABLOCK,
    KEEPNULLS
)

执行无报错，但未插入任何记录；添加ERRORFILE = '...\Fake_error.txt'参数后，也未生成错误日志。

2. 调整行终止符（ROWTERMINATOR）：
   尝试了'0x0a'、'0x0d'、'0x0a0x0d'、'0x0d0x0a'、CHAR(10)、CHAR(13)等多种取值，多数配置下仍无数据插入；仅当使用'0x0a'、'0x0d'或'\n'时，能成功插入首行数据，但后续行均报如下错误：

Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (Date).

3. 尝试XML格式文件：
   创建了自定义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="20"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="4000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="4000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="4000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Date" xsi:type="SQLDATE"/>
<COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="ID" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="Comment" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

使用该文件执行后，仍出现相同的4864错误。

核心原因及解决方法 #

1. 日期格式不匹配（最可能的直接原因） #

报错提示Date列类型转换失败，结合数据中的14/11/2016这类日期：

• SQL Server默认日期格式受服务器语言/会话设置影响，若当前会话采用美式格式（mm/dd/yyyy），14/11/2016会被解析为“14月11日”，因月份无效导致转换失败；而首行11/11/2016无论美式还是英式格式都合法，因此能成功插入。

解决步骤：
在执行BULK INSERT前，先设置会话的日期格式为日/月/年：

SET DATEFORMAT dmy;

再执行原BULK INSERT语句即可。

2. 字段分隔符的空格处理问题 #

原始文件使用|（前后带空格）作为字段分隔符，SQL Server对多字符分隔符的解析可能存在不稳定情况：

• 可尝试预处理文本文件，将|替换为单字符|，同时调整BULK INSERT的FIELDTERMINATOR = '|'；
• 若无法修改文件，可在格式文件中通过PREFIX_LENGTH跳过字段前的空格，或后续用TRIM()函数清理字段内容。

3. 行终止符的实际编码验证 #

用Notepad++等工具打开文本文件，查看行尾标识（可通过“视图->显示符号->显示所有字符”开启）：

• 若行尾是LF（Unix格式），则ROWTERMINATOR = '0x0a'；
• 若行尾是CRLF（Windows格式），则ROWTERMINATOR = '0x0d0x0a'；
  确保参数与文件实际行终止符一致。

内容的提问来源于stack exchange，提问作者The Furious Bear