Link to home
Create AccountLog in
Avatar of Mark Bakelaar
Mark BakelaarFlag for Norway

asked on

Remove carriage return during csv import using sql

Hi Experts,

I use the below script to import data from a csv file. This worked fine until I received csv files with carriage returns in them. I can not effect the export files. Is there a way to remove the carriage returns and line feeds during bulk import?

Best regards,
MB
 
USE ShipNet
GO
TRUNCATE TABLE REP_MAIN
GO

BULK
INSERT REP_MAIN
FROM 'c:\temp\rep_main nox.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO
ASKER CERTIFIED SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Mark Bakelaar

ASKER

Even though the answer is not what I hoped for...thanks a lot for the explanation.
This may work better for you:

INSERT into REP_MAIN
select * from openrowset(BULK 'c:\temp\rep_main nox.csv',single_clob) as RepFile

It will insert the file as one field in the table.
Thanks pattypi, indeed a nice solution.