none
Bulk Insert Failing Message 4863, Bulk load data conversion error (truncation)

    Question

  •  

    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
          SQLCHAR       0       5       "\t"     1     fname          ""
          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!!!
    Friday, July 18, 2008 2:09 PM

Answers

  • This is by design. Sql2k5 enforces stricter data validation before it gets imported into the database. So, any data violation will terminate the import.

     

    http://msdn.microsoft.com/en-us/library/ms188365.aspx

     

    In your case, you explicitly define the max_length for your data in the format file (i.e. 5). So, sql2k5 has to comply and terminate your import.

     

    The "trick" is to dummy up the max_length in the format file and combine it with 'set ansi_warnings off'.

     

    Code Snippet

     

    set nocount on
    go
    create table test(
    firstname varchar(5),
    lastname varchar(5))
    go
    exec xp_cmdshell N'echo 8.0 >c:\fmt.fmt',no_output
    exec xp_cmdshell N'echo 2 >>c:\fmt.fmt'
    exec xp_cmdshell N'echo 1       SQLCHAR       0       25       "\t"     1     fname          "" >>c:\fmt.fmt',no_output
    exec xp_cmdshell N'echo 2       SQLCHAR       0       25       "\r\n"   2     lname          "" >>c:\fmt.fmt',no_output

    --exec xp_cmdshell N'type c:\fmt.fmt'

    exec xp_cmdshell N'echo abcdefgh abcdefgh >c:\bcp.txt',no_output
    exec xp_cmdshell N'echo abcdefgh abcdefgh >>c:\bcp.txt',no_output
    exec xp_cmdshell N'echo abcdefgh abcdefgh >>c:\bcp.txt',no_output

    --exec xp_cmdshell N'type c:\bcp.txt'
    go
    SET ANSI_WARNINGS OFF
    go
    bulk insert test
    from 'c:\bcp.txt'
    with(formatfile='c:\fmt.fmt');
    go
    select * from test
    go

    INSERT INTO test
      SELECT *
          FROM  OPENROWSET(BULK  'c:\bcp.txt',
          FORMATFILE='c:\fmt.fmt' 
           ) AS t1;
    go
    select * from test
    go

    drop table test
    go
    SET ANSI_WARNINGS ON

     

     

     

    Friday, July 18, 2008 9:50 PM
    Moderator

All replies

  • This is by design. Sql2k5 enforces stricter data validation before it gets imported into the database. So, any data violation will terminate the import.

     

    http://msdn.microsoft.com/en-us/library/ms188365.aspx

     

    In your case, you explicitly define the max_length for your data in the format file (i.e. 5). So, sql2k5 has to comply and terminate your import.

     

    The "trick" is to dummy up the max_length in the format file and combine it with 'set ansi_warnings off'.

     

    Code Snippet

     

    set nocount on
    go
    create table test(
    firstname varchar(5),
    lastname varchar(5))
    go
    exec xp_cmdshell N'echo 8.0 >c:\fmt.fmt',no_output
    exec xp_cmdshell N'echo 2 >>c:\fmt.fmt'
    exec xp_cmdshell N'echo 1       SQLCHAR       0       25       "\t"     1     fname          "" >>c:\fmt.fmt',no_output
    exec xp_cmdshell N'echo 2       SQLCHAR       0       25       "\r\n"   2     lname          "" >>c:\fmt.fmt',no_output

    --exec xp_cmdshell N'type c:\fmt.fmt'

    exec xp_cmdshell N'echo abcdefgh abcdefgh >c:\bcp.txt',no_output
    exec xp_cmdshell N'echo abcdefgh abcdefgh >>c:\bcp.txt',no_output
    exec xp_cmdshell N'echo abcdefgh abcdefgh >>c:\bcp.txt',no_output

    --exec xp_cmdshell N'type c:\bcp.txt'
    go
    SET ANSI_WARNINGS OFF
    go
    bulk insert test
    from 'c:\bcp.txt'
    with(formatfile='c:\fmt.fmt');
    go
    select * from test
    go

    INSERT INTO test
      SELECT *
          FROM  OPENROWSET(BULK  'c:\bcp.txt',
          FORMATFILE='c:\fmt.fmt' 
           ) AS t1;
    go
    select * from test
    go

    drop table test
    go
    SET ANSI_WARNINGS ON

     

     

     

    Friday, July 18, 2008 9:50 PM
    Moderator
  • Thank you,

     

    THAT WORKS!!!

     

    Tuesday, July 22, 2008 6:43 PM
  •  

    SELECT a.* FROM OPENROWSET( BULK '\\SERVERNAME\D$\MLSDownload\OfficeData\Data2.csv',

     

    FORMATFILE = '\\SERVERNAME\D$\MLSDownload\OfficeData\myTestFormatFiles.Fmt') AS a

     



    The format file specifies 50 char for the column which I set myself to try to avoid the truncation error. . I did a max length in excel to determine that the maximun length for this field in the csv is 20. I have tried everything.

    Bulk load data conversion error (truncation) for row 5, column 5 (amenities).

    Msg 4863, Level 16, State 1, Line 1

    Saturday, November 28, 2009 9:23 PM
  • My insert just have 20-30 errors and I want to ignore this rows for insert.

    This code resolved my problem:

    go
    SET ANSI_WARNINGS OFF
    go
    bulk insert SrXmlTbl
    from 'D:\Test\01.txt'
    with (fieldterminator=',',codepage=1256,MAXERRORS=100)

    MAXERRORS resolved my problem.

    :D


    Each problem has a solution.

    • Proposed as answer by mammadkoma Monday, June 25, 2012 3:21 PM
    Wednesday, June 06, 2012 5:52 AM