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.

score 3 · Answer 1 · answered Jul 29 '16 at 19:38

3

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

answered Jul 29 '16 at 19:38

Nate B

33
5

score 0 · Answer 2 · edited Sep 17 '14 at 02:29

0

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

edited Sep 17 '14 at 02:29

AliciaBytes

7,300
6
36
47

answered Sep 17 '14 at 02:21

Ayan Sircar

1

Thanks. The above syntax gives an error. Is it because of the older version of MySql? Is there any workaround? – MySQLLearner Sep 17 '14 at 02:35

score 0 · Answer 3 · answered Sep 22 '14 at 03:46

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.

Loading a file with newline characters in text field

3 Answers3