BULK INSERT - only importing half the expected rows

Last Post 13 Jul 2010 12:16 PM by russellb. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
Topic is locked
Author Messages
elephant
New Member
New Member

--
26 Jan 2003 04:25 AM
I am trying to import data from a tab delimited text file using bulk import as follows:

BULK INSERT ABC.dbo.[data]
FROM 'd:\client\abc\data.tab'
WITH
(
--FIELDTERMINATOR = '\t', -- default is \t (tab character)
ROWTERMINATOR = '\n',
KEEPNULLS,
MAXERRORS= 100,
TABLOCK
)


The main problem I am having is that only half of the expected rows are being imported.

When I import the same file using DTS, all of the records are imported ok.

The file is being provided by the client from their FileMaker pro database. We switched to TAB delimited files because FileMaker seems to put out a non standard CSV !

Thanks in advance.
elephant
New Member
New Member

--
28 Jan 2003 12:39 AM
I am not actually using DTS, I am executing the BULK INSERT from a stored proc !

I have tried using both \n and \r\n for the rowterminator, the latter doesn't seem to work at all.
elephant
New Member
New Member

--
30 Jan 2003 04:42 PM
I set MAXERRORS= 100, to try and ensure that the proceedure runs to completion allowing me to pickup as many errors as possible while developing.

Is there an issue with that ?
Jairo
New Member
New Member

--
13 Jul 2010 11:32 AM
The database table you are bulk inserting into has more fields than the text file. So bulk insert wraps the other half of the text file data into the extra database fields.
Fix: ensure the # of fields in the database table match the number of fields in the text file.
russellb
New Member
New Member

--
13 Jul 2010 12:16 PM
No need to resurrect 7 and a half year old threads. That must be a record! Locking thread
Topic is locked

Acceptable Use Policy