Member Avatar for 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 '"'
                           ^
Edited by ruhestorer
  • 3 Contributors
  • 2 Replies
  • 2K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by rch1231
Member Avatar for 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?

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.