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

Aug 17 '05 #1

Erland Sommarskog

Mike Husler (Michael.P.Husler@noaa.gov) writes:[color=blue]
> 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:[/color]

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, esquel@sommarskog.se

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

Aug 17 '05 #2

Mike Husler

Erland Sommarskog wrote:
[color=blue]
>Mike Husler (Michael.P.Husler@noaa.gov) writes:
>
>[color=green]
>>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:
>>
>>[/color]
>
>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
>
>
>
>[/color]
Thanks. We also discovered the unix2dos command on linux and running
all the CSV files
through that solved it. Thanks for the solution.

Aug 17 '05 #3