-
SQL*LOADER from a csv field into CLOB column
Loading from CSV file into CLOB
Can someone point out what is wrong? I looked in all the other examples and this should be OK...or is it? The CLOB field is not being recognized a one input field?
-Thanks in advance...
egavish@snet.net
----------------My table to load:
ARTICLE_ID NUMBER
ARTICLEDATE VARCHAR2(15)
ARTICLETITLE VARCHAR2(255)
SHOWARTICLE VARCHAR2(10)
ARTICLEBODY CLOB
--------------My command line:
sqlldr userid=scott/tiger@ora9 control=load_articles.ctrl log=load_articles.log readsize=10240000
---------------My control file:
load data
INFILE 'articles.TXT'
REPLACE
INTO TABLE articles
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(ARTICLE_ID integer external,
ArticleDate char,
ArticleTitle char,
SHOWArticle char,
ARTICLEBODY char(40000) ENCLOSED BY '' AND '' )
-------my CSV file with one record(clob field has line breaks):
1,"06/17/1999","Ford Going To Bahia","TRUE","Ford Motor Co. said it will build a new factory in Bahia, Brazil.
The $1.3 billion plant will employ 5,000 workers and is scheduled to open in 2001.
For more information
onclick=""OpenNavWin('http://dowjones.wsj.com/i/auto/SB929558916336243030-d-industry-c1-auto.html','_blank')"">click here.
"
--------My Errors in log file:
SQL*Loader: Release 9.2.0.1.0 - Production on Tue Mar 18 02:31:51 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Control File: load_articles.ctrl
Data File: articles.TXT
Bad File: articles.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table ARTICLES, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ARTICLE_ID FIRST * , O(") CHARACTER
ARTICLEDATE NEXT * , O(") CHARACTER
ARTICLETITLE NEXT * , O(") CHARACTER
SHOWARTICLE NEXT * , O(") CHARACTER
ARTICLEBODY NEXT 40000 , CHARACTER
First enclosure string : ''
Second enclosure string : ''
Record 1: Rejected - Error on table ARTICLES, column ARTICLEBODY.
second enclosure string not present
Record 4: Rejected - Error on table ARTICLES, column ARTICLE_ID.
second enclosure string not present
Record 2: Rejected - Error on table ARTICLES, column ARTICLE_ID.
ORA-01722: invalid number
Record 3: Rejected - Error on table ARTICLES, column ARTICLE_ID.
ORA-01722: invalid number
-
SQLLDR will not be able to interpret the data for the CLOB column, because the text you try to load into it contains both ',' and '"'.
If possible, use another delimiter between the columns, or enclose text columns with another character (make sure that this character will not be present within the text itself...)
-v
-
I thought the 'ENCLOSED BY <...> AND <...>' set up the bounderies for the CLOB field? but it doesnt seem to matter.
NOTE: (somehow the "ENCLOSED BY and " did not display correctly in the above message. the 'single quotes display empty. But they are not)
Last edited by egavish; 03-18-2003 at 09:09 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|