Home » RDBMS Server » Server Utilities » Escape character for loading data via sql loader
Escape character for loading data via sql loader [message #206751] Fri, 01 December 2006 06:15 Go to next message
tayalarun
Messages: 20
Registered: December 2005
Junior Member
Hi,

I have a text file which is comma separated with values enclosed in double quotes.

In my text file which I have to load into database, one of the field have the value like

Your \"offspring\"

When I run my normal sqlloader ctl file, it gives the error as

Record 304: Rejected - Error on table BUYER, column BUYERS_NAME.
no terminator found after TERMINATED and ENCLOSED field


Is there any way I can use some escape character for loading this type of data.


Thanks & Regards
Arun Tayal
Re: Escape character for loading data via sql loader [message #206760 is a reply to message #206751] Fri, 01 December 2006 06:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Cannot reproduce your case.
Please post the table ddl,some sample data and oracle version.
Re: Escape character for loading data via sql loader [message #206769 is a reply to message #206751] Fri, 01 December 2006 07:12 Go to previous messageGo to next message
tayalarun
Messages: 20
Registered: December 2005
Junior Member
Hi Mahesh,

I am using Oracle 10.2.0.2.0 on solaris 10.
I am running sqlldr from windows xp.

My table DLL :

Create Table BUYER(
BUYER_CODE NUMBER,
BUYER_NAME VARCHAR2(50));

SAMPLE DATA :

1,"XYZ IND"
2,"ABC"
3,"XYZ ABC"
4,Your \"offspring\"
5,"ATUL"

Here is my ctl :
LOAD DATA
INFILE '..\Data\buyer.txt'
BADFILE 'buyer.bad'
append into table BUYER
FIELDS TERMINATED BY ','
optionally enclosed by '"'
TRAILING NULLCOLS
(
buyer_code,
BUYERS_NAME char(50)
)


I hope this will help you to understand the problem.

Thanks & Regards
Arun Tayal
Re: Escape character for loading data via sql loader [message #206775 is a reply to message #206769] Fri, 01 December 2006 07:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Your controlfile specs are wrong.
Use something like this (you can use append instead of truncate and BADFILE )
C:\ora>type buyer.ctl
LOAD DATA
INFILE 'buyer.data'
truncate into table BUYER
FIELDS TERMINATED BY ',' optionally enclosed by '"' TRAILING NULLCOLS
(
buyer_code,
BUYER_NAME
)

Re: Escape character for loading data via sql loader [message #351568 is a reply to message #206775] Wed, 01 October 2008 10:52 Go to previous messageGo to next message
dsaha
Messages: 4
Registered: October 2008
Location: Chicago
Junior Member
What is wrong in his control file? I can see any.

Anyway, your suggested control file does not solve the problem. I have the similar problem and the problem is: either field separator (comma in his case) or enclosing char (quote in this case) exists itself in side the data followed by a escape char (\ in this case), can we handle it? Is there any way to set escape char for SQL loader? Because by default it is not escaping \" pr \,

My data look like this:

1, THIS IS A TEST DATA, TEST
2, THIS IS A \,TEST DA, TEST

it is loading as:

1 | THIS IS A TEST DATA | TEST
2 | THIS IS A \ | TEST DA

But want it loaded as:
1 | THIS IS A TEST DATA | TEST
2 | THIS IS A ,TEST DA | TEST



Re: Escape character for loading data via sql loader [message #351574 is a reply to message #351568] Wed, 01 October 2008 10:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Anyway, your suggested control file does not solve the problem

Because your problem is not exactly the same as above.
Did you read the second response?
>>Please post the table ddl,some sample data and oracle version.
And, use code tags, post your control file.
Re: Escape character for loading data via sql loader [message #351598 is a reply to message #351574] Wed, 01 October 2008 12:21 Go to previous messageGo to next message
dsaha
Messages: 4
Registered: October 2008
Location: Chicago
Junior Member

create table t1 (
id number,
text varchar2(350 char),
d date);

control file:

OPTIONS(skip=0, direct=true, ERRORS=50)
LOAD DATA
INFILE "../data/t1.data"
TRUNCATE
INTO TABLE t1
fields terminated by "|"
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
id,
text char(350),
d date 'YYYY-MM-DD HH24:MI:SS')


data file:

1|THIS IS A TEST DATA1|2008-09-30 17:56:58
2|THIS IS A \"TEST \"DATA2|2008-09-30 17:56:58
3|THIS IS A \|TEST DATA3|2008-09-30 17:56:59
4|THIS IS A TEST DATA4|2008-09-30 17:56:59

I am concerned about rec 2 and 3.

Thanks for your help.


Re: Escape character for loading data via sql loader [message #351602 is a reply to message #351598] Wed, 01 October 2008 12:32 Go to previous messageGo to next message
dsaha
Messages: 4
Registered: October 2008
Location: Chicago
Junior Member
Sorry forgot to mention the oracle version:
SQL*Loader: Release 10.2.0.1.0 - Production on Wed Oct 1 13:25:43 2008

I running it from "AIX 5"


Oracle database version:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production                          
CORE	10.2.0.3.0	Production                                      
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production  
Re: Escape character for loading data via sql loader [message #351620 is a reply to message #351602] Wed, 01 October 2008 14:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There are multiple escape characters without a pattern.
You can use "replace" function iteratively to remove the unwanted characters (slash and quotes) , but again may get stuck with '|' within data.
Sqlldr methods might exist, but I see a simple solution by prefixing the source datafile.
This can done with a one liner too!.
oracle@mutation#cat fixt1File
sed -e  's/\\|//g' t1.data > t.data
sed -e  's/\\"//g' t.data > final.data
cat final.data

oracle@mutation#fixt1File
1|THIS IS A TEST DATA1|2008-09-30 17:56:58
2|THIS IS A TEST DATA2|2008-09-30 17:56:58
3|THIS IS A TEST DATA3|2008-09-30 17:56:59
4|THIS IS A TEST DATA4|2008-09-30 17:56:59

Please let us know if it would not suit your needs.
Re: Escape character for loading data via sql loader [message #351625 is a reply to message #351620] Wed, 01 October 2008 15:01 Go to previous messageGo to next message
dsaha
Messages: 4
Registered: October 2008
Location: Chicago
Junior Member
Hi Mahesh,

Thanks for trying and your suggestion. Clean up is not an option in my case. I have keep the data as it is.

what we could do is replace \| with something stange like @@#$$%%% and then replace it back to original once data is loaded in oracle. But the data file is huge (couple of gig) and that is why we are concerned about performance also. Any kind of preprocessing ( like sed ) would take significant amount of time. So dont want to go to that option.

Only one thing comming to my mind is, let it load allowing errors to a certain number and then in bad file I wont have many records as this situation is very rare. After loading is done, taking the bad file, make insert statement and insert it those recs. The insert statement works great with escape chars.

Thanks again for your quick reply and suggestion.

~Debashis
Re: Escape character for loading data via sql loader [message #351629 is a reply to message #351625] Wed, 01 October 2008 15:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Any kind of preprocessing ( like sed ) would take significant amount of time
Not totally true.
While sed could be slow, PERL is very effective and made for "this" purpose. We have loaded files as large as 20 gigs using PERL.
Using functions iteratively is not exactly faster either.
>>let it load allowing errors to a certain number and then in bad file I wont have many records as this situation is very rare.

In that case, only the third record will fail here.
oracle@mutation#sqlldr userid=xx/xx control=somectl.ctl 

SQL*Loader: Release 9.2.0.8.0 - Production on Wed Oct 1 16:06:23 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Load completed - logical record count 4.
oracle@mutation#q

        ID TEXT                           D
---------- ------------------------------ ---------
         1 THIS IS A TEST DATA1           30-SEP-08
         2 THIS IS A TEST DATA2           30-SEP-08
         4 THIS IS A TEST DATA4           30-SEP-08

oracle@mutation#cat somectl.ctl
LOAD DATA
INFILE "t1.data"
TRUNCATE
INTO TABLE t1
fields terminated  by '|' optionally enclosed by '"'
(
id ,
text "replace(replace(:text,'\\'),'\"')",
d date 'YYYY-MM-DD HH24:MI:SS')
Re: Escape character for loading data via sql loader [message #351630 is a reply to message #351629] Wed, 01 October 2008 15:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
did you also try with external tables?
Re: Escape character for loading data via sql loader [message #376293 is a reply to message #351630] Tue, 16 December 2008 18:12 Go to previous messageGo to next message
neal.pressley
Messages: 9
Registered: December 2008
Junior Member
Hi there,

I am also finding the similar problem. I have a field which can contain special characters in my dump file got from mySQL database. I need to upload as it is to the oracle database thru SQL loader.

While exporting data from MySQL, I get fields terminated by "," and optionally enclosed by '"'. and choose '\' as escape character. So, I see a data like f"080726152552 is correctly appears in the dump file as "f\"080726152552".
But, now when I run the SQL Loader, it fails to take the escape character in account.


LOAD DATA
REPLACE
INTO TABLE tablename
FIELDS TERMINATED BY ',' OPTIONALLY  ENCLOSED BY '"'
TRAILING NULLCOLS
(
  SESSIONID
)


I saw Debashis also asked above the same thing that "cleanup is not an option" but I did not see Mahesh response answered that question. Now, the general question is if we are using double quotes as enclosed by character and escaping it correctly in the dump file, how to instruct the sql loader to take care of the same.

I have googled a lot on this and only I saw the same question asked by many but no responses.

neal

Re: Escape character for loading data via sql loader [message #376306 is a reply to message #376293] Tue, 16 December 2008 21:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
With my limited knowledge, I do not see any other native sqlldr option.
Either you fix the datafile before/after loading
or
generate the file in a format that sqlldr is happy about.
quote:
Quote:
and choose '\' as escape character.So, I see a data like f"080726152552 is correctly appears in the dump file as "f\"080726152552".


If you can afford to choose " as delimiter, it might work(at least with posted sample data) as is.
This worked just fine.
load data
infile *
truncate
into table tname
FIELDS TERMINATED BY ',' optionally enclosed  by '"' trailing nullcols
(
c1 
)
begindata
"f""080726152552"

[Updated on: Tue, 16 December 2008 23:36] by Moderator

Report message to a moderator

Re: Escape character for loading data via sql loader [message #376504 is a reply to message #376306] Wed, 17 December 2008 11:14 Go to previous messageGo to next message
neal.pressley
Messages: 9
Registered: December 2008
Junior Member
Hi Mahesh,

I guess you wanted to say that if I could afford to use " as "escape character" (instead of delimiter).

Yes, surprisingly it works if I use " as escape character instead of \ and sql loader is able to read the string with " correctly when a string f"080726152552 is escaped like this
"f""080726152552"


Now, the issue is different. When you use " as escape character, and you use the export from tool like SQLyog, it will put "N in the dump file for null fields instead of \N.

In my earlier control file, I had SITE NULLIF (SITE="\\N")
but to tackle this, now I use SITE NULLIF (SITE="\"N")
but I get an error message like

no terminator found after TERMINATED and ENCLOSED field

Regards
Neal
Re: Escape character for loading data via sql loader [message #376506 is a reply to message #376504] Wed, 17 December 2008 11:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Yes, surprisingly it works if I use
That is because in Oracle, we quote the quote.Smile

>>no terminator found after TERMINATED and ENCLOSED field
One workaround (that would work sometime) is not to declare optionally enclosed by '"' for the whole table.
Just use the same in column level.
Please post a sample set of data and your expected output.

[Updated on: Wed, 17 December 2008 11:21]

Report message to a moderator

Re: Escape character for loading data via sql loader [message #556589 is a reply to message #206751] Tue, 05 June 2012 09:24 Go to previous message
wryan67
Messages: 1
Registered: June 2012
Junior Member
I like this regex better for the sqlldr using escape codes. I tested it and it works fine in oracle 11g. The object here is to replace the string "\\p" with the pipe symbol, "|" in a file who's column separator is the pipe symbol, so that columns can be loaded which contain pipes.



  • test.ld
    LOAD DATA
    TRUNCATE
    INTO TABLE test_tb
    fields terminated  by '|'
    (
      id ,
      merch_desc "REGEXP_REPLACE(:merch_desc,'[\\][\\]p','|')",
      merch_zip
    )
    


  • desc test.sh
    #!/bin/ksh
    sqlldr CONTROL=test.ld \
           BAD=test.bad \
           DATA=test.dat \
           ROWS=2500000, ERRORS=0, \
           LOG=test.llg \
           BINDSIZE=524288, \
           DIRECT=TRUE, PARALLEL=FALSE \
           USERID=doesit1/****@rtt1
    


  • SQL> desc test_tb
    Name                            Null?    Type
    ------------------------------- -------- ----------------
    ID                                       VARCHAR2(9)
    MERCH_DESC                               VARCHAR2(64)
    MERCH_ZIP                                VARCHAR2(9)
    


  • test.dat
    100|starbucks\\plit|72211
    100|starbucks\\pcwy|72032
    


  • SQL> select * from test_tb;
    ID        MERCH_DESC                   MERCH_ZIP
    --------- ---------------------------- ---------
    100       starbucks|lit                72211
    100       starbucks|cwy                72032
    






Previous Topic: Alter Shared pool
Next Topic: How to associate a header record to its corresponding detail record
Goto Forum:
  


Current Time: Fri Dec 02 12:43:48 CST 2016

Total time taken to generate the page: 0.05030 seconds