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>:
>
> 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 <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' <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