import from csv text-field containing crlf

Gallus
SQL-Fighter

Good morning

We receive from an external source files wich are comma (",") separated and the text fields all start and end with double quotes. however in one of these text fiels there are CRLF. It looks something like the following

 

," bla bla 

bla blab",1234,..

I can see that the two lines are separated with CRLF

 

We import the file with the following configuration

SKIP=1
ROW SEPARATOR='CRLF'
NULL='NULL'

Is there a way to tell the import statement to ignore the crlfs within the text-fields?

Kind Regars

Gallus

 

1 REPLY 1

mwellbro
Xpert

Hi @Gallus ,

 

short answer:

 

create table test(
my_pk decimal(15,0),
txt varchar(200)
);

import into test
from local csv file 'c:\tmp\line_break.csv'
column delimiter='"'
ROW SEPARATOR='CRLF'
NULL='NULL'
;

select * from test;


-- input file

--1,"test"
--2,"bla
--bla
--bla"
--3,"another test"

should do it.

Cheers,
Malte