Bulk Insert help

[babai74]

Hi All,

Can someone clue me in on BULK INSERT? I've used it to create a table of one column, and everything worked fine, but now I have a TXT file of about 11 fields. I've tried importing the data by using this as a semicolon-delimited txt(around 400 lines) file

I keep getting several error messages:


Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 11. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


All of the fields in my table can accept nulls. The table created fine. Here's my code:



USE im
CREATE TABLE Customer_Contacts
(
ContactName nvarchar(50),
CompanyName nvarchar(100),
Address1 nvarchar(255),
Address2 nvarchar(255),
Address3 nvarchar(255),
City nvarchar(32),
State nvarchar(32),
Zip nvarchar(32),
Country nvarchar(50),
Phone nvarchar(32),
Salesrep nvarchar(50)
)

BULK INSERT Customer_Contacts
FROM 'e:\temp\iv\customer_contacts.txt'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n',

)




When i change 11th column

Salesrep nvarchar(50) --> Salesrep nvarchar(MAX)

When i perform bulk insert, one row updated sucessfully, when checked the table, last column is salesrep is filled with the rest of the text, here rowterminator \n doesnt work, am i doing anything wrong?? when i open text file, file opens perfectly.

here is some of the data in txt file

ALDEN BRASHEAR;ACME INTERNATIONAL;P.O.BOX 6578;;;LODI;CA;940746578;US;1 792-589-4581;
HENRY ROBINSON;ACME SOIL EQUIPMENT;P.O.BOX 50068;;;SANTA CLARA;CA;93568;US;864-980-4689;AB
JANE PROBST;THE ACME GROUP;1578 CHICAGO STREET;;;ERIE;PA;15024;US;1-654-333-2210;

babai

 

[fredericofonseca]

try using "\r" as the row terminator

Was that file created on a Windows system or on another system? e.g. unix/mainframe?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[babai74]

i checked in the notepad, and see carriage return characters (instead of new line, i cant see when i open log file in textpad), i dont know how to remove from the logfile (i mean replace carriage return characters with nextline)

Babai

 

[dhulbert]

You don;t need to change it in notepad, just change

ROWTERMINATOR = '\n',

to

ROWTERMINATOR = '\r',

in your Bulk Insert Script.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[babai74]

\r didnt worked getting the same error

The bulk load failed. The column is too long in the data file for row 1, column 11. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1