Avatar of Alaska Cowboy
Alaska Cowboy
Flag for United States of America asked on

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

Open in new window

Oracle Database

Avatar of undefined
Last Comment
Alaska Cowboy

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
MikeOM_DBA

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
no worries :-) no nothing

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Alaska Cowboy

ASKER
Mike OM, ok, thanks, will try later tonight

George,ok thanks as well


maybe that will solve my 2nd issue as well . . .
Alaska Cowboy

ASKER
MikeOM, worked !

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes