sql loader how to ignore error due to quotes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: sql loader how to ignore error due to quotes

  1. #1
    Join Date
    Jan 2010
    Posts
    20

    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.

  2. #2
    Join Date
    Jan 2010
    Posts
    20
    or is there a way to trim first and last quote ( " ) instead of using OPTIONALY ENCLOSED by ' " '

  3. #3
    Join Date
    Jan 2010
    Posts
    20
    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)",

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    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.

  5. #5
    Join Date
    Jan 2010
    Posts
    20
    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.?

  6. #6
    Join Date
    May 2002
    Posts
    2,645

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width