-
sql loader how to ignore error due to quotes
Hi,
I have control file written like
Code:
LOAD DATA
APPEND
INTO TABLE MYTABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
but i have data in csv file like
660501,1,"0187591","12"PEGHOOKW/SA",,"04/03/2002",
Since there is an extra double quote (denoting inch) in the third column, im getting an error. Is there any way to avoid this error without modifying the csv file.
-
or is there a way to trim first and last quote ( " ) instead of using OPTIONALY ENCLOSED by ' " '
-
or is there a way to use trim function to trim first and last quote ( " ) instead of using OPTIONALY ENCLOSED by ' " '
my control file looks like
LOAD DATA
APPEND
INTO TABLE MYTABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
LOCATION_NAME CHAR "decode(:LOCATION_NAME,null,' ',:LOCATION_NAME)",
-
If the fields are consistently separated by commas, don't worry about the optionally enclosed clause. Load the data, then do the trim of the double quotes within SQL.
-
sorry for the trouble. it would really of great help if you could let me know on how to use trim to achive this task.? can trim be be used to cut only the first and last quote characters in a column.?
-
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
|