sql loader with delimited file

[acorbally]

I am trying to load text that is delimted with ***. I don't fully understand the following items for loader, perhaps if I understood them better I could answer my own question:

fields terminated by '***', optionally enclosed by ';' and trailing nullcols. Any suggestions on how I can understand them better?

My real question would be when I try to load the data there may be a " or : or ; in the data but what is consistent is the *** separating the files. How can I load everything between the *** into records?

Data example: ***this is a test "really" I am testing******here is a new record, see it started with the three asterisks and finishes with three asterisks******sometimes the data has weird characters in it like a ; or a ' or even a " but they should not matter, just the asterisks at the end***

I would expect my data to look like this:

1. this is a test "really" I am testing

2. here is a new record, see it started with the three asterisks and finishes with three asterisks

3. sometimes the data has weird characters in it like a ; or a ' or even a " but they should not matter, just the asterisks at the end

 

[alexhu]

I tried this with a lot smaller example and found that


LOAD DATA
INFILE *
APPEND
INTO TABLE table1
FIELDS TERMINATED BY "***" OPTIONALLY ENCLOSED BY '***'
(text)
BEGINDATA
***this is a test******more stuff***

only loads "this is a test"

To get more fields, they have to be on seperate lines. This works

LOAD DATA
INFILE *
APPEND
INTO TABLE table1
FIELDS TERMINATED BY "***" OPTIONALLY ENCLOSED BY '***'
(text)
BEGINDATA
***this is a test***
***more stuff***

Alex