Oracle Sqlldr error with column having crlf-CodePudding

I am trying to load a '|' delimited file, but it fails because some columns have crlf values.

I converted the text files to xlsx and imported them successfully using SQL Developer.

I noticed that SQL Developer using a Line Terminator option set to "standard: CR LF, CR or LF".

I suspect that I need to set that in my ctl file, but have been unable to find the correct syntax.

Any assistance would be appreciated.

Here is a screenshot from SQL Developer:

CodePudding user response：

As usual, it helps if you post what you have (in this case, control file, table description and sample data). Without it, we have to guess and that doesn't have to reflect reality.

Sample table:

SQL> create table test
  2    (id          number,
  3     description varchar2(50));

Table created.

Control file (contains sample data):

load data
infile *
replace
continueif next preserve(1:1) != "|"
into table test
fields terminated by '|'
trailing nullcols

(
  dummy filler,
  id,
  description 
)

begindata
|1|this is some text 
with no meaning 
at all
|2|some 
more text

Loading session:

SQL> $sqlldr scott/tiger@pdb1 control=test11.ctl log=test11.log

SQL*Loader: Release 21.0.0.0.0 - Production on Sat Nov 26 21:51:53 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2

Table TEST:
  2 Rows successfully loaded.

Check the log file:
  test11.log
for more information about the load.

Result:

SQL> select * from test;

        ID DESCRIPTION
---------- --------------------------------------------------
         1 this is some text with no meaning at all
         2 some more text

SQL>

CodePudding user response：

Try

INFILE 'target.dat' "STR X'220D0A'"

The terminator_string is specified as either 'char_string' or X'hex_string' where:

'char_string' is a string of characters enclosed in single or double quotation marks X'hex_string' is a byte string in hexadecimal format that you can get with the cast_to_raw function.

So here X'220D0A' is "|CRLF" and this will keep the "CRLF" in the imported column.