Loading a file with newline characters in text field

Question

So I have data with in which all the fields are enclosed by quotes and its delimited by pipe. Some fields have html text in them so there are new line characters as part of the field. I want these new line characters to be part of the text field. The data looks something like this:

"abcd"|"1"|""|" abcdegf

"|"abcd"

Also, html data is huge amount of text (sample shows ery less data) and I get the error 'multibyte enlose string not supported". I am on infobright. I am okay even if I can remove those fields from CSV file. They are not needed for analysis. What should be the correct LOAD DATA LOCAL INFILE syntax for this?

I am new to this field, help is greatly appreciated.

Answer 1

LOAD DATA INFILE 'filename' "STR '\r\n'"
APPEND INTO TABLE tablename FIELDS TERMINATED BY "|"
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS

Answer 2

Please add the below code to the CTL file -

LOAD DATA INFILE 'filename' APPEND CONTINUEIF LAST != "|" INTO TABLE IDP.M_ACTION FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (..

The previous answer is not supported on all SQL Loader versions. You can try this solution instead.

Answer 3

load data infile '<file>' into table <table> fields enclosed
by '"' terminated by '|';