SQL Loader how to ignore/ skip single quote

[ruhestorer]

I have huge csv file that i need to load into database using sql loader.
In the file, there are columns enclosed by ", but some of them is not and in these columns there are single quotes and I would like to know how to deal with them (I can't change csv file):

LP31388-9.LP31392-1,4,LP31392-1,LP15305-3,5'-Nucleotidase
LP31388-9.LP31392-1.LP15305-3,1,LP15305-3,LP45022-8,5'-nucleotidase | Amniotic Fluid
LP31388-9.LP31392-1.LP15305-3,2,LP15305-3,LP45028-5,5'-nucleotidase | Bld-Ser-Plas
LP31388-9.LP31392-1.LP15333-5.LP44699-4,2,LP44699-4,1743-4,ALT SerPl w P-5'-P-cCnc
LP31389-7.LP18116-1.LP16832-5.LP175708,7,LP175708,LP181867-5,"2,3',4,4',5-Pentachlorobiphenyl (PCB) | Bld-Ser-Plas"

my sql loader control file

OPTIONS (errors=0, SKIP=1, SILENT=(FEEDBACK))
LOAD DATA
CHARACTERSET UTF8
INFILE 'data/hierarchy.csv'
INTO TABLE hierarchy
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    path_to_root        CHAR
    ,seq                NUMBER
    ,immediate_parrent  CHAR
    ,code               CHAR
    ,code_text          CHAR
)

error I get looks like:

SQL*Loader-350: Syntax error at line 6.
Expecting double quote, found single quote.
FIELDS TERMINATED BY ',' OPTIONALY ENCLOSED BY '"'
                           ^

[Reverend Jim]

Can you give us an example of how

LP31389-7.LP18116-1.LP16832-5.LP175708,7,LP175708,LP181867-5,"2,3',4,4',5-Pentachlorobiphenyl (PCB) | Bld-Ser-Plas"

should look after it is split up into separate fields?

[rch1231]

Hello,

As I see it you have two choices:
1. You create a new csv file by substituting all of the single quotes with backslash single quote. If it is Linux you can do this with sed and if it is Windows you can use Wordpad and find-replace (you could use notepad but does not work well on large files) or you can script it with powershell.

2 Use Excel to read the file and then save it as a new csv. Excel will put double quotes around any fields cantaining single quotes.