bulk insert error

Question

Post reply

bulk insert error

balaji.ganga

SSCrazy

Points: 2280
More actions
May 20, 2010 at 6:42 am

#221789

Hi,
I have create a table like csvtest & insert 4 rows.
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
insert into csvtest values(1,'James','Smith','19750101')
go
insert into csvtest values(2,'Meggie','Smith','19790122')
go
insert into csvtest values(3,'Robert','Smith','20071101')
go
insert into csvtest values(4,'Alex','Smith','20040202')
select * from csvtest
BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
once i run the above script. it shows the following error message.
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
It would be grateful.. if anyone can helpme..
Thanks
Balaji.G

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

Could you please post the contents of csvtest.txt.

Lowell SSC Guru Points: 323518 More actions · Answer 2

i've seen this when the file you are processing comes from a unix type source...instead of slash-n for the row terminator, which looks for CHAR(13) + CHAR(10), try changing your rowterminator to slash-r, which is just CHAR(10) i think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 3

I agree with what Lowell says. If your source is from a non-Windows machine, other OSes tend to use different end of line characters other than CRLF, such as just CR or just LF.

Joie Andrew
"Since 1982"

balaji.ganga SSCrazy Points: 2280 More actions · Answer 4

CREATE TABLE CSVTest

(ID INT,

FirstName VARCHAR(40),

LastName VARCHAR(40),

BirthDate SMALLDATETIME)

insert into csvtest values(1,'James','Smith','19750101')

go

insert into csvtest values(2,'Meggie','Smith','19790122')

go

insert into csvtest values(3,'Robert','Smith','20071101')

go

insert into csvtest values(4,'Alex','Smith','20040202')

select * from csvtest

1JamesSmith1975-01-01 00:00:00

2MeggieSmith1979-01-22 00:00:00

3RobertSmith2007-11-01 00:00:00

4AlexSmith2004-02-02 00:00:00

BULK

INSERT CSVTest

FROM 'c:\csvtest.txt'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO

error shows

Msg 4832, Level 16, State 1, Line 1

Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 1

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 1

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".