You Asked
I've been struggling with a dataload from an outside datasource and have to get the previous 2 years worth of data files loaded by Christmas. Normally that wouldn't be a problem because once I figure out my initial load process, there are only minor tweaks from there. I fear though, that I'm missing something obvious or am going about solving this problem the wrong way. I have been unable to get my initail load to work. First the first 5 lines of the data file:
074851 ,KEYS OF KINGDOM (INTERLUDES) ,$10.00, 1 , $5.00
073448 ,"GOSPEL PIANIST, THE ",$13.00, - , $-
088100 ,KEYS OF KINGDOM (COMMUNION)) ,$10.00, - , $-
018781 ,KEYS OF KINGDOM (CHRISTMAS) ,$10.00, 3 , $15.00
018773 ,KEYS OF KINGDOM (GENERAL) ,$10.00, - , $-
They are sending us a list of every product of ours they sell, regardless of if they sold any of that product this month(?!?). It appears as if ' - ' means 0 quantity sold. Tom, I do dataloads all the time and have just finished my first Oracle 11G class, but this one is kicking my tail. Here is my control file I've written(still doesn't work yet, and as you can see, I've been working on this one as I have time for almost a week)
-- Written by Craig Buchanan 12/03/10
Load DATA
INFILE '/xfers/oracle/dw/data/presser_sales.dat'
BADFILE '/xfers/oracle/dw/data/presser_sales.bad'
APPEND INTO TABLE STAGE.PRESSER_SALES
WHEN (ITEM_QTY != '0')
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(ITEM_NUM CHAR
"TRIM(:ITEM_NUM)",
TITLE CHAR
"TRIM(:TITLE)",
LIST_PRICE CHAR
"NVL(translate(TRIM(:LIST_PRICE), '0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!#$%^&*()_+=-`{}[]|\?><,/''','0123456789.'),0)",
ITEM_QTY CHAR
"NVL(translate(TRIM(:ITEM_QTY), '0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!#$%^&*()_+=-`{}[]|\?><,/''','0123456789.'),0)",
PAYMENT_AMOUNT CHAR
"NVL(translate(TRIM(:PAYMENT_AMOUNT), '0123456789.-ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz~!#$%^&*()_+=`{}[]|\?><,/''','0123456789.-'),0)",
INSERT_USER CONSTANT "SYSTEM",
INSERT_USER_DATE SYSDATE,
INSERT_STATUS_CODE CONSTANT "I")
...and here is my table I need to load it into...
CREATE TABLE presser_sales
(presser_sales_id NUMBER ,
item_num VARCHAR2(20 BYTE),
title VARCHAR2(200 BYTE),
list_price NUMBER,
item_qty NUMBER,
payment_amount NUMBER,
insert_user VARCHAR2(20 BYTE),
insert_user_date DATE,
insert_status_code CHAR(1 BYTE))
/
This seems so simple, just a few fields, map em, move on with your life. If my boss would let me load everything into STAGE as Varchar2, I would handle data cleanup on my transfer to our PROD table, but he has drawn a line in the sand on this. So I'm trying to handle ugly data that I have no control over, being passed across to me in wrong formats. I'd really apreciate you talking me through the thought process of a solution that I could use. Thanks for your time.
and Tom said...
I think it was all mostly due to "when (item_qty!='0')", you meant to say "-".
I didn't like your functions for the numbers, I just stripped the $ and to_numbered it.
ops$tkyte%ORA11GR2> !cat test.ctl
Load DATA
INFILE *
REPLACE
INTO TABLE PRESSER_SALES
WHEN (ITEM_QTY != '-')
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(ITEM_NUM CHAR,
TITLE CHAR "TRIM(:TITLE)",
LIST_PRICE CHAR "to_number(substr(:list_price,2))",
ITEM_QTY CHAR,
PAYMENT_AMOUNT CHAR "to_number(substr(:payment_amount,2))",
INSERT_USER CONSTANT "SYSTEM",
INSERT_USER_DATE SYSDATE,
INSERT_STATUS_CODE CONSTANT "I"
)
begindata
074851 ,KEYS OF KINGDOM (INTERLUDES) ,$10.00, 1 , $5.00
073448 ,"GOSPEL PIANIST, THE ",$13.00, - , $-
088100 ,KEYS OF KINGDOM (COMMUNION)) ,$10.00, - , $-
018781 ,KEYS OF KINGDOM (CHRISTMAS) ,$10.00, 3 , $15.00
018773 ,KEYS OF KINGDOM (GENERAL) ,$10.00, - , $-
ops$tkyte%ORA11GR2> !sqlldr / test
SQL*Loader: Release 11.2.0.2.0 - Production on Thu Dec 9 13:34:36 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 5
ops$tkyte%ORA11GR2> select * from presser_sales;
PRESSER_SALES_ID ITEM_NUM TITLE LIST_PRICE ITEM_QTY PAYMENT_AMOUNT INSERT_USE INSERT_USER_DATE I
---------------- -------------------- ------------------------------ ---------- ---------- -------------- ---------- -------------------- -
074851 KEYS OF KINGDOM (INTERLUDES) 10 1 5 SYSTEM 09-dec-2010 13:34:36 I
018781 KEYS OF KINGDOM (CHRISTMAS) 10 3 15 SYSTEM 09-dec-2010 13:34:36 I
ops$tkyte%ORA11GR2> !cat test.log
SQL*Loader: Release 11.2.0.2.0 - Production on Thu Dec 9 13:34:36 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: test.ctl
Data File: test.ctl
Bad File: test.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table PRESSER_SALES, loaded when ITEM_QTY != 0X2d(character '-')
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ITEM_NUM FIRST * , O(") CHARACTER
TITLE NEXT * , O(") CHARACTER
SQL string for column : "TRIM(:TITLE)"
LIST_PRICE NEXT * , O(") CHARACTER
SQL string for column : "to_number(substr(trim(:list_price),2))"
ITEM_QTY NEXT * , O(") CHARACTER
PAYMENT_AMOUNT NEXT * , O(") CHARACTER
SQL string for column : "to_number(substr(trim(:payment_amount),2))"
INSERT_USER CONSTANT
Value is 'SYSTEM'
INSERT_USER_DATE SYSDATE
INSERT_STATUS_CODE CONSTANT
Value is 'I'
Record 2: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.
Record 5: Discarded - failed all WHEN clauses.
Table PRESSER_SALES:
2 Rows successfully loaded.
0 Rows not loaded due to data errors.
3 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 83136 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 0
Total logical records discarded: 3
Run began on Thu Dec 09 13:34:36 2010
Run ended on Thu Dec 09 13:34:36 2010
Elapsed time was: 00:00:00.04
CPU time was: 00:00:00.00
Rating
(8 ratings)
Is this answer out of date? If it is, please let us know via a Comment