We have a very simple need and have received a lot of conflictig advise. The simple question is "How do I remove a CR or LF out of one column contained in a CSV text INFILE?" I want to do this inside the SQL*Loader 10g CTL file below. The rest of this proces has run for four months as is, but we now want to remove these characters from the COMMENTS column to include its data as well.
Note that MS Access is the source database, we run a TransferText to produce the CSV text file. CR/LF is legal in MS Access but not in Oracle, so we need to get SQL*Loader to strip these out. Must be done here, not upstream. Thanks!
LOAD DATA
INFILE 'Refer to app.config file'
BADFILE 'Refer to app.config file'
DISCARDFILE 'Refer to app.config file'
APPEND
INTO TABLE dairy.insp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
insp_ID INTEGER EXTERNAL,
Insp_Empl_ID CHAR,
.
. <some detail removed>
.
SA_Date_Hazard_Analysis_Is
sue "to_date(:SA_Date_Hazard_A
nalysis_Is
sue,'MM/DD
/YYYY HH24:MI:SS')",
SA_Date_HACCP_Plan_Issue "to_date(:SA_Date_HACCP_Pl
an_Issue,'
MM/DD/YYYY
HH24:MI:SS')",
SA_Date_Prereq_Programs_is
sue "to_date(:SA_Date_Prereq_P
rograms_is
sue,'MM/DD
/YYYY HH24:MI:SS')",
Insp_Order INTEGER EXTERNAL,
COMMENTS CHAR, <=========== Need to remove CR/LRs from this column
CREATE_DATE "to_date(:CREATE_DATE,'MM/
DD/YYYY HH24:MI:SS')",
CREATE_USER CHAR,
MODIFY_DATE "to_date(:MODIFY_DATE,'MM/
DD/YYYY HH24:MI:SS')",
MODIFY_USER CHAR,
PROGRAM_USE CHAR,
Upload_Date "to_date(:Upload_Date ,'MM/DD/YYYY HH24:MI:SS')",
Press_Insp CHAR
)
Start Free Trial
