Note: this is a revised version of an earlier question
I have a text file that is to be imported into an Oracle table with SQL Loader. However, some rows have a CR/LF in their first column, enclosed by double quotes. Note that the row terminators are also CR/LF.
This is the .ctl file I am using:
LOAD DATA
INFILE 'incoming_data.txt'
CONTINUEIF THIS PRESERVE (1:1) = '"'
INTO TABLE my_table
TRUNCATE
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
text_column char(150),
date_column date 'YYYY-MM-DD HH24:MI:SS',
number_column integer external,
another_text_column char(150) terminated by whitespace
)
This is concatenating the two "partial rows" correctly. However, it concatenates the two portions of the first field without any separator. I would like to insert, say, a slash between the two parts - i.e. replace the original newline in the field's data with a slash.
How do I do that? Something like replace(:text_column,'\n','/') is not working, presumably as the continueif is removing the embedded newline.