Posted by Mike Husler on 08/17/05 20:02

We have created CSV files on HPUX 11.0 and transferred them via ASCII ftp
to our SQL Server machine file store to load large amounts for data using
the BULK INSERT command. This is the command:

BULK INSERT db..table FROM 'S:\path\filename.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Now these files are written on Linux and there seems to be a linefeed
problem when loading
the data. This is the error:

/Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column
31. Make sure the field terminator and row terminator are specified
correctly.
/
Column 31 is our last column in the CSV file.
I've tried '\r', '\r\n' for ROWTERMINATOR and cannot get it execute past
the 1st line.
If one opens the CSV file in Wordpad and saves it, then executes the
above statement,
the ROWTERMINATOR = '\n' suffices but this is an unreasonable solution.

Any help on this is greatly appreciated.

Michael Husler

[Back to original message]