Home » RDBMS Server » Server Utilities » Unescaped quotes & External Tables
Unescaped quotes & External Tables [message #434334] Tue, 08 December 2009 13:25 Go to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
How does one deal with unescaped quotes? I'm asking specifically in relation to External Tables, but I'm certain a SQL Loader solution could be adapted to fit my needs.

For instance how would I load the following?

"A",1,"this is "funky""
"B",2,"this is normal"
"C",2,"this is normal"


This is what I use so far:

<copy the above to YOUR_PATH\a.csv>
<connect to schema that can create oracle directories>

create or replace directory rawdata as 'YOUR_PATH';
grant read, write on directory rawdata to YOUR_SCHEMA;

<connect to YOUR_SCHEMA>

CREATE TABLE t_test
(
col1 VARCHAR2(8),
col2 VARCHAR2(8),
col3 VARCHAR2(64)
)
ORGANIZATION EXTERNAL
(
DEFAULT DIRECTORY bc_pims
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
)
LOCATION ('a.csv')
)
REJECT LIMIT 3

select * from t_test


Current output is:
COL1	COL2	COL3
B	2	this is normal
C	3	this is normal

With the A column showing in the bad file.

Desired output is:
COL1	COL2	COL3
A	1	this is "funky"
B	2	this is normal
C	3	this is normal
Re: Unescaped quotes & External Tables [message #434338 is a reply to message #434334] Tue, 08 December 2009 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You simply can't.
Either the character is a delimiter, either it is not. It can't be both.

Regards
Michel
Re: Unescaped quotes & External Tables [message #434343 is a reply to message #434334] Tue, 08 December 2009 14:09 Go to previous messageGo to next message
tmcallister
Messages: 107
Registered: December 2007
Senior Member
Delimiters and enclosures seem to work differently. I can understand delimiters not being nestable, but it seems to me that enclosures should be nestable (that's how I'd do it at least), but they are not.

It could be 'hacked' in that the 'optionally enclosed by' clause could be removed, and then each column could simply be substring'ed from position 2 to it's length - 2. But this would negate the feature that the delimiter can be enclosed and not count as the delimiter.

Newbie question, can one modify the data with External Tables as one does with SQL Loader?

<ctl file stuff>
    col1, "substr(:col1, 2, length(:col1)-2)",
<ctl file stuff>


How would one translate the above into and external table creation statement.

I guess the only other option is to preprocess the file outside of oracle.

Re: Unescaped quotes & External Tables [message #434346 is a reply to message #434343] Tue, 08 December 2009 14:44 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With external table the data are modfied in the qiery that load the target table not in the definition of the external.
Remember thet external table definition is much more a defintion of how are the data in the file that how the data has to be loaded.
An external table is a relational view of your datafile not the target itself.
So with SQL to load you have much more possibilities than with SQL*Loader language.
And to answer your question you do it with a simple SQL statement:
insert into target
select col1, substr(col2,2,length(col2)-2), ... 
from external_table;

Regards
Michel

[Updated on: Tue, 08 December 2009 14:46]

Report message to a moderator

Previous Topic: How to choose between exp/imp or clone by copying data files ?
Next Topic: sql ldr
Goto Forum:
  


Current Time: Thu Dec 08 01:59:25 CST 2016

Total time taken to generate the page: 0.06437 seconds