| Unescaped quotes & External Tables [message #434334] |
Tue, 08 December 2009 13:25  |
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 #434343 is a reply to message #434334] |
Tue, 08 December 2009 14:09  |
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 |
 |
Michel Cadot
Messages: 68641
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
