Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • Last Modified:

How do I fix this bulk insert

I am getting this error:
The bulk load failed. The column is too long in the data file for row 1, column 3. Verify that the field terminator and row terminator are specified correctly.

I have this proc(Llisted below) to insert into a 3 column table.The text being inserted looks like this:

123456798|12/03/2003|03/19/2004|741258963|12/15/2003|01/14/2004|852147963|12/15/2003|01/14/2004|

Table definition looks like this
ID VARCHAR(9), BEG_BEG, VARCHAR(10), END_DATE VARCHAR(10)

Where am I going wrong?
Thanks
ALTER PROCEDURE dbo.IMPORT_file
	(@DataFileName sysname ='C:\data.TXT'	)
AS
	/* SET NOCOUNT ON */ 

	DECLARE @SQLCommand VARCHAR ( 1012 )
	
	SELECT @SQLCommand = 'Bulk INSERT tmptable FROM ''' + @DataFileName + ''' with ( FIELDTERMINATOR=''|'', ROWTERMINATOR = ''|\n'' ) '			
	PRINT @SQLCommand
	EXEC ( @SQLCommand )	
	
	RETURN

Open in new window

0
LIFEEXPERT
Asked:
LIFEEXPERT
  • 2
  • 2
1 Solution
 
valkyrie_ncCommented:
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

Open in new window

0
 
LIFEEXPERTAuthor Commented:
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?
0
 
valkyrie_ncCommented:
Hmmm.  Maybe it wants the old-school terminator "\r\n"?
0
 
LIFEEXPERTAuthor Commented:
I tried that. Thanks for your solution. I figured out an alternative, that is much simpler. For future reference to all...

SELECT @SQLCommand = 'Bulk INSERT tmptable  FROM ''' + @DataFileName + ''' with ( FIELDTERMINATOR=''|'', ROWTERMINATOR = ''|' + nchar(10) + ''')'      
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now