Escape character for loading data via sql loder
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Escape character for loading data via sql loder

  1. #1
    Join Date
    Dec 2005
    Posts
    19

    Escape character for loading data via sql loder

    Hi,

    I have a text file which is comma separated with values enclosed in double quotes.

    In my text file which I have to load into database, one of the field have the value like

    Your \"offspring\"

    When I run my normal sqlloader ctl file, it gives the error as

    Record 304: Rejected - Error on table BUYER, column BUYERS_NAME.
    no terminator found after TERMINATED and ENCLOSED field


    Is there any way I can use some escape character for loading this type of data.


    Thanks & Regards
    Arun Tayal

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    post your control file

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,441

    Cool

    And a sample of your data file.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Dec 2005
    Posts
    19
    Hi,

    I am using Oracle 10.2.0.2.0 on solaris 10.
    I am running sqlldr from windows xp.

    My table DLL looks like :

    Create Table BUYER(
    BUYER_CODE NUMBER,
    BUYER_NAME VARCHAR2(50));

    SAMPLE DATA :

    1,"XYZ IND"
    2,"ABC"
    3,"XYZ ABC"
    4,"Your \"offspring\""
    5,"ATUL"

    Here is my ctl :
    LOAD DATA
    INFILE 'Data\buyer.txt'
    BADFILE 'buyer.bad'
    append into table BUYER
    FIELDS TERMINATED BY ','
    optionally enclosed by '"'
    TRAILING NULLCOLS
    (
    buyer_code,
    BUYERS_NAME char(50)
    )


    I hope this will help you to understand the problem.

    Thanks & Regards
    Arun Tayal

  5. #5
    Join Date
    Jun 2006
    Posts
    259
    Change the enclosure character to something other than "... I used ' for example and was able to load the data.
    Or you could used FIXED width fields.

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