Home » SQL & PL/SQL » SQL & PL/SQL » Double Quotes in Data+External Tables+SQL
Double Quotes in Data+External Tables+SQL [message #232684] Mon, 23 April 2007 00:43 Go to next message
Dorababu
Messages: 1
Registered: April 2007
Location: Hyderabad
Junior Member
Hi

I am working on External Tables, I want to load the .CSV Data into These Table. Here i am facing a problem.

External Table Code

CREATE TABLE contacts_ext(
 customer_name         VARCHAR (50) 
,customer_number       VARCHAR (30) 
,Cust_orig_system_reference VARCHAR (240) 
)  
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY actar_cust_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline SKIP 1
BADFILE actar_cust_dir:Cont.bad'
DISCARDFILE actar_cust_dir:'Cont.dis'
LOGFILE actar_cust_dir:'Cont.log'
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(customer_name         
,customer_number       
,Cust_orig_system_reference 
)
 )
LOCATION (Cont.csv')
)
REJECT LIMIT UNLIMITED;


Error

KUP-04021: field formatting error for field CUSTOMER_NAME
KUP-04037: terminator not found


And My Data is like..

"Family Toy Warehouse DIP"" (CIA)""",	2274,	2091
"Family Toy Warehouse DIP"" (CIA)""",	2274,	2091	
"Family Toy Warehouse DIP"" (CIA)""",	2274,	2091


I was identifed the Problem, is with Customer name(Data).

As per my External Table Script , OPTIONALLY ENCLOSED BY '"' clause looking ofr Data should be within the Double Quotes, but in my Customer Data itself having another Double Quotes.

Now how to Handle This


Please Help Its Very Urgent.

Thanks InAdvance

Dorababu Kethari


[MOD: Added code tags]


[Updated on: Sun, 29 April 2007 02:04] by Moderator

Report message to a moderator

Re: Double Quotes in Data+External Tables+SQL [message #234024 is a reply to message #232684] Sun, 29 April 2007 02:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Show us the row in the bad file actar_cust_dir:Cont.bad

According to the doco, the FIELDS TERMINATED BY "," should be FIELDS TERMINATED BY ',' (ie. put the comma in single quotes). Not sure if that makes a difference.

Ross Leishman
Re: Double Quotes in Data+External Tables+SQL [message #234486 is a reply to message #232684] Tue, 01 May 2007 14:58 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
IMHO - you have to use some other character (OPTIONALLY ENCLOSED BY "|" - for example).

Previous Topic: How to find this node value
Next Topic: How create a copy of database
Goto Forum:
  


Current Time: Mon Dec 05 09:11:22 CST 2016

Total time taken to generate the page: 0.10019 seconds