sql * loader file not axtracting (DATE DATATYPE)

Question

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)*

The columns in question have values `9_17_97,6_30_86`, this does not match any `DATE` format. — Wernfried Domscheit, Jan 12 '23 at 11:55
THE FIRST LINE IS LOOK LIKE THE HEADER CHECK 2ND LINE AS 3/13/2008,1/4/1993 — Jnanendra Y, Jan 13 '23 at 12:45

score 0 · Accepted Answer · answered Jan 02 '23 at 01:26

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.

"YYYYMMHH24MISS" I THINK IN THIS 'DD' IS MISSING? RIGHT IS IT NECESSARY TO MENTION '24' — Jnanendra Y, Jan 11 '23 at 15:06
you are right, you'll wnat to add DD somewhere. HH24 distinguishes between 24-hour and 12-hour formats. whatever your data needs. — Paul W, Jan 11 '23 at 15:14

score 0 · Answer 2 · answered Jan 03 '23 at 10:00

0

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),
...

answered Jan 03 '23 at 10:00

Gnqz

3,292
3
25
35

THANKS FOR THE SOLUTION ITS MORE CONVENIENT @Gnqz – Jnanendra Y Jan 11 '23 at 14:46

sql * loader file not axtracting (DATE DATATYPE)

2 Answers2