SQL Server BULK INSERT格式文件数据导入异常求助

问题概述 #

使用SQL Server及Management Studio执行BULK INSERT导入CSV数据到SeriesData表时，初始直接导入出现数据转换错误；改用格式文件后虽能完成插入，但导入数据完全不符合预期（数值全部错误，浮点值变为NULL）。原数据近2000行，简化为单行测试仍可复现问题。

相关表结构 #

-- Create the Signals table
CREATE TABLE Signals 
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    SignalName NVARCHAR(255) NOT NULL,
    SignalUnit NVARCHAR(50)
);
GO

-- Create the Series table
CREATE TABLE Series 
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    SeriesType NVARCHAR(255) NOT NULL,
    ReferenceSignalID INT,
    FOREIGN KEY (ReferenceSignalID) REFERENCES Signals(ID)
);
GO

-- Create the SeriesData table
CREATE TABLE SeriesData 
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    SeriesID INT,
    SignalID INT,
    SignalValue FLOAT NULL, -- 精度为53
    ReferenceSignalValueID INT NULL,
    FOREIGN KEY (SeriesID) REFERENCES Series(ID),
    FOREIGN KEY (SignalID) REFERENCES Signals(ID),
    FOREIGN KEY (ReferenceSignalValueID) REFERENCES SeriesData(ID)
);
GO

CSV数据说明 #

示例CSV（简化自原数据）：

29,953,0.0,
29,953,0.01000213623046875,
29,953,0.0200042724609375,

• 无表头，列对应关系：SeriesID | SignalID | SignalValue | ReferenceSignalValueID
• 最后一列为空，需导入为NULL
• 行结尾为CRLF，编码为UTF-8，无异常字符

执行的BULK INSERT代码 #

BULK INSERT SeriesData
FROM 'myPath\myData.csv'
WITH (
    --FIELDTERMINATOR = ',',
    --ROWTERMINATOR = '\r\n', -- 使用\n时错误更多
    FORMATFILE = 'myPath\SeriesData.fmt',
    CODEPAGE = '65001' -- UTF-8编码
    --KEEPNULLS -- 启用后无变化
)

格式文件与导入异常 #

第一版格式文件及错误结果 #

格式文件内容：

10.0
4
1       SQLINT              0       4       ","     1     SeriesID               ""
2       SQLINT              0       4       ","     2     SignalID               ""
3       SQLFLT8             0       8       ","     3     SignalValue            ""
4       SQLINT              0       4       "\r\n"  4     ReferenceSignalValueID ""

导入后SeriesData表结果：

ID      SeriesID    SignalID    SignalValue    ReferenceSignalValueID
----------------------------------------------------------
6166    3355961     0           NULL           NULL
6167    3355961     0           NULL           NULL
6168    3355961     0           NULL           NULL

所有行的SeriesID应为29却显示3355961，SignalID应为953却显示0，SignalValue全部为NULL，完全不符合预期。

第二版格式文件问题 #

尝试使用文本类型的格式文件，仍出现类型不匹配错误：

10.0
4
1       SQLCHAR             0       12      ","     1     SeriesID               ""
2       SQLCHAR             0       12      ","     2     SignalID               ""
3       SQLCHAR             0       20      ","     3     SignalValue            ""
4       SQLCHAR             0       12      "\r\n"  4     ReferenceSignalValueID ""

无格式文件时的错误提示 #

未使用格式文件时，报错信息：

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

错误中指向的SignalID应为表的第2列，却被标记为第3列，疑似解析时列对应关系混乱。

问题根源与解决方法 #

• 格式文件类型错误（核心问题）：第一版格式文件使用SQLINT、SQLFLT8等二进制数据类型读取文本格式的CSV，导致SQL Server将文本字符的二进制值直接解析为数值，出现完全错误的结果（如"29"被当成4字节二进制数解析为3355961）。CSV是文本格式，必须使用SQLCHAR/SQLVARCHAR类型读取，再自动转换为表对应的数值类型。

• 修正格式文件：使用以下正确的格式文件（适配文本CSV与目标表类型）：

10.0
4
1       SQLCHAR             0       10      ","     1     SeriesID               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       10      ","     2     SignalID               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       30      ","     3     SignalValue            SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       10      "\r\n"  4     ReferenceSignalValueID SQL_Latin1_General_CP1_CI_AS

• 所有列使用SQLCHAR读取文本

• 调整列长度以适配最大可能的数值长度

• 指定字符集，确保转换正常

• 完善BULK INSERT参数：启用KEEPNULLS参数，确保CSV中的空列被导入为NULL，同时确认编码与换行符设置：

BULK INSERT SeriesData
FROM 'myPath\myData.csv'
WITH (
    FORMATFILE = 'myPath\SeriesData.fmt',
    CODEPAGE = '65001',
    KEEPNULLS,
    ROWTERMINATOR = '\r\n'
)

• 解决列序号错误问题：无格式文件时报错列序号混乱，大概率是换行符解析问题：
  • 确认CSV实际换行符为CRLF，而非LF
  • 若仍有问题，可尝试使用ROWTERMINATOR = '0x0D0A'（CRLF的十六进制表示）替代'\r\n'

验证测试 #

使用修正后的格式文件与BULK INSERT代码，导入单行测试数据，确认SeriesID、SignalID、SignalValue均正确导入，最后一列空值被转换为NULL。

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