BULK INSERT misbehaving when there are less fields in a file than in a table

  • I have a table with 6 fields and the file which sometimes contains 6 and sometimes 5 fields.

    When there are only 5 fields in the incoming file, BULK INSERT disregards the row terminator and tries to load the whole next line into 6th field.

    Is there a workaround for that?

    The command I use is

    BULK INSERT table

    FROM 'file'

    WITH

    (

    FIELDTERMINATOR ='\t',

    FIRSTROW = 2,

    ROWTERMINATOR = ''

    )

  • use a format file.

    there are examples in books online how to deal with this situation.


    N 56°04'39.16"
    E 12°55'05.25"

  • I coudn't find a relevant example. Could you at least point me in the right direction?

  • Here http://msdn.microsoft.com/en-us/library/ms191175(SQL.90).aspx

    What you also could do, is to make a two-step import routine.

    First try to import file using six columns. If that fails, execute the import again using five columns.

    DECLARE @rc INT

    EXEC @rc = usp_MyImport 'Data filename here', 'Format filename here with 6 columns'

    IF @@ERROR <> 0 OR @@rc <> 0

    BEGIN

    SET @rc = 0

    EXEC @rc = usp_MyImport 'Data filename here', 'Format filename here with 5 columns'

    END

    IF @@ERROR <> 0 OR @@rc <> 0

    RAISERROR('Import failed', 18, 1)


    N 56°04'39.16"
    E 12°55'05.25"

  • The problem is, the import doesn't fail - it simply sticks the next line into the 6th column (which is sufficiently large). So the only way to see that it didn't work correctly is to count the lines in a file and compare to the number of rows inserted.

    Some lines in the file have 6 fields, some 5. I can't see how format files with 6 and 5 columns in them will save me here.

  • Sergey Kazachenko (12/19/2008)


    The problem is, the import doesn't fail - it simply sticks the next line into the 6th column (which is sufficiently large).

    It is a sign that the row terminator is wrong.


    N 56°04'39.16"
    E 12°55'05.25"

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

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