| SQL*Loader delimiter/enclosing problem [message #74315] |
Thu, 11 November 2004 08:01  |
Debbie
Messages: 19
Registered: December 2002
|
Junior Member |
|
|
I am loading a CSV file using this delimiter spec in the control file:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
A few rows are being rejected with this error:
Record 5378: Rejected - Error on table CISIF.ADS_PREMISE, column ACCT_NAME.
No terminator found after TERMINATED and ENCLOSED field
Here's the BAD file contents for my 3 rejected rows:
3005517,AC,,"WILSON, JACOB "JAKE" D",,9495472111,,LH
3063601,AC,,"HEREDIA, MEMIN "PINGUIN"",,2818108,,NO
3097353,AC,,"DREW, ELSWORTH "JIM"",,9286807448,,LH
It's the quotation marks around the nicknames which appear at the end of the account_name field that seem to be causing the problem. This is what those field values actually look like out of the CSV file if you open it in Excel:
WILSON, JACOB JAKE" D"
HEREDIA, MEMIN PINGUIN""
DREW, ELSWORTH JIM""
What needs to happen to get these values to load?
Thanks in advance
|
|
|
|
| Re: SQL*Loader delimiter/enclosing problem [message #74316 is a reply to message #74315] |
Thu, 11 November 2004 18:48  |
jacqui
Messages: 1
Registered: November 2004
|
Junior Member |
|
|
I have had a similar problem which I solved using SQL*Loader: Release 9.2.0.1.0 but unfortuneately it does not work in version 8.1.7.0.0
the ctl file looks something like this
LOAD DATA
INFILE blah.csv"
BADFILE "blah.bad"
DISCARDFILE "blah.dsc"
REPLACE
INTO TABLE blah
TRAILING NULLCOLS
(
blah_id FLOAT EXTERNAL TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' ,
anotherblah_id FLOAT EXTERNAL TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' ,
quoteproblem_field TERMINATED BY "|" "replace(:quoteproblem_field ,'"')",
blah_status TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' ,
)
maybe this helps ...
Regards
Jacqui
PS does anyone know if you can upgrade SQLLDR without updating the rest of the Oracle Client ????
Thanks
J
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
