Bulk Insert导入TXT文件遇数据转换错误求助

我尝试用Bulk Insert将TXT文件导入自建数据库，创建表的脚本如下：

create table Producer(
    ProducerID              char(10)    not null primary key,
    Producer                varchar(25) not null,
    ListPrice               int         null,
    Quantity                int         not null
)

执行Bulk Insert时弹出以下错误：

Bulk load: DataFileType was incorrectly specified as char.
DataFileType will be assumed to be widechar because the data file has
a Unicode signature. Bulk load: DataFileType was incorrectly specified
as char. DataFileType will be assumed to be widechar because the data
file has a Unicode signature. Msg 4864, Level 16, State 1, Line 2 Bulk
load data conversion error (type mismatch or invalid character for the
specified codepage) for row 1, column 4 (Quantity). Msg 4864, Level
16, State 1, Line 2 Bulk load data conversion error (type mismatch or
invalid character for the specified codepage) for row 2, column 3
(ListPrice). 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 4 (Quantity). Msg 4864, Level 16, State 1,
Line 2 Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 4, column 4 (Quantity).
Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error
(type mismatch or invalid character for the specified codepage) for
row 5, column 4 (Quantity). Msg 4864, Level 16, State 1, Line 2 Bulk
load data conversion error (type mismatch or invalid character for the
specified codepage) for row 6, column 4 (Quantity). Msg 4864, Level
16, State 1, Line 2 Bulk load data conversion error (type mismatch or
invalid character for the specified codepage) for row 7, column 4
(Quantity). Msg 4864, Level 16, State 1, Line 2 Bulk load data
conversion error (type mismatch or invalid character for the specified
codepage) for row 8, column 4 (Quantity). Msg 4864, Level 16, State 1,
Line 2 Bulk load data conversion error (type mismatch or invalid
character for the specified codepage) for row 9, column 4 (Quantity).
Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error
(type mismatch or invalid character for the specified codepage) for
row 10, column 4 (Quantity). Msg 4864, Level 16, State 1, Line 2 Bulk
load data conversion error (type mismatch or invalid character for the
specified codepage) for row 11, column 4 (Quantity). Msg 4865, Level
16, State 1, Line 2 Cannot bulk load because the maximum number of
errors (10) was exceeded. Msg 7399, Level 16, State 1, Line 2 The OLE
DB provider "BULK" for linked server "(null)" reported an error. The
provider did not give any information about the error. Msg 7330, Level
16, State 2, Line 2 Cannot fetch a row from OLE DB provider "BULK" for
linked server "(null)".

最初用Excel文件导入失败，转为TXT文件后仍无法成功，寻求解决办法。（注：原问题附带文件内容截图，显示数据列存在非数字字符或格式不匹配情况）

1. 修正Bulk Insert的编码与参数配置 #

报错明确提示文件带Unicode签名，需将DataFileType改为widechar适配编码，同时根据文件实际格式调整分隔符等参数：

BULK INSERT Producer
FROM 'C:\你的文件路径\producer.txt'
WITH (
    DATAFILETYPE = 'widechar',  -- 匹配Unicode签名文件
    FIELDTERMINATOR = '\t',     -- 按文件实际分隔符调整，比如制表符、逗号等
    ROWTERMINATOR = '\n',       -- 行分隔符
    FIRSTROW = 2,               -- 若文件有表头则跳过第一行
    ERRORFILE = 'C:\错误日志路径\error.log'  -- 生成错误日志排查具体问题
);

2. 清理TXT文件的数据格式 #

• 检查ListPrice和Quantity列，确保内容是纯数字，无空格、逗号、货币符号等非数字字符。
• 确认所有行的列分隔符一致，无缺失或多余分隔符导致列错位。
• 若从Excel导出TXT，选择「Unicode文本(*.txt)」格式，避免编码混乱；导出前清除单元格的格式（如货币、百分比格式）。

3. 验证数据与表结构匹配 #

• ProducerID为char(10)，确保数据长度不超过10字符，过长会截断报错。
• Producer为varchar(25)，检查内容长度符合要求。
• ListPrice允许空值，若数据中有空值，用空字符串或NULL标识，避免无效字符。

4. 小批量测试排查 #

先提取前2-3行数据单独存为TXT文件，用Bulk Insert测试，确认参数和格式正确后再导入全量数据，降低排查难度。

5. 替代方案：用SQL Server导入导出向导 #

若Bulk Insert仍报错，可使用SSMS的导入导出向导：

1. 右键目标数据库 → 任务 → 导入数据
2. 数据源选择「平面文件源」，选中TXT文件后配置分隔符、编码、表头选项
3. 目标选择数据库和Producer表，匹配列映射
4. 运行导入，向导会自动处理编码和格式问题，并给出详细错误提示

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