ROWTERMINATOR in bulk insert query.

[Mike Husler]

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

[Erland Sommarskog]

Mike Husler (Mi**************@noaa.gov) writes:

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:

A classic problem, that I don't think I ever found a solution to.
Until now. This is ugly, but it works:

CREATE TABLE nisse (a varchar(22) NOT NULL,
b varchar(23) NOT NULL,
c varchar(23) NOT NULL)
go
DECLARE @sql nvarchar(4000)
SELECT @sql =
'BULK INSERT nisse FROM ''E:\temp\slask.csv''
WITH (
DATAFILETYPE = ''char'',
FIELDTERMINATOR = '';'',
ROWTERMINATOR = ''' + nchar(10) + ''')'
EXEC(@sql)
go
SELECT * FROM nisse
go
DROP TABLE nisse
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Mike Husler]

Erland Sommarskog wrote:

Mike Husler (Mi**************@noaa.gov) writes:

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:

A classic problem, that I don't think I ever found a solution to.
Until now. This is ugly, but it works:

CREATE TABLE nisse (a varchar(22) NOT NULL,
b varchar(23) NOT NULL,
c varchar(23) NOT NULL)
go
DECLARE @sql nvarchar(4000)
SELECT @sql =
'BULK INSERT nisse FROM ''E:\temp\slask.csv''
WITH (
DATAFILETYPE = ''char'',
FIELDTERMINATOR = '';'',
ROWTERMINATOR = ''' + nchar(10) + ''')'
EXEC(@sql)
go
SELECT * FROM nisse
go
DROP TABLE nisse

Thanks. We also discovered the unix2dos command on linux and running
all the CSV files
through that solved it. Thanks for the solution.