| Escape character for loading data via sql loader [message #206751] |
Fri, 01 December 2006 06:15  |
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 #206769 is a reply to message #206751] |
Fri, 01 December 2006 07:12  |
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 #351568 is a reply to message #206775] |
Wed, 01 October 2008 10:52 |
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 #351598 is a reply to message #351574] |
Wed, 01 October 2008 12:21 |
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 |
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 #351625 is a reply to message #351620] |
Wed, 01 October 2008 15:01 |
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 #376293 is a reply to message #351630] |
Tue, 16 December 2008 18:12 |
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 |
 |
Mahesh Rajendran
Messages: 10707
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 |
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 |
|
Mahesh Rajendran
Messages: 10707
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.
>>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 |
 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
