|
|
Posted by Mike Husler on 08/18/05 01:30
Erland Sommarskog wrote:
>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
>
>
>
>
Thanks. We also discovered the unix2dos command on linux and running
all the CSV files
through that solved it. Thanks for the solution.
Navigation:
[Reply to this message]
|