December 18, 2008 at 9:25 am
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 = ''
)
December 19, 2008 at 1:56 am
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"
December 19, 2008 at 4:18 am
I coudn't find a relevant example. Could you at least point me in the right direction?
December 19, 2008 at 4:48 am
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"
December 19, 2008 at 6:57 am
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.
December 19, 2008 at 7:07 am
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply