确定导致BULK INSERT出现“文件意外结束”的行？

Question

我正在执行批量插入操作：

DECLARE @row_terminator CHAR;
SET @row_terminator = CHAR(10); -- or char(10)

DECLARE @stmt NVARCHAR(2000);
SET @stmt = '
  BULK INSERT accn_errors
   FROM ''F:\FullUnzipped\accn_errors_201205080105.txt''
   WITH 
      (
        firstrow=2,
FIELDTERMINATOR = ''|''  ,
ROWS_PER_BATCH=10000
   ,ROWTERMINATOR='''+@row_terminator+'''
   )'
exec sp_executesql @stmt;

我得到了以下错误：

Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
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)".

有没有办法知道这个错误发生在哪一行？

我可以成功导入10,000,000行数据，但是在之后出现了错误。

- Alex Gordon

---

我建议首先尝试不同的行终止符。char(10)这样的终止符并不常见。可以尝试'\n'或'\r\n' - 同时，调查产生该文件的程序和操作系统可能也会有所帮助。 - Aaron Bertrand

@AaronBertrand 那么无法识别行吗？ - Alex Gordon

据我所知，没有。如果您已经排除了第2/3行（按照@Gordon的建议设置LastRow），您可以使用二进制分割快速缩小范围 - 取文件中的行数并设置LastRow = <该数字的一半> - 如果仍然出现错误，请将其减半并重试。很有可能是数据的第一行，并且也可能仅仅是因为您还没有将ROWTERMINATOR与文件中实际的行终止符匹配。您尝试过\n或\r\n吗？ - Aaron Bertrand

Answer 1

使用errorfile指定符号来定位有问题的行。

BULK INSERT myData
FROM 'C:\...\...\myData.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = 'C:\...\...\myRubbishData.log' 
);

myRubbishData.log 将包含有问题的行以及一个伴随文件。

myRubbishData.log.txt 将向您提供文件中的行号和偏移量。

伴随文件示例：

Row 3 File Offset 152 ErrorFile Offset 0 - HRESULT 0x80004005
Row 5 File Offset 268 ErrorFile Offset 60 - HRESULT 0x80004005
Row 7 File Offset 384 ErrorFile Offset 120 - HRESULT 0x80004005
Row 10 File Offset 600 ErrorFile Offset 180 - HRESULT 0x80004005
Row 12 File Offset 827 ErrorFile Offset 301 - HRESULT 0x80004005
Row 13 File Offset 942 ErrorFile Offset 416 - HRESULT 0x80004005

- user1588622

---

很遗憾，在Azure中“批量插入”无法正常工作。我不得不退而求其次，使用bcp命令。https://learn.microsoft.com/nl-nl/azure/sql-database/sql-database-load-from-csv-with-bcp - JP Hellemons

@JPHellemons：它有效，仅供参考，请查看我的答案：https://stackoverflow.com/questions/44065643/importing-a-bcp-file-in-azure-database/44067283#44067283 - TheGameiswar

偏移量是什么意思？我该如何使用从 HRESULT 指针来解决我的问题？ - lwileczek

Answer 2

我有一个csv文件，使用Bulk导入

BULK INSERT [Dashboard].[dbo].[3G_Volume]
FROM 'C:\3G_Volume.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '","',
ROWTERMINATOR = '\n'
)
GO

通常我使用这个脚本，它没有问题，但偶尔会出现以下错误：

"The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error."

通常情况下，这种情况发生在最后一行有空值（null）的情况下。

您需要将CSV文件链接到MS Access数据库中以检查数据。 （如果您的CSV文件不超过1.4百万行，则可以在Excel中打开它）

由于我的数据约为3百万行，因此我需要使用Access数据库。

然后检查最后一行的编号是否为空，并将空行的数量从CSV的总行数中减去。

例如，如果您在末尾有2个空行，并且总行数为30000005，则脚本将变成如下形式：

BULK
INSERT [Dashboard].[dbo].[3G_Volume]
 FROM 'C:\3G_Volume.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '","',
ROWTERMINATOR = '\n',
Lastrow = 30000003
)
GO

Cheers... Mhelboy

- Mhelboy

Answer 3

好玩，好玩，好玩。我还没有找到一个好的方法来调试这些问题，所以我使用暴力方法。也就是说，FirstRow和LastRow选项非常有用。

从LastRow = 2开始尝试。将结果加载到一个可以轻松清空的临时表中。

同时，您还应该记住第一行可能也会导致问题。

- Gordon Linoff

Answer 4

如果CHAR（10）是行终止符，那么我认为您不能像在BULK INSERT中尝试的那样将其放在引号中。不过有一种未记录的方法可以指示它：

ROWTERMINATOR = '0x0A'

- Steve Kass

---

谢谢，Артём。我没想到你也可以这样处理char(10)！它确实有效，我在评论之前应该测试一下。 - Steve Kass

即使你不了解 char 10，你仍然可以成为一个好人。 - Alex Gordon

这个小技巧拯救了我的项目。它适用于 Linux 命令行上的 tsql（freetds 的一部分），而其他解决方案都不起作用。太棒了！ - Kirk Roybal

Answer 5

是的 - BULK INSERT 的错误信息需要更多的细节，唯一的解决方法是采用Gordon所说的粗暴的方法。但首先，根据您收到的错误提示，要么是不理解您的行终止符，要么是文件末尾缺少行终止符。使用FIRSTROW和LASTROW将有助于确定问题所在。

因此，您需要执行以下操作：

1. 检查文件末尾是否有行终止符。如果没有，请添加一个并重试。还要确保最后一行包含所有必需的字段。如果显示“EOF”，那就是问题所在。
2. 您确定每行末尾都有LF吗？尝试CR（\n, 0x0D）并查看是否有效。
3. 仍然无法工作？尝试设置LASTROW=2并重试。然后尝试LASTROW=3。如果您的文件中有超过三行，并且此步骤失败，则行终止符无效。

- Peter

Answer 6

我遇到了同样的问题。我在Linux上编写了一个shell脚本来创建一个.csv文件。我将这个.csv文件带到Windows上并尝试批量加载数据。它不喜欢逗号...不要问我为什么，但我改用*作为批量导入的分隔符，并在我的.csv中执行了查找和替换逗号为*的操作...那起作用了...我改用~作为分隔符，那也起作用了...制表符也可以——它不喜欢逗号...希望这能帮助某些人。

- user2125311

Answer 7

我通过将所有字段转换为字符串，然后使用通用的FIELDTERMINATOR解决了这个问题。这很有效：

BULK INSERT [dbo].[workingBulkInsert]  
FROM 'C:\Data\myfile.txt' WITH (
   ROWTERMINATOR = '\n', 
   FIELDTERMINATOR = ',' 
)

我的数据文件现在看起来是这样的：

"01502","1470"
"01504","686"
"02167","882"
"106354","882"
"106355","784"
"106872","784"

第二个字段是一个没有双引号分隔符的十进制类型（例如，1470.00）。将两个字段都格式化为字符串可以消除错误。

- Karl Hoaglund

Answer 8

在我的经验中，这几乎总是由最后两行中的某些内容引起的。使用tail命令查看导入文件，它应该仍然会给出失败信息。然后使用全文编辑器打开它，使您可以看到非打印字符，例如CR、LF和EOF。即使您不知道原因，也应该能够通过这种方式把它混过去。例如： BULK INSERT fails with row terminator on last row。

- feetwet

Answer 9

我有一个CSV文件，使用Bulk导入。

您需要创建一个表，所有列都应该是可空的，并删除最后一行中的空格，在表格中添加仅在Excel中可用的列。请不要创建一个主键列，这个过程不是自动增量标识，因此会导致错误。

我已经像这样进行了批量插入：

CREATE TABLE [dbo].[Department](
    [Deptid] [bigint] IDENTITY(1,1) NOT NULL,
    [deptname] [nvarchar](max) NULL,
    [test] [nvarchar](max) NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
    [Deptid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table_Column](
    [column1] [nvarchar](max) NULL,
    [column2] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

BULK INSERT Table_Column
FROM 'C:\Temp Data\bulkinsert1.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR='\n' ,
    batchsize=300000 
);

insert into [dbo].[Department] 
select column1,column2 from Table_Column

- Dhaval Soni

---

感谢您对SO的贡献。问题是关于查找失败行，您的答案没有回答这个问题。另外已经有一个被接受的答案了。也许删除您的答案是有意义的。 - Maxim Sagaydachny

Answer 10

如果我将所有字段转换为字符串，然后使用通用的字段分隔符，就可以解决这个问题。

- Karl Hoaglund