blocks|key|577119|text|有趣，有趣，有趣。我还没有找到调试这些问题的好方法，所以我使用暴力破解。也就是说，FirstRow和LastRow选项非常有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|577120|从LastRow+=2开始并继续尝试。将结果加载到一个可以随时截断的一次性表中。|577121|而且，您还应该记住，第一行也可能会给您带来问题。|577122|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Fun, fun, fun. I haven't found a good way to debug these problems, so I use brute force. That is, the FirstRow and LastRow options are very useful.

Start with LastRow = 2 and keep trying. Load the results into a throw-away table, that you can readily truncate.

And, you should also keep in mind that the first row could be causing you problems as well.

blocks|key|577177|text|如果CHAR(10)是行终止符，我认为您不能像在BULK+INSERT中那样将它放在引号中。不过，有一种未经记录的方式来表示它：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|577178|ROWTERMINATOR+=+'0x0A'|code-block|syntax|javascript|577179|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If CHAR(10) is the row terminator, I don't think you can put it in quotes like you are trying to in BULK INSERT. There is an undocumented way to indicate it, though:

<pre><code>ROWTERMINATOR = '0x0A'
</code></pre>

blocks|key|577219|text|要定位麻烦的行，请使用errorfile说明符。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|577220|BULK+INSERT+myData
FROM+'C:\...\...\myData.csv'
WITH+(
FIELDTERMINATOR+=+',',
ROWTERMINATOR+=+'\n',
ERRORFILE+=+'C:\...\...\myRubbishData.log'+
);|code-block|syntax|javascript|577221|myRubbishData.log将包含有问题的行，一个配套文件myRubbishData.log.txt将为您提供行号和文件中的偏移量。|577222|配套文件示例：|577223|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|577224|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

To locate the troublesome row use the errorfile specifier.

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

myRubbishData.log will have the offending rows and a companion file
myRubbishData.log.txt will give you row numbers and offsets into the file. 

Companion file example:

<pre><code>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
</code></pre>

blocks|key|577298|text|我遇到了同样的问题。我写了一个shell脚本来在Linux中创建一个.csv。我把这个.csv带到了Windows上，并尝试批量加载数据。它并不“喜欢”逗号...不要问我为什么，我在批量导入中更改为*作为分隔符，并在.csv中使用*查找和替换逗号。这很管用..我改成了~作为分隔符，这很管用...制表符也有效-它不喜欢逗号....希望这对某些人有帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|577299|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I ran into the same issue. I had written a shell script to create a .csv in Linux. I took this .csv to Windows and tried to bulk load the data. It did not "like" the commas.... Don't ask me why, but I changed to a * as a delimiter in the bulk import and performed a find and replace for comma with * in my .csv .. that worked.. I changed to a ~ as a delimiter, that worked... tab also worked- it didn't like the comma.... Hope this helps someone.

blocks|key|577342|text|根据我的经验，这几乎总是由最后两行中的某些东西引起的。对导入文件执行tail操作，它应该仍然会给出失败。然后在全文编辑器中打开它，该编辑器允许您看到非打印字符，如CR、LF和EOF。即使你不知道为什么，这也会让你把它弄得乱七八糟。例如，BULK+INSERT+fails+with+row+terminator+on+last+row|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|577343|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/q/25291988/2662901^0|Y|4|3A|1D|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

In my experience this is almost always caused by something in the last two lines. <code>tail</code> the import file and it should still give you the failure. Then open it in a full text editor that lets you see non-printing characters like CR, LF, and EOF. That should enable you to kludge it into working, even if you don't know why. E.g., <a href="https://stackoverflow.com/q/25291988/2662901">BULK INSERT fails with row terminator on last row</a>

blocks|key|577397|text|生成此错误的行没有CHAR(10)终止符或包含不必要的空格|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|577398|entityMap^0|9|8|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

the rows generating this error don't have <code>CHAR(10)</code> terminator or have unnecessary spaces

