Sql Loader, chopping off first three characters; date field errors

this should be dirt simple, but a couple of odd errors . . .

1. The first two positions of the first field are chopped off
2. If the first field is bracketed by double quotes, the date field at the end gets an error.


I removed all the commas from the first field and everything loaded ok, even though one of the other fields had commas (but enclosed in double-quotes),

/********** Data coming in **************/
Sample 1
"Actelion Pharmaceutical U.S., Inc",P1012,66215,Actelion Pharmaceuticals US Inc.,1/1/2011,1/1/2011

but this causes an error:
Record 16: Rejected - Error on table MEDPARTD.CGDP_RFR_MFTRR_LIST, column ADD_DT.
ORA-01858: a non-numeric character was found where a numeric was expected

(but if I would remove the double quotes on the first field and the comma in the field, it loads ok, so it's not really a date issue

Sample 2
Data in file
Affordable Pharmaceuticals LLC,P1109,10572,Affordable Pharmaceuticals LLC,1/1/2011,1/1/2011

Loaded (everything ok but drops first two positions of first field)
fordable Pharmaceuticals LLC, P1109, 10572, Affordable Pharmaceuticals LLC, 01-JAN-11, 01-JAN-11

Sample 3
Data in file (note the double quotes in the 4th field)
Abraxis BioScience LLC,P1158,68817,"Abraxis BioScience, LLC",1/1/2011,1/1/2011

Loaded ok
raxis BioScience LLC, P1158, 68817, Abraxis BioScience, LLC, 01-JAN-11, 01-JAN-11
- but can't figure out while double quotes surrounding the FIRST field doesn't work.


/********* Script ************/
-- This file is used to build the control file for Medicare Part D Manufacturers List
-- There are two parts to this control file
-- Part one is the infile, badfile info (designated in the UNIX script), built from the script.
        APPEND INTO TABLE medpartd.etl_file_load_det
        when (2:4) = 'HDR'
        (
        etl_load_file_id "medpartd.etl_file_load_det_seq.nextval",
        etl_file_nm constant 'MMR Load from CGI',
        etl_prcs_load_dt "sysdate"
        )
        INTO TABLE MEDPARTD.cgdp_rfr_mftrr_list
        fields terminated by ',' optionally enclosed by '"' trailing nullcols
        (
        mftrr_nm char,
        p_nbr char,
        lblr_cd char,
        lblr_cd_firm_nm char,
        add_dt date "mm/dd/yyyy",
        efft_dt date "mm/dd/yyyy",
        mftrr_list_skey "medpartd.mor_rpt_det_seq.nextval",
        aud_insrt_id "user",
        aud_srce_sys_cd constant '194',
        aud_insrt_tmstp "localtimestamp",
        etl_load_file_id "medpartd.etl_file_load_det_seq.currval"
        )

/*********** More sample data ***********/
Abraxis BioScience LLC,P1158,68817,"Abraxis BioScience, LLC",1/1/2011,1/1/2011
Acorda Therapeutics,P1208,10144,"Acorda Therapeutics, Inc.",1/1/2011,1/1/2011
Actavis Kadian LLC,P1069,67767,Actavis South Atlantic LLC,1/1/2011,1/1/2011
Actavis Kadian LLC,P1069,52152,Actavis Totowa LLC,1/1/2011,1/1/2011
Actavis Kadian LLC,P1069,46987,Actavis Kadian LLC,1/1/2011,1/1/2011
Actavis Kadian LLC,P1069,45963,Actavis Inc,1/1/2011,1/1/2011
Actavis Kadian LLC,P1069,14550,Actavis Pharma Manufacturing Private Ltd.,1/1/2011,1/1/2011
Actavis Kadian LLC,P1069,00472,Actavis Mid-Atlantic LLC,1/1/2011,1/1/2011
Actavis Kadian LLC,P1069,00228,Actavis Elizabeth LLC,1/1/2011,1/1/2011
"Actelion Pharmaceutical U.S., Inc",P1012,66215,Actelion Pharmaceuticals US Inc.,1/1/2011,1/1/2011
"Actient Pharmaceuticals, LLC",P1229,52244,"Actient Pharmaceticals, LLC",1/1/2011,1/1/2011
Affordable Pharmaceuticals LLC,P1109,10572,Affordable Pharmaceuticals LLC,1/1/2011,1/1/2011
"Akorn, Inc.",P1194,22360,"Akorn-Strides, Inc.",1/1/2011,1/1/2011
"Akorn, Inc.",P1194,17478,"Akorn, Inc.",1/1/2011,1/1/2011
"Akorn, Inc.",P1194,11098,Taylor Pharmaceuticals,1/1/2011,1/1/2011
"Akrimax Pharmaceuticals, LLC",P1151,24090,"Akrimax Pharmaceuticals, LLC",1/1/2011,1/1/2011
Alaven Pharmaceutical LLC,P1089,68220,Alaven Pharmaceutical LLC,1/1/2011,1/1/2011
"Alcon Laboratories, Inc.",P1026,42826,"Alcon Laboratories, Inc.",1/1/2011,1/1/2011

Select allOpen in new window