Monday, March 19, 2012

bulk insert is drinving me mad!

I am probably a putz, but the following is got me stumped. I have a text file called file001.txt that looks like this:

1,2,3,4,5,6,7,8,9|1,2,3,4,5,6,7,8,9|

When I use the following bulk insert statement everything works and I have no problems at all:

BULK INSERT table001
FROM 'C:\file001.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '|'
)

But, if I try to do this it fails:

declare @.fdel varchar
declare @.rdel varchar
set @.fdel = ','
set @.rdel = '|'
BULK INSERT table001
FROM 'C:\file001.txt'
WITH
(
FIELDTERMINATOR = @.fdel,
ROWTERMINATOR = @.rdel
)

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. The statement has been terminated.

What the heck is happening? Please help!!!!Are you sure that BULK INSERT allows variables to be used for terminators ?

Try:
declare @.SqlStr varchar(8000)
declare @.fdel varchar
declare @.rdel varchar
set @.fdel = ','
set @.rdel = '|'
set @.SqlStr='
BULK INSERT table001
FROM 'C:\file001.txt'
WITH
(
FIELDTERMINATOR = '+@.fdel+',
ROWTERMINATOR = '+@.rdel+'
)
exec (SqlSr)

?|||Good morning kukuk,
That did not get it, but thanks. I farted around with it a little and got the following to work - and its closer to what I eventually want to accomplish anyway:

declare @.sql varchar(8000)
select @.sql = "BULK INSERT table001
FROM 'C:\file001.txt'
WITH
(
FIELDTERMINATOR = '" + (select fld_delimiter from datadef where description = 'BFH') + "',
ROWTERMINATOR = '" + (select row_delimiter from datadef where description = 'BFH') + "'
)"
exec(@.sql)

While your response wasnt an excat fix, it did allow me to break out of the endless cycle of MADNESS that totally blinded me to a simple solution last night.
-thanks again

No comments:

Post a Comment