CODEWITHSUNDEEP
sql-server
Mike
Mike

Reputation: 107

SQL server bulk insert rowterminator failed

I have an csv like this :

"F","003","abc""X","1","50A1","name","Z5AA1A005C","70008","","A1ZZZZ17","","","","","","""X","2","50A1","name","Z5AA1A005C","70007","","A1ZZZZ17","","","","","","""X","3","50A1","name","Z5AA1A005C","70000","","A1ZZZZ17","","","","","",""

I need to bulk insert to tabel A from the 2nd row

BULK INSERT A FROM 'c:\csvtest.csv' 
  WITH
  ( 
   FIELDTERMINATOR='","',
   ROWTERMINATOR='0x0a',
   FIRSTROW = 2,
   DATAFILETYPE = 'widenative'
  )

the problem is when I insert, failed insert it show error :

Msg 4866, Level 16, State 8, Line 15 The bulk load failed. The column is too long in the data file for row 1, column 15. Verify that the field terminator and row terminator are specified correctly. Msg 7301, Level 16, State 2, Line 15 Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

I have tried rowterminator : '0x0a','\n','\r\n','char(10)' but nothing works

Upvotes: 1

Views: 904

Answers (2)

Jason Clark
Jason Clark

Reputation: 1423

Try to Open the file in Notepad then check it for line structure and save it again.

Upvotes: 0

bilpor
bilpor

Reputation: 3931

Although it will only be inserting data from row2 row 1 still needs to be in the correct format as I'm pretty sure SQLServer performs a 'pre-validation' cycle against the Schema to ensure you have half a chance of the data getting to the database. Row 1 fails this 'pre-validation' because you have not provided all the columns as per the table schema.

Upvotes: 0

Related Questions