From oracle-l-bounce@freelists.org Wed Mar 17 08:39:19 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2HEdJh18109 for ; Wed, 17 Mar 2004 08:39:19 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i2HEdFo18085 for ; Wed, 17 Mar 2004 08:39:15 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E78ED390B2D; Wed, 17 Mar 2004 09:36:13 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 17 Mar 2004 09:35:10 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from syn2kex2.smdi.com (syn2kex2.smdi.com [204.52.186.149]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7E3F8390BD3 for ; Wed, 17 Mar 2004 09:35:06 -0500 (EST) Received: from SYN2KEX1.smdi.com ([10.10.10.7]) by syn2kex2.smdi.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 17 Mar 2004 09:41:08 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C40C24.FB377A2C" Subject: RE: sql loader question Date: Wed, 17 Mar 2004 08:37:24 -0500 Message-ID: <91AFBA9B76078B4E8340A383EADEF1DB7EE571@syn2kex1.smdi.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: sql loader question Thread-Index: AcQLrR7UVR7frpJvQTGnLsTbzK2n/gAdsYvw From: "John Flack" To: X-OriginalArrivalTime: 17 Mar 2004 14:41:08.0515 (UTC) FILETIME=[E26E2F30:01C40C2D] X-archive-position: 1037 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: JohnF@smdi.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l ------_=_NextPart_001_01C40C24.FB377A2C Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Can you apply the "optionally enclosed by" to individual fields instead = of all fields? Then you could leave it off of the offending field and = use some SQL to remove the quotes that are now not regarded as = enclosuring marks. Like this: AH_DESCRIPT CHAR "SUBSTR(:AH_DESCRIPT,2,LENGTH(:AH_DESCRIPT)-1)", -----Original Message----- From: ed lewis [mailto:eglewis@hotmail.com] Sent: Tuesday, March 16, 2004 6:18 PM To: oracle-l@freelists.org Subject: sql loader question Hi, I have a file that I'm attempting to load into a table with sql loader using the following ctl file. On the "ah_descript" column I receive the following error on some of the records : =20 no terminator found after TERMINATED and ENCLOSED field =20 The column contains a " (double quote) used to designate inches,=20 such as 6", which I think sql loader is choking on. Is there a way I can handle this within sql loader ? =20 thanks very much. =20 =20 OPTIONS (SKIP=3D1) LOAD DATA=20 infile 'csv/12292003-AHPOLNTMP.csv' INTO TABLE sdusr3.ahpolntmp=20 fields terminated by ',' optionally enclosed by '"' trailing nullcols ( AHDATE DATE 'MM/DD/YYYY', AHTIME char , PO_NUMBER CHAR, PO_LAWSON CHAR, LINE_NBR char, COST_OPTION CHAR, AH_DESCRIPT CHAR, DISTRIBFLAG CHAR, EARLY_DL_DATE DATE 'MM/DD/YYYY', ENT_BUY_UOM CHAR, ENT_UNIT_CST char, ERRORCODE char, ERRORMSG CHAR, GLOBALITEMID CHAR, ITEM CHAR, ITEM_TYPE CHAR, MANUF_NBR CHAR, PURCHCLASS CHAR, QUANTITY char, REQUESTERID CHAR, VEN_ITEM CHAR, CUSTOM1 CHAR, CUSTOM2 CHAR, CUSTOM3 CHAR ) ------_=_NextPart_001_01C40C24.FB377A2C Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Can=20 you apply the "optionally enclosed by" to individual fields instead of = all=20 fields? Then you could leave it off of the offending field and use = some=20 SQL to remove the quotes that are now not regarded as enclosuring = marks. =20 Like this:

AH_DESCRIPT CHAR=20 "SUBSTR(:AH_DESCRIPT,2,LENGTH(:AH_DESCRIPT)-1)",
=

-----Original Message-----
From: ed lewis=20 [mailto:eglewis@hotmail.com]
Sent: Tuesday, March 16, 2004 = 6:18=20 PM
To: oracle-l@freelists.org
Subject: sql loader=20 question

Hi,

    I have a = file that I'm=20 attempting

to load into a table with sql = loader

using the following ctl = file.

On the "ah_descript" column I receive = the=20 following

error on some of the records = :

no terminator found after TERMINATED = and ENCLOSED=20 field

The column contains a " (double = quote) used to=20 designate inches,

such as 6", which I think sql loader = is choking=20 on.

Is there a way I can handle this = within sql=20 loader ?

thanks very much.

OPTIONS (SKIP=3D1)
LOAD DATA =
infile=20 'csv/12292003-AHPOLNTMP.csv'
INTO TABLE sdusr3.ahpolntmp
fields = terminated by ',' optionally enclosed by '"'
trailing=20 nullcols
(   AHDATE      DATE=20 'MM/DD/YYYY',
    = AHTIME      char=20 ,
    PO_NUMBER CHAR,
    = PO_LAWSON=20 CHAR,
    LINE_NBR char,
    = COST_OPTION   CHAR,
    = AH_DESCRIPT  =20 CHAR,
    DISTRIBFLAG  =20 CHAR,
    EARLY_DL_DATE DATE=20 'MM/DD/YYYY',
    ENT_BUY_UOM  =20 CHAR,
    ENT_UNIT_CST = char,
   =20 ERRORCODE     char,
   =20 ERRORMSG      CHAR,
   =20 GLOBALITEMID CHAR,
   =20 ITEM         =20 CHAR,
    ITEM_TYPE    =20 CHAR,
    MANUF_NBR    =20 CHAR,
    PURCHCLASS   =20 CHAR,
    QUANTITY     =20 char,
    REQUESTERID  =20 CHAR,
    VEN_ITEM     =20 CHAR,
    CUSTOM1    =20 CHAR,
    CUSTOM2    =20 CHAR,
    CUSTOM3    =20 CHAR
)

------_=_NextPart_001_01C40C24.FB377A2C-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------