为何SSMS批量插入语句抛出数据转换错误？

问题场景 #

从Mac迁移的多个CSV文件批量插入SQL Server时，所有Dim表均出现相同的两条Msg 4864类型转换错误，报错行的数值在bigint范围内，且Excel、R检查未发现明显异常。

使用的BULK INSERT语句：

BULK INSERT [dbo].[DimBeat]
FROM '$(SqlSamplesSourceDataPath)DimBeat.csv'
WITH (
    CHECK_CONSTRAINTS,
    --CODEPAGE='ACP',
    DATAFILETYPE='char',
    FIELDTERMINATOR=',',
    ROWTERMINATOR='0x0A',
    --KEEPIDENTITY,
    TABLOCK
);

表结构：

CREATE TABLE DimBeat
(
    CrimeNumber bigint PRIMARY KEY,
    Beat varchar(20) UNIQUE NOT NULL
)

报错信息：

Msg 4864, Level 16, State 1, Line 26
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 95631, column 1 (CrimeNumber).
Msg 4864, Level 16, State 1, Line 26
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 191340, column 1 (CrimeNumber).

可能的原因及验证方式 #

• 换行符不匹配或行尾隐藏字符：
  Mac默认换行符是LF（0x0A），但部分行可能混入CR（0x0D）或者其他非打印字符（比如软回车、零宽空格）。SQL Server按指定的ROWTERMINATOR='0x0A'解析时，会把包含额外字符的行拆分成错误的结构，导致CrimeNumber列读取到非数值内容。
  验证方法：用文本编辑器（如Notepad++）打开CSV，开启显示所有字符功能，检查报错行及其前后行的换行符和末尾是否有不可见字符。

• 编码不兼容：
  Mac生成的CSV通常是UTF-8编码，而SQL Server的BULK INSERT默认使用系统ACP编码。如果CSV是UTF-8带BOM或纯UTF-8，未指定正确的CODEPAGE会导致字符解析乱码，进而影响数值转换。
  解决尝试：取消注释CODEPAGE='65001'（UTF-8的代码页）后重新执行BULK INSERT。

• 行计数偏差：
  由于换行符问题，SQL Server实际解析的行号和Excel/R显示的行号不一致，你检查的行可能并非实际出错的行。
  验证方法：用OPENROWSET结合BULK读取CSV并过滤可疑行，或者将CSV按行拆分后定位实际包含非法字符的行。

• 数值字段的隐性格式问题：
  某些行的CrimeNumber可能包含千位分隔符、全角数字或其他格式字符，Excel/R会自动转换为数值，但BULK INSERT会严格解析为纯数字字符串，导致转换失败。
  验证方法：用文本编辑器直接查看对应行的CrimeNumber字段内容，确认是否有非0-9的字符。

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