SQL*Loader delimiter/enclosing problem

[welchdor]

I am loading a CSV file using this delimiter spec in the control file:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

A few rows are being rejected with this error:

Record 5378: Rejected - Error on table CISIF.ADS_PREMISE, column ACCT_NAME.
No terminator found after TERMINATED and ENCLOSED field

Here's the BAD file contents for my 3 rejected rows:

3005517,AC,,"WILSON, JACOB "JAKE" D",,9495472111,,LH
3063601,AC,,"HEREDIA, MEMIN "PINGUIN"",,2818108,,NO
3097353,AC,,"DREW, ELSWORTH "JIM"",,9286807448,,LH

It's the quotation marks around the nicknames which appear at the end of the account_name field that seem to be causing the problem. This is what those field values actually look like out of the CSV file if you open it in Excel:

WILSON, JACOB JAKE" D"
HEREDIA, MEMIN PINGUIN""
DREW, ELSWORTH JIM""

What needs to happen to get these values to load?

Thanks in advance

[DaPi]

I sympathise. I have even worse trouble with addresses: practically any character can appear in an address.

The only good solution I've found is to tackle it at the point the CSV is generated. My favourite enclosing character is the circumflex ^ - in my applications it can not meaningfully stand by itself, it only appears in composite characters (like â). Other applications may not be so lucky.

Otherwise something at the OS level to replace ," and ", by ,^ and ^, respectively?