批量插入失败“批量加载数据转换错误（截断）”； [英] Bulk Insert Failed "Bulk load data conversion error (truncation)"

问题描述

我已经使用SQL Server的批量插入任务完成了数百次数据导入，但是这次我收到了一个不熟悉的错误，并且我已经尝试了故障排除对Google无济于事。下面是我用逗号分隔文件使用的代码，其中新行用换行符表示：

I've done data imports with SQL Server's BULK INSERT task hundreds of times, but this time I'm receiving an error that's unfamiliar and that I've tried troubleshooting to no avail with Google. The below is the code I use with a comma deliminated file where the new rows are indicated by new line characters:

BULK INSERT MyTable
FROM 'C:\myflatfile.txt'
WITH (
    FIELDTERMINATOR = ','
    ,ROWTERMINATOR = '/n')
GO

它始终有效，但是现在在具有日期和费率的简单文件上，它以 第1列，第2列（ColumnTwo）的消息4863，级别16，状态1，第1行的大容量数据转换错误（截断）。查看文件时，我看不到为什么会失败（通常Google故障排除表明分隔符可能在一行中多次存在，这会引发此错误）。从文件开始，以下是前十行（请注意，它在第一行上失败）：

It consistently works, yet now on a simple file with a date and rate, it's failing with the error of "Msg 4863, Level 16, State 1, Line 1 Bulk load data conversion error (truncation) for row 1, column 2 (ColumnTwo)." When I look at the file, I don't see why this would fail (usually the Google troubleshooting indicates that the delimiters may exist multiple times in a line, which would raise this error). From the file, here are the first ten lines (note that it fails on the FIRST line):

1961-01-01,8.2
1961-02-01,8.2
1961-03-01,7.4
1961-04-01,7.6
1961-05-01,7.8
1961-06-01,8.5
1961-07-01,9.1
1961-08-01,8.8
1961-09-01,8.4
1961-10-01,8.8

我要插入这些数据的表有两个字段，分别是 VARCHAR（50），即使当我最初看到截断时，我也将数据字段扩展为 VARCHAR（2000），但这并没有影响。

The table I'm inserting these data into has two fields thare are VARCHAR(50), even though when I initially saw the truncation I expanded the data fields to VARCHAR(2000) and it didn't affect it.

CREATE TABLE MyTable (
    ColumnOne VARCHAR(50),
    ColumnTwo VARCHAR(50)
)

我还尝试删除所有破折号以查看是否弄乱了东西（尽管我已经使用相同的代码用破折号完成了很多数据导入，并且它可以正常工作而没有错误），它仍然收到相同的错误消息。

I also tried removing all the dashes to see if that was messing up things (even though I've done plenty of data imports with dashes using this same code and it works without error), and it still received the same error message.

直接导入有效（通过 Ta sks ），但是该代码失败了，因为它应该做的完全一样？

A direct import works (through Tasks) as does SSIS, but what about this code is failing, as it should be doing the exact same thing?

推荐答案

问题可能是由于文件格式而导致行终止符不起作用。

The problem is likely the row terminator is not working due to the file format.

尝试：

ROWTERMINATOR = '0x0a'

编辑

实际上我只是注意到您使用的是正斜杠，应该是反斜杠，所以这可能有用：

Actually I just notice you are using forward slash, it should be backslash, so this may work:

ROWTERMINATOR = '\n'

这篇关于批量插入失败“批量加载数据转换错误（截断）”；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！