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)
3 ROWS SAMPLE OF CSV FILE
2012,19048,152,286690,compass_bank,3805_a1a_south,saint_augustine,st_johns,fl,32080,brcenm,consold,11,33_317,0,los_angeles_long_beach_glendale_ca,saint_augustine_2,united_states,109,300,27260,1,9_17_97,6_30_86,29_826612,81_272145,m,florida,12,mult,1391237,4,commercial_lending,banco_bilbao_vizcaya_argentaria_s_a,bilbao,al,697633,0_5,15_south_20th_street,birmingham,al_2,35233,65_982_103,sm,call,state,state_agency,state_2,13,united_states_2,0_6,48_097_626,48_313_018,10256,escrow,5,atlanta,6,atlanta_2,dif,cb,insbrdd,insbrts,5_2,southwest_district,fed,1073,alabama,1_2
2012,19048,660,257606,Compass Bank,3809 East 42nd Street,Odessa,Ector,TX,79762,M,,11,28063,FALSE,Chicago-Naperville-Arlington Heights IL,Odessa,United States,135,372,36220,TRUE,3/13/2008,1/4/1993,31.891448,-102.343769,M,Texas,48,MULT,1391237,4,COMMERCIAL LENDING,BANCO BILBAO VIZCAYA ARGENTARIA S.A.,BILBAO,AL,697633,FALSE,15 South 20th Street,Birmingham,AL,35233,65982103,SM,CALL,STATE,State Agency,STATE,13,United States,FALSE,48097626,48313018,10256,,5,Atlanta,6,Atlanta,DIF,CB,,,5,Southwest District,FED,1073,ALABAMA,TRUE
2012,19048,620,196395,Compass Bank,3810 Hwy 365,Port Arthur,Jefferson,TX,77642,C,956,23,0,FALSE,,Port Arthur,United States,245,0,13140,TRUE,3/13/2008,3/8/1993,29.915512,-93.877536,M,Texas,48,MULT,1391237,4,COMMERCIAL LENDING,BANCO BILBAO VIZCAYA ARGENTARIA S.A.,BILBAO,AL,697633,FALSE,15 South 20th Street,Birmingham,AL,35233,65982103,SM,CALL,STATE,State Agency,STATE,13,United States,FALSE,48097626,48313018,10256,,5,Atlanta,6,Atlanta,DIF,CB,,,5,Southwest District,FED,1073,ALABAMA,TRUE
--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)*
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.