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

Question

Post reply

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

Sergey Kazachenko

Ten Centuries

Points: 1051
More actions
December 18, 2008 at 9:25 am

#103648

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 = ''
)

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

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

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 1

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"

Sergey Kazachenko Ten Centuries Points: 1051 More actions · Answer 2

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

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 3

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"

Sergey Kazachenko Ten Centuries Points: 1051 More actions · Answer 4

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.

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 5

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"

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

Cookies on SQLServerCentral