How do I fix this bulk insert

Your problem is that your file has several rows of data per row, but you've told the Bulk Insert to expect a \n after each row of data. Since it sees more data instead of a \n after the 1st three columns, it throws an error.

You have several options: clean up the file (blech), create an SSIS package and insert that way (by splitting by the columns), or use the attached code (source for the base of the code: http://forums.databasejournal.com/showthread.php?t=47966) to read the file, parse out the 3 pieces of each row's data in the rows, and insert that way.

hth

valkyrie_nc

CREATE PROCEDURE ProcessTextFile (@FileName VARCHAR (1024), @delimiterChar char(1))

AS

DECLARE @OLEResult INT
DECLARE @FS INT
DECLARE @FileID INT
DECLARE @Message VARCHAR (8000)

-- Create an instance of the file system object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
PRINT 'Scripting.FileSystemObject'
PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
END

-- Open the text file for reading
EXEC @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1, 1
IF @OLEResult <> 0
BEGIN
PRINT 'OpenTextFile'
PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
END

-- Read the first line into the @Message variable
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT

-- Keep looping through until the @OLEResult variable is < 0; this indicates that the end of the file has been reached.
WHILE @OLEResult >= 0
BEGIN

-- Loop for every 3 sets of data
	DECLARE @pos int
	DECLARE @piece varchar(50)
	DECLARE @ID varchar(9)
	DECLARE @BEG_DATE varchar(10)
	DECLARE @END_DATE varchar(10)
	DECLARE @counter int
	
	SET @counter = 0
	
	IF RIGHT(RTRIM(@Message), 1) <> @delimiterChar
		SET @Message = @Message + @delimiterChar
	
	SET @pos = PATINDEX('%' + @delimiterChar + '%',@Message)
	WHILE @pos <> 0
	BEGIN
		SET @piece = LEFT(@Message, @pos - 1)
		IF @counter = 0
		BEGIN
			SET @ID = @piece
			SET @counter = @counter + 1
		END
		ELSE IF @counter = 1
		BEGIN
			SET @BEG_DATE = @piece
			SET @counter = @counter + 1
		END
		ELSE IF @counter = 2
		BEGIN
			SET @END_DATE = @piece
			SET @counter = 0
			INSERT INTO tmptable (ID, BEG_DATE, END_DATE) values (@ID, @BEG_DATE, @END_DATE)
		END
		
		SET @Message = STUFF(@Message, 1, @pos, '')
		SET @pos = PATINDEX('%' + @delimiterChar + '%', @Message)
	END

	EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT
END

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

GO

Select allOpen in new window

I thought the same thing..."Your problem is that your file has several rows of data per row", but when I cut and past this into MSword it puts the line return in and divides the data into correct rows; as if I just couldn't see it in Notepad. Any idea why?

Hmmm.  Maybe it wants the old-school terminator "\r\n"?