BULK INSERT Line Feed

Question

BULK INSERT Line Feed

Mr or Mrs. 500

Points: 528

July 15, 2005 at 6:48 am

I am trying to use the BULK INSERT command to import the National Do Not Call file. The records are apparently separated by Line Feed characters (UNIX style). I have tried several differenct commands, but I always get an error message like this:

Server: Msg 4866, Level 17, State 66, Line 1

Bulk Insert fails. Column is too long in the data file for row 1, column 2. Make sure the field terminator and row terminator are specified correctly.

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.

OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].

The statement has been terminated.

Here are a couple of examples of the syntaxes I have tried:

BULK INSERT Data.dbo.TableName

FROM '\\server\filename'

WITH

(FIELDTERMINATOR = ',',

ROWTERMINATOR = '\r'

)

BULK INSERT Data.dbo.TableName

FROM '\\server\filename'

WITH

(FIELDTERMINATOR = ',',

ROWTERMINATOR = 'char(10)'

)

BULK INSERT Data.dbo.TableName

FROM '\\server\filename'

WITH

(FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 1

Hi Michael,

Have a quick look through BOL - Check under keywords "character data format" and subtopic "Unicode Character Format".

Have fun

Steve

We need men who can dream of things that never were.

Michael Wasson Mr or Mrs. 500 Points: 528 More actions · Answer 2

Thanks for the reply, but I don't see how that helps. I have already tried specifying \n as the row terminator.

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 3

Can you give us a sample couple of lines of the data you are trying to import and a table definition for the table it is going into.

Have fun

Steve

We need men who can dream of things that never were.

Michael Wasson Mr or Mrs. 500 Points: 528 More actions · Answer 4

I don't think this will be helpful because you won't get the linefeed characters in the post. If you email me at mikeotown@msn.com, I can send you a sample file...

Here is what the rows look like:

201,2000004

201,2000007

201,2000008

201,2000012

201,2000017

201,2000048

201,2000055

Here is the table definition:

CREATE TABLE [dbo].[Load_DNC_National] (

[Col001] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Col002] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

)

If you do an Import in Enterprise Manager and choose "{LF}" for the row terminator, it works fine.

noeld SSC Guru Points: 96590 More actions · Answer 5

Have you tried?:

BULK INSERT Data.dbo.TableName

FROM '\\server\filename'

WITH

(FIELDTERMINATOR = ',',

ROWTERMINATOR = '\r\n'

)

* Noel

Michael Wasson Mr or Mrs. 500 Points: 528 More actions · Answer 6

That doesn't work, but specifying this format file works....

8.0

2

1 SQLCHAR 0 3 "," 1 col001 ""

2 SQLCHAR 0 7 "\n" 2 col002 ""

Thanks anyway!