none
BULK INSERT / FORMAT FILE error: bulk load failed. The column is too long in the data file for row

    Question

  • I am trying to test bulk insert with a very simple file.  After trying many variations and trying different suggestions, I cannot seem to get past the error below.  I am sure I have just been looking at it too long.  Could someone please point out what I am doing wrong... or tell me what I have to sacrifice to get bulk insert to work ;-)

    XP - SP2
    SQL Server Express 2005 (9.00.3042.00)

    CODE
    Code Snippet

    CREATE TABLE #temp
    (
    col1 varchar(255),
    col2 varchar(255),
    col3 varchar(255),
    )

    BULK INSERT #temp

        FROM 'C:\test.txt'
        WITH
        (
              FIELDTERMINATOR = '*'
            , ROWTERMINATOR = '\r\n'
            , KEEPNULLS
            , FORMATFILE='c:\test.fmt'

       )



    INPUT FILE
    Code Snippet

    [1]ABC*00

    [2]EFG*00



    FORMAT FILE:
    Code Snippet

    9.0
    2
    1 SQLCHAR 2 255 "*" 1 col1 SQL_Latin1_General_CP1_CI_AS
    2 SQLCHAR 2 255 "\r\n" 2 col2 SQL_Latin1_General_CP1_CI_AS



    ERROR:
    Msg 4866, Level 16, State 7, Line 1
    The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
    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)".



    Friday, August 22, 2008 1:26 AM

Answers

  • First,  if you're using a format file, you don't need to specify the formatting in the statment, this would work:

     

    Code Snippet

    BULK INSERT #temp

     

    FROM 'e:\test.txt'

    WITH

    (FORMATFILE = 'e:\test.fmt');

    GO

     

     

    Also, the problem is with the format file, it should be like this:

     

    Code Snippet

    9.0
    2
    1 SQLCHAR 0 255 "*" 1 col1 SQL_Latin1_General_CP1_CI_AS
    2 SQLCHAR 0 255 "\n" 2 col2 SQL_Latin1_General_CP1_CI_AS

     

     

    Check this reference for a detailed explanation:

     

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

    Friday, August 22, 2008 2:35 PM

All replies

  • First,  if you're using a format file, you don't need to specify the formatting in the statment, this would work:

     

    Code Snippet

    BULK INSERT #temp

     

    FROM 'e:\test.txt'

    WITH

    (FORMATFILE = 'e:\test.fmt');

    GO

     

     

    Also, the problem is with the format file, it should be like this:

     

    Code Snippet

    9.0
    2
    1 SQLCHAR 0 255 "*" 1 col1 SQL_Latin1_General_CP1_CI_AS
    2 SQLCHAR 0 255 "\n" 2 col2 SQL_Latin1_General_CP1_CI_AS

     

     

    Check this reference for a detailed explanation:

     

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

    Friday, August 22, 2008 2:35 PM
  • That worked perfectly.  Thank you so much!  I knew I had made a mistake somewhere ... but was wracking my brain trying to figure out what it was.  I had tried it without the row/column terminators.  But as you pointed out, the format file was still wrong. So it did not work.

    You know what is funny is I did read that article on format files.  As I will have null values, I took this comment to mean  I had to use a non-0 prefix.  Of course that did not work. So I am going to have to read this one again ...
    To avoid specifying the length prefix, set this to 0. A length prefix must be specified if the field contains NULL data values.


    Friday, August 22, 2008 8:18 PM