AW: sqlldr Question

It will be (something like) this in the control file

FIELDS TERMINATED BY ",“ (:col, forename " REGEXP_REPLACE(:forename, something horrid goes here)“, surname " REGEXP_REPLACE(:col, something horrid goes here)“

Where „something horrid goes here“ is a regex (that just replaces one problem with another, I know). Also, for your specific example, I think you have to use lookahead, and the last time I looked and this was 11g it was not supported in regex.

Possibly this is just one of those ETL things, where you have to do the T before the L and fix up the double quotes first.

If this is something ongoing, there are loads of third party libraries that handle this sort of quote nonsense in CSV files painlessly. Again, if this is something that is more regular rather than a one off, you might choose to use CSVHelper and a Java stored proc, but thinking about it Java regex does support lookaheads so this might be the regex route of choice if you wanted to turn a 10 minute job into an afternoon of anguish.

Or maybe I am just overthinking it all … will be interesting to see what the other listers propose.

Mike

Von meinem iPhone gesendet

> Am 24/03/2022 um 19:21 schrieb Scott Canaan <srcdco_at_rit.edu>:
>
> 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