Skip to Main Content
  • Questions
  • how to preserver double quotes in the data using sqlloader data load.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, nnv.

Asked: March 06, 2017 - 4:42 pm UTC

Last updated: March 23, 2017 - 5:18 am UTC

Version: oracle 11g

Viewed 10K+ times! This question is

You Asked

hey I have data like below and need to preserve double quotes in data.

Please let me know how to load data preserving double quotes using sql loader. Here record Filed 2 last line is in multiple lines marked in bold

data

"Filed 1","Field 2","Field 3"
"1","test1","test1"
"2","test2","TESt double quotes
""NEW"" REgardssdfdsfdsf,
Prasad
"
"3","test3","test4"


value for your reference which has double quotes for 2 nd record. NEW value should store with double quotes
"TESt double quotes
""NEW"" REgardssdfdsfdsf,
Prasad
"

and Connor said...

Rating

  (4 ratings)

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

Comments

from windows environment.

Rajeshwaran, Jeyabal, March 09, 2017 - 7:42 am UTC

Team,

I am running this from Windows environment, but i dont get the multiple lines of input into the column Z.

demo@ORA12C>
demo@ORA12C> create table t(
  2    x varchar2(10),
  3    y varchar2(20),
  4    z varchar2(100) );

Table created.

demo@ORA12C> host
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\179818>sqlldr demo/demo@ora12c control=d:\ctl.txt log=d:\log.txt

SQL*Loader: Release 12.1.0.2.0 - Production on Thu Mar 9 12:51:59 2017

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

SQL*Loader-283: file processing string "str X'7C0D0A'" ignored for INFILE *
Path used:      Conventional
Commit point reached - logical record count 5

Table T:
  3 Rows successfully loaded.

Check the log file:
  d:\log.txt
for more information about the load.

C:\Users\179818>exit

demo@ORA12C> column z format a30
demo@ORA12C> select * from t;

X          Y                    Z
---------- -------------------- ------------------------------
"1"        "test1"              "test1"|
"2"        "test2"              "TESt double quotes
"3"        "test3"              "test4"|

demo@ORA12C>

Here is my control file.
demo@ORA12C> $type d:\ctl.txt
load data
infile * "str X'7C0D0A'"
into table t
truncate
fields terminated by ','
(x , y ,z )

begindata
"1","test1","test1"|
"2","test2","TESt double quotes
""NEW"" REgardssdfdsfdsf,
Prasad"|
"3","test3","test4"|

demo@ORA12C>


did i miss something here?
Connor McDonald
March 11, 2017 - 2:28 am UTC

Look at the line underneath the "Copyright" one and above the "Path used" one :-)

from windows environment.

Rajeshwaran, Jeyabal, March 11, 2017 - 3:29 am UTC

Thanks, when isolated the data from control file - it got loaded successfully.

C:\Users\179818>sqlldr demo/demo@ora12c control=d:\ctl.txt log=d:\log.txt

SQL*Loader: Release 12.1.0.2.0 - Production on Sat Mar 11 08:55:19 2017

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

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

Table T:
  3 Rows successfully loaded.

Check the log file:
  d:\log.txt
for more information about the load.

C:\Users\179818>exit

demo@ORA12C> column z format a40
demo@ORA12C> select * from t;

X          Y                    Z
---------- -------------------- ----------------------------------------
"1"        "test1"              "test1"
"2"        "test2"              "TESt double quotes
                                ""NEW"" REgardssdfdsfdsf
                                Prasad"

"3"        "test3"              "test4"

demo@ORA12C> 


Here is my control file and data files.

demo@ORA12C> $type d:\ctl.txt
load data
infile "d:\data.txt" "str X'7C0D0A'"
into table t
truncate
fields terminated by ','
(x , y ,z )


demo@ORA12C> $type d:\data.txt
"1","test1","test1"|
"2","test2","TESt double quotes
""NEW"" REgardssdfdsfdsf
Prasad"|
"3","test3","test4"|

demo@ORA12C>

using functions in control file

Rajeshwaran, Jeyabal, March 11, 2017 - 3:44 am UTC

now, tweaked the data file slightly different.

using "\n" to represent the new line character and then from the control file while loading tried to replace "\n" with chr(13)||chr(10)

this approach doesn't work from sql*loader. could you help us to understand?

So here is my control files and data files.

demo@ORA12C> $type d:\ctl2.txt
load data
into table t
truncate
fields terminated by ','
(       x ,
        y ,
        z "replace(:z,'\n',chr(13)||chr(10))")



demo@ORA12C> $type d:\data2.txt
"1","test1","test1"
"2","test2","TESt double quotes \n""NEW"" REgardssdfdsfdsf \nPrasad"
"3","test3","test4"
demo@ORA12C>


here is my data load.

C:\Users\179818>sqlldr demo/demo@ora12c control=d:\ctl2.txt log=d:\log.txt data=d:\data2.txt

SQL*Loader: Release 12.1.0.2.0 - Production on Sat Mar 11 09:02:22 2017

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

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

Table T:
  3 Rows successfully loaded.

Check the log file:
  d:\log.txt
for more information about the load.

C:\Users\179818>exit

demo@ORA12C> select * from t;

X          Y                    Z
---------- -------------------- ----------------------------------------
"1"        "test1"              "test1"
"2"        "test2"              "TESt double quotes \n""NEW"" REgardssdf
                                dsfdsf \nPrasad"

"3"        "test3"              "test4"

demo@ORA12C> 


you could see the "\n" is not getting replaced during loads.

but when tried to invoke this replace from sql it works fine.

demo@ORA12C> column x format a40
demo@ORA12C> select z, replace(z,'\n',chr(13)||chr(10)) x
  2  from t  ;

Z                                        X
---------------------------------------- ------------------------------
"test1"                                  "test1"
"TESt double quotes \n""NEW"" REgardssdf "TESt double quotes
dsfdsf \nPrasad"                         ""NEW"" REgardssdfdsfdsf
                                         Prasad"

"test4"                                  "test4"

demo@ORA12C>


while the sql*loader log file shows this.

Table T, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
X                                   FIRST     *   ,       CHARACTER            
Y                                    NEXT     *   ,       CHARACTER            
Z                                    NEXT     *   ,       CHARACTER            
    SQL string for column : "replace(:z,'
',chr(13)||chr(10))"


don't understand why the replace() command appear this way in the log file ( the second argument to the replace command "\n" is not shown) why ?
Connor McDonald
March 23, 2017 - 5:18 am UTC

'\' is an escape character.

Change your control file to:

load data
infile "data.txt"
into table t
truncate
fields terminated by ','
(       x ,
        y ,
        z "replace(:z,'\\n',chr(13)||chr(10))"   <<==== see double slash
) 


and you should be ok

using functions in control file

Rajeshwaran, March 23, 2017 - 7:31 am UTC

Thanks, that helps.