RE: sqlldr Question

Much older than 25. My kids are older than 25!

I did a google search before asking here and didn’t find it. It wasn’t until I searched for the error that was generated that I found the answer. It’s always fun trying to figure out the right words to use in google.

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

From: Michael D O'Shea/Woodward Informatics Ltd <woodwardinformatics_at_strychnine.co.uk> Sent: Thursday, March 24, 2022 3:09 PM
To: Scott Canaan <srcdco_at_rit.edu>
Cc: oracle-l_at_freelists.org
Subject: AW: sqlldr Question

Of course right after I posted this, I found the answer online. Someone else had exactly the same issue (yes, first_name field and all). Here’s the “fix”.

By asking here first rather than using Google or Stackoverflow at the outset, I can tell already you are older than 25 :-) The younger generations write entire application suites plagiarising copy/paste code from StackOverflow.

And in the ctl file too and using a replace rather than a regexp_replace as I suggested, it is win win for you today.

Mike

Von meinem iPhone gesendet

Am 24/03/2022 um 19:59 schrieb Scott Canaan <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>>:

Of course right after I posted this, I found the answer online. Someone else had exactly the same issue (yes, first_name field and all). Here’s the “fix”.

• Remove optionally enclosed by ‘”’ from after fields terminated by “,’
• Add optionally enclosed by ‘”’ to each character field, except the one that has the issue
• For that field, define it as:

first_name "replace(substr(:first_name,2,length(:first_name)-2), chr(34) || chr(34), chr(34))",

That strips the leading and ending quotes and loads the rest of the data into the field as is, so you end up with: Jonathan “Jon” in the field.

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> On Behalf Of Scott Canaan Sent: Thursday, March 24, 2022 2:22 PM
To: 'oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>' <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: sqlldr Question

I have a .csv file that I need to load into an Oracle table. Every character field is enclosed in double quotes (“). Two records have one field that contains double quotes and I’m supposed to load those fields into the database with the embedded double quotes included.

The control file begins with:

load data
infile table1.csv'
truncate
into table table1
fields terminated by "," optionally enclosed by '"' trailing nullcols

When it gets to those two records, they fail to load with the error:

Record 1659: Rejected - Error on table COOPEVAL_OWNER.SYMPLICITY_STUDENTS, column FIRST_NAME. no terminator found after TERMINATED and ENCLOSED field Record 9979: Rejected - Error on table COOPEVAL_OWNER.SYMPLICITY_STUDENTS, column FIRST_NAME. no terminator found after TERMINATED and ENCLOSED field

Here’s an example from one of the rows: "Yunhao "David""

How can I get these records loaded?

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659 CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

--
http://www.freelists.org/webpage/oracle-l