RE: sql loader question

From: John Flack <JohnF_at_smdi.com>
Date: Wed, 17 Mar 2004 08:37:24 -0500
Message-ID: <91AFBA9B76078B4E8340A383EADEF1DB7EE571@syn2kex1.smdi.com>

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_at_hotmail.com] Sent: Tuesday, March 16, 2004 6:18 PM
To: oracle-l_at_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 :

no terminator found after TERMINATED and ENCLOSED field

The column contains a " (double quote) used to designate inches, such as 6", which I think sql loader is choking on. Is there a way I can handle this within sql loader ?

thanks very much.

OPTIONS (SKIP=1)
LOAD DATA
infile 'csv/12292003-AHPOLNTMP.csv'
INTO TABLE sdusr3.ahpolntmp
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

)

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_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


-----------------------------------------------------------------

Received on Wed Mar 17 2004 - 08:39:19 CST