blocks|key|361732|text|我有一个使用批量导入的csv文件|type|unstyled|depth|inlineStyleRanges|entityRanges|data|361733|BULK+INSERT+[Dashboard].[dbo].[3G_Volume]
FROM+'C:\3G_Volume.csv'
WITH
(
FIRSTROW+=+2,
FIELDTERMINATOR+=+'","',
ROWTERMINATOR+=+'\n'
)
GO|code-block|syntax|javascript|361734|我通常使用这个脚本，它没有问题，但在极少数情况下。|361735|我遇到这个错误..|361736|链接服务器"(null)“的OLE+DB访问接口"BULK”报告了一个错误。该访问接口未提供有关该错误的任何信息。|361737|通常，当最后一行有空值(Null)时就会发生这种情况。|361738|您需要在MS+access数据库中链接csv文件以检查数据。(如果您的csv不超过140万行，您可以在excel中打开它)|361739|因为我的数据大约有300万行，所以我需要使用access+db。|361740|然后用空格检查最后一行的数量，并将空行的数量减去csv的总行数。|361741|如果末尾有两个空白行，总行数为30000005，脚本将如下所示。|361742|BULK
INSERT+[Dashboard].[dbo].[3G_Volume]
+FROM+'C:\3G_Volume.csv'
WITH
(
FIRSTROW+=+2,
FIELDTERMINATOR+=+'","',
ROWTERMINATOR+=+'\n',
Lastrow+=+30000003
)
GO|361743|干杯..。马赫博伊|361744|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|14|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|15|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|16|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|17|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|18|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|19|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1A|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1B|8|@]|9|@]|A|$]]|$1|W|3|X|5|D|7|1C|8|@]|9|@]|A|$E|F]]|$1|Y|3|Z|5|6|7|1D|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|11|$]]

I have a csv file that i import using Bulk

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

Usually I used this script and it has no problems but in rare occassions.

I encounter this error..

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

Usually, this happens when the last row have blank values(null).

You need to link your csv file in MS access db to check the data..
(If your csv is not more than 1.4million rows you can open it in excel)

Since my data is around 3million rows I need to use access db.

Then check the number of the last row with blanks and subtract the number of null rows to your total rows for csv.

if you have 2 blank rows at the end and the total number of rows is 30000005
The script will become like this..

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

Cheers...
Mhelboy

blocks|key|577519|text|如果我将所有字段转换为字符串，然后使用通用的字段分隔符，我就解决了这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|577520|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I got around the problem if I converted all fields to string and then used a common fielddelimiter.

blocks|key|361899|text|我通过将所有字段转换为字符串，然后使用通用的FIELDTERMINATOR解决了这个问题。这是可行的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|361900|BULK+INSERT+[dbo].[workingBulkInsert]++
FROM+'C:\Data\myfile.txt'+WITH+(
+++ROWTERMINATOR+=+'\n',+
+++FIELDTERMINATOR+=+','+
)|code-block|syntax|javascript|361901|我的数据文件现在看起来像这样：|361902|"01502","1470"
"01504","686"
"02167","882"
"106354","882"
"106355","784"
"106872","784"|361903|第二个字段是不带双引号分隔符的decimal类型(如1470.00)。将两者格式化为字符串消除了错误。|361904|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I got around the problem by converting all fields to strings and then using a common FIELDTERMINATOR. This worked:

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

My data file looks like this now:

<pre><code>"01502","1470"
"01504","686"
"02167","882"
"106354","882"
"106355","784"
"106872","784"
</code></pre>

The second field had been a decimal type with no double-quote delimiter (like , 1470.00) . Formatting both as strings eliminated the error.

blocks|key|577707|text|我有一个使用批量导入的CSV文件|type|unstyled|depth|inlineStyleRanges|entityRanges|data|577708|您需要创建一个表，并且所有列都应为空，并删除最后一行中的空格，只添加那些在excel中可用的列。请不要创建主列，此过程不是自动标识递增，这就是创建错误的原因。|577709|我做过这样的大容量插入：|577710|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|code-block|syntax|javascript|577711|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I have a CSV file that I import using Bulk

You need to create one table and all columns should be nullable and remove space in the last row, add only those columns that available in excel. And please do not create a primary column, this process is not Identity increment automatically that's why creating the error.

I have done a bulk insert like this:

<pre><code>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
</code></pre>

i am doing a bulk insert:

<pre><code>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;
</code></pre>

and am getting the following error:

<pre><code>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)".
</code></pre>

is there a way to know on which ROW this error occurred?

i am able to import 10,000,000 rows without a problem and error occurs after that

Determine ROW that caused "unexpected end of file" error in BULK INSERT?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我正在进行批量插入：DECLARE @row_terminator CHAR;SET @row_terminator = CHAR(10); -- or char(10)DECLARE @stmt NVARCHAR(2000);SET @stmt = '  BULK INSERT accn_errors   FROM ...

问确定在大容量插入中导致“文件意外结束”错误的行？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问确定在大容量插入中导致“文件意外结束”错误的行？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问确定在大容量插入中导致“文件意外结束”错误的行？
EN