Cannot fetch a row from OLE DB provider “BULK” for

问题:

I try to load my database with tons of data from a .csv file sized 1.4 GB. But when I try to run my code I get errors.

Here's my code:

USE [Intradata NYSE] 
GO
CREATE TABLE CSVTest1
(Ticker varchar(10) NULL,
dateval date NULL,
timevale time(0) NULL,
Openval varchar(10) NULL,
Highval varchar(10) NULL,
Lowval varchar(10) NULL,
Closeval varchar(10) NULL,
Volume varchar(10) NULL
)
GO

BULK
INSERT CSVTest1
FROM 'c:\intramerge.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest1
GO
--Drop the table to clean up database.
DROP TABLE CSVTest1
GO

I try to build a database with lots of stockquotes. But I get this error message:

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)"

I do not understand much of SQL, but I hope to catch a thing or two. Hope someone see what might be very obvious.

回答1:

Resurrecting an old question, but in case this helps someone else: after much trial-and-error I was finally (finally!) able to get rid of this error by changing this:

ROWTERMINATOR = '\n'

To this:

ROWTERMINATOR = '0x0A'

回答2:

I had same issue.

Solution:

Verify the CSV or textfile in text editors like notepad+. Last line might be incomplete. Remove it.

回答3:

I got the same error when I had a different number of delimited fields in my CSV than columns I had in my table. Check if you have the right number of fields in intramerge.csv.

回答4:

I got this error when my format file (i.e. specified using the FORMATFILE param) had a column width that was smaller than the actual column size (e.g. varchar(50) instead of varchar(100)).

回答5:

It was an old question but It seems that my finding would enlight some other people having a similar issue.

The default SSIS timeout value appears to be 30 seconds. This makes any service bound or IO bound operation in your package goes well beyond that timeout value and causes a timeout. Increasing that timeout value (change to "0" for no timeout) will resolve the issue.

回答6:

I got this exception when the char field in my SQL table was too small for the text coming in. Try making the column bigger.

回答7:

This is my solution: just give up.

I always end up using SSMS and [ Tasks > Import Data ].

I have never managed to get a real world .csv file to import using this method. This is utterly useless function that only works on pristine datasets that don't exist in the real world. Perhaps I've never had any luck because the datasets I deal with are quite messy and are generated by third parties.

And if it goes wrong, it doesn't give any clue as to why. Microsoft, you sadden me with your utter incompetence in this area.

Microsoft, perhaps add some error messages, so it says why it rejected it? Because it's almost impossible to fix the issue if you don't know why it failed!

回答8:

I encountered a similar issue, but in this case the file being loaded contained some blank lines. Removing the blank lines solved it.

Alternatively, as the file was delimited, I added the correct number of delimiters to the blank lines, which again allowed the file to import successfully - use this option if the blank lines need to be loaded.

回答9:

This might be a bad idea with a full 1.5GB, but you can try it on a subset (start with a few rows):

CREATE TABLE CSVTest1
(Ticker varchar(MAX) NULL,
    dateval varchar(MAX) NULL,
    timevale varchar(MAX) NULL,
    Openval varchar(MAX) NULL,
    Highval varchar(MAX) NULL,
    Lowval varchar(MAX) NULL,
    Closeval varchar(MAX) NULL,
    Volume varchar(MAX) NULL
)

... do your BULK INSERT, then

SELECT MAX(LEN(Ticker)),
    MAX(LEN(dateval)),
    MAX(LEN(timevale)),
    MAX(LEN(Openval)),
    MAX(LEN(Highval)),
    MAX(LEN(Lowval)),
    MAX(LEN(Closeval)),
    MAX(LEN(Volume))

This will help tell you if your estimates of column are way off. You might also find your columns are out of order, or the BULK INSERT might still fail for some other reason.

回答10:

i just want to share my solution to this. The problem was the size of table columns, use varchar(255) and all should work.

回答11:

The bulk insert will not tell you if the import values will "fit" into the field format of the target table.

For example: I tried to import decimal values into a float field. But as the values all had a comma as decimal point, it was unable to insert them into the table (it was expecting a point).

These unexpected results often happen when the provided CVS value is an export from an Excel file. Your computer's regional settings will decide which decimal point will be used when saving an Excel file into a CSV. CSV's provided by different people will cause different results.

Solution: import all fields as VARCHAR, and try to deal with the values afterwards.