Posted by Erland Sommarskog on 08/18/05 00:45

Mike Husler (Michael.P.Husler@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, esquel@sommarskog.se

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


begin 644 slask.zip
M4$L#!`H``````.F\$3,IM,A$$@```!(````)````<VQA<VLN8W-V03M".T,*
M3SM$.T8*,3LR.S,*4$L!`A0`"@``````Z;P1,RFTR$02````$@````D`````
L`````0`@`+:!`````'-L87-K+F-S=E!+!08``````0`!`#<````Y``````#/
`
end

[Back to original message]