sql * loader file not axtracting (DATE DATATYPE)-CodePudding

LOAD DATA
INFILE "C:\\Users\\lenovo T480\\Desktop\\all_2012.csv"
DISCARDFILE "C:\\Users\\lenovo T480\\Desktop\\BANKING_DATA_DISCARDED.txt"
TRUNCATE INTO TABLE BANKDTLS
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '""'
(year,BACH,ACODE,XNUM,BANK,location,AREA,HEAD_NAME,
CODETX,BALANCE,COL_PIVOT,CONSOLD,DEPTNO,DEBIT,STATUS,
SUB_ADDR,PROVINCE,CONTRY,COLNO1,COLNO2,CREDIT,ADD_STATUS,
DATE1,
DATE2,
INTEREST,TAX,GENDER,STATES,STATE_CODE,type,TYPE_CODE,LOAN_CODE,LOAN_TYPE)

--TABLE
Create table BANKDTLS
( year number(4),BACH number(25),ACODE number(30),XNUM number(26),
  BANK varchar2(70),location varchar2(70),AREA varchar2(50),HEAD_NAME varchar2(40),
  CODETX varchar(30),BALANCE varchar(20),COL_PIVOT varchar(25),CONSOLD varchar(20),
  DEPTNO varchar(30),DEBIT varchar(30),STATUS varchar(10), SUB_ADDR varchar2(50),
  PROVINCE   varchar2(50),CONTRY varchar2(50), COLNO1 varchar(40), COLNO2 varchar(40),
  CREDIT varchar(38),ADD_STATUS varchar2(46),
  DATE1 DATE,
  DATE2 DATE,
  INTEREST varchar2(30), TAX varchar(25), GENDER varchar(30), STATES varchar2(30),
  STATE_CODE varchar(30),type varchar2(36),TYPE_CODE varchar(30),
  LOAN_CODE varchar(35), LOAN_TYPE varchar2(50)
 );

--columns(date1,date2)
--TABLE AND COLUMNS CREATED IN ORACLE WITH DATE DATATYPE
--DATE FORMAT IN FLAT FILE (9/31/2012)

--/*value used for ROWS parameter changed from 250 to 123
Record 1: Rejected - Error on table BANKDTLS, column DATE1.
ORA-01843: not a valid month

Record 2: Rejected - Error on table BANKDTLS, column DATE1.
ORA-01843: not a valid month

Record 3: Rejected - Error on table BANKDTLS, column DATE1.
ORA-01843: not a valid month*/

HOW TO INSERT STRING DATA INTO DATE (DATATYPE) COLUMN USING SQLLOADER AND ALREADY TABLE CREATED WITH DATE(DATATYPE)*

CodePudding user response：

If you don't specify the source field datatype, SQLLoader reads everything as CHAR(255). It doesn't know how to stick a CHAR value into a DATE column unless the format just happens to match the database's (or session's) NLS_DATE_FORMAT parameter. Programmers should never rely on that. Instead, they should always specify the date format their data is in. For SQLLoader control files, you can do that in the field definition. For example:

. . .
(year,BACH,ACODE,XNUM,BANK,location,AREA,HEAD_NAME,
CODETX,BALANCE,COL_PIVOT,CONSOLD,DEPTNO,DEBIT,STATUS,
SUB_ADDR,PROVINCE,CONTRY,COLNO1,COLNO2,CREDIT,ADD_STATUS,
DATE1 DATE "YYYYMMHH24MISS",
DATE2 DATE "YYYYMMHH24MISS",
INTEREST,TAX,GENDER,STATES,STATE_CODE,type,TYPE_CODE,LOAN_CODE,LOAN_TYPE)

Change that to whatever format your date strings are in, and it should parse it correctly and then will be loading a date into a date.

CodePudding user response：

In the control file you need to also specify the types and format for the date:

...
DATE1 DATE "MM/DD/YYYY" NULLIF (DATE1=BLANKS),
DATE2 DATE "MM/DD/YYYY" NULLIF (DATE2=BLANKS),
...