Skip to Main Content
  • Questions
  • Replacing special characters CR and LF while loading data using SQL Loader

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Sam.

Asked: June 14, 2016 - 1:48 am UTC

Last updated: November 12, 2018 - 9:57 am UTC

Version: 9.0.4

Viewed 50K+ times! This question is

You Asked

Hello everyone,

I am looking for some much needed advice on what options I can use to replace special characters Carriage Return (hex 0D) and Line Feed (hex 25) when loading them into Oracle using SQL Loader. These 2 special characters occur together and are used as a delimiter between fields within each record, and I would like to replace them with say '..' (two periods). If my question is not clear or any additional information is required, please ask.

Thanks in advance,
Sam.


and Chris said...

There's two things you'll need to do:

- Edit your input fields to have a custom "end of record" delimiter
- Specify replace in the column definition to convert CR LF to ".."

For example, here's our input file, temp.txt. Notice the #EOR# marker. This marks the end of a record:
"this should all be
one line"#EOR#


You can specify in the SQL*Loader control file with the str option of infile:
load data infile 'temp.txt' "str '#EOR#'"
  truncate
  into table t
  fields terminated by ',' (
    x char(100) "replace(:x, chr(13)||chr(10), '..')" 
  )


Notice the replace in the field definition. Now when we try to load temp.txt using this control file, we get:

C:\Users\csaxon\Documents>sqlldr userid=xxx/xxx control=load.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Jun 14 09:54:53 2016

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

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

Table T:
  1 Row successfully loaded.

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


Just one row loaded! Query the contents of the table and we can see the conversion:

select * from t;

X                                                                                                  
---------------------------------------
"this should all be..one line" 

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Spot on, thanks

Sam Weisneth, July 07, 2016 - 11:28 pm UTC

Hi Chris,

Sorry I did not get time to test your solution until today and it worked fine the very first time. Thanks a lot, really appreciate your help.

Best Regards,
Sam.
Connor McDonald
July 08, 2016 - 3:52 am UTC

glad we could help

neglecting the delimiter CHR(10)

suman jami, June 14, 2018 - 11:58 am UTC

Hello,

i am trying to upload a .csv file to oracle table using pl/sql, but when my last row of the file is a column of number field then i am getting "invalid number" ORA error as it is attached with the delimiter or new line charecter which is in my case CHR(10).

is there any way to remove/ignore the newline charecter while uploading.

thanks in advance.

regards,
Connor McDonald
June 15, 2018 - 1:20 am UTC

A rtrim should take care of it

SQL> declare
  2    x varchar2(10) := '12345
  3  ';
  4    y int;
  5  begin
  6    y := to_number(x);
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 6


SQL>
SQL> declare
  2    x varchar2(10) := '12345
  3  ';
  4    y int;
  5  begin
  6    y := to_number(rtrim(x,chr(10)));
  7  end;
  8  /

PL/SQL procedure successfully completed.



It Worked!!!

Ei, November 10, 2018 - 3:47 am UTC

This is in response to the post,

Replacing special characters CR and LF while loading data using SQL Loader

Just to clarify, the header record should also contain the custom string delimiter, correct? Otherwise, sqlldr ignores the first data record.

#EOR#

This is my control file:

OPTIONS (DIRECT=TRUE, SKIP=1)
LOAD DATA
INFILE 'Open Req.csv' "str '#EOR#'"
BADFILE 'Open Req.bad'
TRUNCATE
INTO TABLE OPEN_REQ
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
REQ_NAME CHAR(240),
REQ_NUM CHAR(240),
REQ_ID CHAR(240),
REQ_DESC CHAR(1000) "replace(:TBH_DESC, CHR(13)||CHR(10), ' ')",
DISPLAY_NAME CHAR(1000),
REQ_APPROVAL_DATE "TO_DATE(:REQ_APPROVAL_DATE, 'YYYY-MM-DD')",
EOR FILLER
)
Chris Saxon
November 12, 2018 - 9:57 am UTC

It lumps together all lines until there's an #EOR#. So yes, if your header DOESN'T have an #EOR#, then it'll get merged with the first data line. And you'll skip both.

It Worked!!! - Reply

ei, November 13, 2018 - 11:00 pm UTC

Thanks for the quick confirmation.