Bulk Insert Error Msg 4863,Bulk load data conversion error (truncation)

Question

Post reply

Bulk Insert Error Msg 4863,Bulk load data conversion error (truncation)

SQL_DBA-450371

SSC-Addicted

Points: 473
More actions
July 18, 2008 at 9:36 am

#129932

Hi,
I am having trouble trying to do bulk insert into my table.
I know my data file has some records which is longer than the length of the columns in the table. I need the data to be truncated and inserted into the table without producing an error.
In SQL 2000 we accomplished this by using SET ANSI_WARNINGS OFF.
In SQL 2005, I have done this but it is still giving the errors. I have also turned off ARITHABORT
my table looks like this:
create table test(
firstname varchar(5),
lastname varchar(5))
My data(tab delimited) looks like this:
abcdefgh abcdefgh
ijklmnop ijklmnop
qrstuvwx qrstuvwx
I am using a format file with the bulk insert which is like this:
8.0
2
1 SQLCHAR 0 5 "\t" 1 fname ""
2 SQLCHAR 0 5 "\r" 2 lname ""
I have also tried to change the 8.0 to a 9.0
I have even tried SET ANSI_DEFAULTS OFF.
I have even tried BCP but the same thing happens.
I want the data to be truncated and loaded into the table without any errors
this is the kind of error I am getting:
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 944, column 96 (PR_DESC).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1074, column 96 (PR_DESC).
Msg 4865, Level 16, State 1, Line 1
Please help!!!

Viewing 3 posts - 1 through 3 (of 3 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Using OpenRowset as:

INSERT INTO myTestOrder

SELECT Col1, Col2, Col3, SUBSTRING(Col4,1,3)

FROM OPENROWSET(BULK 'C:\myTestOrder-c.txt',

FORMATFILE='C:\myTestOrder.fmt'

) AS t1;

The above was copied from BOL topics:

Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...)

Using a Format File to Map Table Columns to Data-File Fields

URL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/e7ee4f7e-24c4-4eb7-84d2-41e57ccc1ef1.htm

and I modified the original with the SUBSTRING function and it imported just 3 characters from Col4 which itself was 10 characters in the text file.

Hope this helps

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

SQL_DBA-450371 SSC-Addicted Points: 473 More actions · Answer 2

It still doesnt work

I wrote some code to dynamically determin the insert into query:

declare @colName varchar(255)

declare @colType int

declare @colMaxLength varchar(20)

declare @sql varchar(max)

declare @tbl varchar(128)

declare @loadfile varchar(500)

declare @formatFile varchar(500)

declare @vartype int

select @vartype=system_type_id from sys.types where name='varchar'

set @tbl = 'ls_float'

set @loadfile='I:\feeds\ildump_dpa\in\dfscan_ls_float'

set @formatfile='I:\feeds\ildump_dpa\in\ls_float.fmt'

set @sql = 'insert into '+@tbl+' select '

declare tmp cursor for

select name, system_type_id, max_length from sys.columns where object_id=object_id(@tbl) order by column_id

OPEN tmp

FETCH NEXT FROM tmp into @colName, @colType, @colMaxLength

while @@Fetch_status =0

begin

if(@colType = @vartype)

begin

set @sql = @sql + 'Substring('+@colName+',1,'+@colMaxLength+'), '

end

else

begin

set @sql = @sql + @colName+', '

end

FETCH NEXT FROM tmp into @colName,@colType, @colMaxLength

end

close tmp

deallocate tmp

set @sql = substring(@sql, 1, len(@sql)-1) +' from OPENROWSET(BULK '''+@loadfile+''',FORMATFILE='''+

@formatFile+''') AS t1;'

--select @sql, len(@sql)

sp_executesql @sql

This is still giving me the following errors: