Table trying to get data to: CREATE TABLE REVENUE_FACT_TABLE
Maybe my Orcale table date format causing issue?
SQL Loader CSV to Oracle
I have 3 Tables total
my biggest problem is date format
Here the csv table for REVENUE_FACT_TABLE
| Customer_Key | Date | Item_Key | Amount | Quantity |
| 111 | 01/Jan/2004 | 1 | 1000 | 1 |
| 333 | 01/Jan/2004 | 2 | 300 | 1 |
| 222 | 01/Jan/2004 | 3 | 300 | 1 |
| 111 | 01/Jan/2004 | 4 | 20 | 1 |
| 333 | 01/Jan/2004 | 5 | 20 | 1 |
| 333 | 01/Jan/2004 | 6 | 3 | 1 |
| 111 | 01/Jan/2004 | 7 | 12 | 3 |
| 333 | 01/Jan/2004 | 8 | 9 | 1 |
| 333 | 01/Jan/2004 | 9 | 11 | 1 |
| 222 | 01/Jan/2004 | 10 | 9 | 1 |
| CC1 | 01/Jan/2004 | 11 | 3500 | 1 |
| CC2 | 05/Jan/2004 | 12 | 2200 | 1 |
Other two CSV at end
Here the ctl file
load data
infile 'Merge_2.csv' "str '\r\n'"
append
into table REVENUE_FACT_TABLE
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(Customer_Key CHAR,
REVENUE_DATE CHAR,
Item_Key CHAR,
Amount CHAR,
Quantity CHAR
)
Error log
SQL*Loader: Release 11.2.0.2.0 - Production on Tue Apr 11 01:09:36 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: merge_2.ctl
Data File: Merge_2.csv
File processing option string: "str '\r\n'"
Bad File: Merge_2.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 REVENUE_FACT_TABLE, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CUSTOMER_KEY FIRST * , O(") CHARACTER
REVENUE_DATE NEXT * , O(") CHARACTER
ITEM_KEY NEXT * , O(") CHARACTER
AMOUNT NEXT * , O(") CHARACTER
QUANTITY NEXT * , O(") CHARACTER
Record 14: Discarded - all columns null.
Record 1: Rejected - Error on table REVENUE_FACT_TABLE, column REVENUE_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 2: Rejected - Error on table REVENUE_FACT_TABLE.
ORA-00001: unique constraint (PROZEE.SYS_C007941) violated
Record 3: Rejected - Error on table REVENUE_FACT_TABLE.
ORA-00001: unique constraint (PROZEE.SYS_C007941) violated
Record 4: Rejected - Error on table REVENUE_FACT_TABLE.
ORA-00001: unique constraint (PROZEE.SYS_C007941) violated
Record 5: Rejected - Error on table REVENUE_FACT_TABLE.
ORA-00001: unique constraint (PROZEE.SYS_C007941) violated
Record 6: Rejected - Error on table REVENUE_FACT_TABLE.
ORA-00001: unique constraint (PROZEE.SYS_C007941) violated
Record 7: Rejected - Error on table REVENUE_FACT_TABLE.
ORA-00001: unique constraint (PROZEE.SYS_C007941) violated
Record 8: Rejected - Error on table REVENUE_FACT_TABLE.
ORA-00001: unique constraint (PROZEE.SYS_C007941) violated
Record 9: Rejected - Error on table REVENUE_FACT_TABLE.
ORA-00001: unique constraint (PROZEE.SYS_C007941) violated
Record 10: Rejected - Error on table REVENUE_FACT_TABLE.
ORA-00001: unique constraint (PROZEE.SYS_C007941) violated
Record 11: Rejected - Error on table REVENUE_FACT_TABLE.
ORA-00001: unique constraint (PROZEE.SYS_C007941) violated
Record 12: Rejected - Error on table REVENUE_FACT_TABLE.
ORA-00001: unique constraint (PROZEE.SYS_C007941) violated
Record 13: Rejected - Error on table REVENUE_FACT_TABLE.
ORA-00001: unique constraint (PROZEE.SYS_C007941) violated
Table REVENUE_FACT_TABLE:
0 Rows successfully loaded.
13 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
1 Row not loaded because all fields were null.
Space allocated for bind array: 82560 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 14
Total logical records rejected: 13
Total logical records discarded: 1
Run began on Tue Apr 11 01:09:36 2017
Run ended on Tue Apr 11 01:09:37 2017
Elapsed time was: 00:00:01.05
CPU time was: 00:00:00.03
Space allocated for bind array: 82560 bytes(64 rows)
Read buffer bytes: 1048576
Create Tables
CREATE TABLE ITEM
(
Item_Key NUMBER(5) NOT NULL,
Item_MshpId VARCHAR(5) NULL,
Item_ID VARCHAR(15) NULL,
Item_Category VARCHAR(25) NULL,
Item_Type VARCHAR(25) NULL,
PRIMARY KEY (Item_Key)
);
CREATE TABLE CUSTOMER
(
Customer_Key VARCHAR(15) NOT NULL,
CustomerFName VARCHAR(26) NULL,
CustomerLName VARCHAR(26) NULL,
Custcategory VARCHAR(26) NULL,
CustState CHAR(20) NULL,
CustZip VARCHAR(10) NULL,
PRIMARY KEY (Customer_Key)
);
CREATE TABLE REVENUE_FACT_TABLE
(
Customer_Key VARCHAR(15) NOT NULL,
REVENUE_DATE DATE NULL,
Item_Key NUMBER(5) NOT NULL,
Amount NUMBER(10,4) NULL,
Quantity INT NULL,
FOREIGN KEY (Item_Key) REFERENCES Item (Item_Key),
CONSTRAINT CUST_FK FOREIGN KEY (Customer_Key) REFERENCES CUSTOMER (Customer_Key),
PRIMARY KEY(Customer_Key,Item_Key)
);
CSV
ITEM table
| Item_Key | Item_MshpId | MshpName | Item_Category | Item_Type |
| 1 | M1 | Platnum | MEMBERSHIP |
| 2 | M2 | Gold | MEMBERSHIP |
| 3 | M3 | Value | MEMBERSHIP |
| 4 | PSA | Adult | ONE DAY PASS CATEGORY | 1-001 |
| 5 | PSS | Senior | ONE DAY PASS CATEGORY | 1-003 |
| 6 | PSK | Kid | ONE DAY PASS CATEGORY | 1-002 |
| 7 | EQ1 | Jump Rope | MERCHANDISE | 11113 |
| 8 | AP2 | Hat | MERCHANDISE | 11111 |
| 9 | AP1 | T-shirt | MERCHANDISE | 11111 |
| 10 | AP2 | Hat | MERCHANDISE | 11112 |
| 11 | L-A | All Day Rental | Event | |
| 12 | L-H | Half Day Rental | Event | |
Customer
| Customer_Key | CustomerFName | CustomerLName | CustState | CustZip |
| 111 | Joe | 50611 |
| 333 | Sue | 60611 |
| 222 | Mary | 50640 |
| CC1 | Sears | Chicago | 60640 |
| CC2 | Boeing | Chicago | 60611 |