Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2005
    Posts
    27

    Unanswered: no terminator found after TERMINATED and ENCLOSED field

    I am getting this error when I was trying to load the data into a table. Please do suggest me something.
    my control file is follows:
    load data
    infile "min.csv"
    replace
    into table min_telco_bridge_exp_det_final
    fields terminated by ',' optionally enclosed by '"'
    trailing nullcols
    (
    CS_NO,
    MONSYS_ID,
    MASCS,
    MASRCVR,
    CMCBRIDGE,
    RCVRTYPE,
    TECHNOLOGY,
    LEASED_LINE,
    SITE_NAME,
    SITE_ADDR1,
    SITE_ADDR2 ,
    CITY_NAME,
    STATE_ID,
    ZIP_CODE_4,
    ZIP_CODE,
    PHONE1,
    PHONE2,
    SITE_BRANCH_NO,
    SERVICE,
    CUST_NO,
    CUST_BRANCH_NO
    )

    and the infile is as follows:
    1)
    "19050112-2","BRBRAD","1905","240","San Bruno","Morse 5000","MPLEX-O",,"#PCN#OPE
    RA PLAZA(FIRE)","770 FRANKLIN STREET","WEST TOWER "B"","SAN FRANCISCO","CA","941
    02","94102",,,457,"In Service",955400228,457
    2) "Q340060221","KAELMH","Q34006","5","Kansas City","UCS","MPLEX-O","81FDPA013320 8
    1FDPA016800","PARMERLEE, JOHN SR M/M","718 W 111TH TER",""MAIN HOUSE"","KANSAS C
    ITY","MO","64114-5107","64114","8169420641",,880,"In Service",4604038,880


    the bold ones are creating problem.

    Please do suggest?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    You need to create your source file with delimiters which are NOT embeded in the data.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2005
    Posts
    27
    cant we make any modifications to the control file as I need to run this automatically every month.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Not with "embeded" terminators -- You need to change the source file.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You can have double "" to indicate a single quote, but it MUST look like the following and be enclosed by double quotes

    "19050112-2","BRBRAD","1905","240","San Bruno","Morse 5000","MPLEX-O",,"#PCN#OPERA PLAZA(FIRE)","770 FRANKLIN STREET","WEST TOWER ""B""","SAN FRANCISCO","CA","94102","94102",,,457,"In Service",955400228,4572) "Q340060221","KAELMH","Q34006","5","Kansas City","UCS","MPLEX-O","81FDPA013320 81FDPA016800","PARMERLEE, JOHN SR M/M","718 W 111TH TER","""MAIN HOUSE""","KANSAS CITY","MO","64114-5107","64114","8169420641",,880,"In Service",4604038,880
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by beilstwh
    You can have double "" to indicate a single quote, but it MUST look like the following and be enclosed by double quotes
    True, but then this field has to have the "" terminators in ALL the records.
    Therefore also forcing to reformat the source file.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    True, but what i am in the habit of doing is something like the following

    select '"'||replace(col1,'"','""')||'","'||replace(col2,' "','""')....
    from mytable;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by beilstwh
    True, but what i am in the habit of doing is something like the following
    select '"'||replace(col1,'"','""')||'","'||replace(col2,' "','""')....
    from mytable;
    This post refers to sql loader control file, where does the "select" come from?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I assume that the comma delimited file is being produced from somewhere. However, It wa a lession in making comma delimited files, not code to fix their problem.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    OK, as I said, the problem is at the creation of the source file.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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