Hi All,
Trying to use sql loader in order to laod csv format data. However, during load, I come across with errors.
Please find below my log,control and datafile.
------------Ctl File----------------
load data
discardfile 'c:\dis.log'
infile 'c:\csv\personal1.csv'
into table personal_expenses
fields terminated by ","
trailing nullcols
(PURCHASE_DATE date 'mm/dd/yy',PAYEE,RUPEE_SPENT,CATEGORY,SUB_CATEGORY,DESCRIPTION)
------------- Log FIle---------------------------
SQL*Loader: Release 10.1.0.2.0 - Production on Tue Aug 11 12:29:07 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Control File: C:\control01.ctl.txt
There are 2 data files:
Data File: control01.ctl
Bad File: C:\control01.bad
Discard File: c:\dis.log
(Allow all discards)
Data File: c:\csv\personal.csv
Bad File: C:\personal.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 PERSONAL_EXPENSES, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
PURCHASE_DATE FIRST * , DATE mm/dd/yyyy
PAYEE NEXT * , CHARACTER
RUPEE_SPENT NEXT * , CHARACTER
CATEGORY NEXT * , CHARACTER
SUB_CATEGORY NEXT * , CHARACTER
DESCRIPTION NEXT * , CHARACTER
Data File control01.ctl -
Record 1: Discarded - all columns null.
Record 3: Discarded - all columns null.
Record 5: Discarded - all columns null.
Record 2: Rejected - Error on table PERSONAL_EXPENSES, column PURCHASE_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 4: Rejected - Error on table PERSONAL_EXPENSES, column PURCHASE_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 6: Rejected - Error on table PERSONAL_EXPENSES, column PURCHASE_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 7: Rejected - Error on table PERSONAL_EXPENSES, column PURCHASE_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 8: Rejected - Error on table PERSONAL_EXPENSES, column PURCHASE_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 9: Rejected - Error on table PERSONAL_EXPENSES, column PURCHASE_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected
Data File c:\csv\personal.csv -
Record 10: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 11: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 12: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 13: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 14: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 15: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 16: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 17: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 18: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 19: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 20: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 21: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 22: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 23: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 24: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 25: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 26: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 27: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 28: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 29: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 30: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 31: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 32: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 33: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 34: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 35: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 36: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 37: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 38: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 39: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 40: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 41: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 42: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 43: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 44: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 45: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 46: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 47: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 48: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 49: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 50: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 51: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 52: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 53: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
Record 54: Rejected - Error on table PERSONAL_EXPENSES, column RUPEE_SPENT.
ORA-01722: invalid number
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table PERSONAL_EXPENSES:
0 Rows successfully loaded.
51 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
3 Rows not loaded because all fields were null.
Space allocated for bind array: 99072 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 73
Total logical records rejected: 51
Total logical records discarded: 3
Run began on Tue Aug 11 12:29:07 2009
Run ended on Tue Aug 11 12:29:08 2009
Elapsed time was: 00:00:00.55
CPU time was: 00:00:00.06
------------------------- Sample Data-----------------
04/05/09,Groceries,Withdrawal,83,Food,Groceries
05/05/09,Other,Withdrawal,50,Homeneeds,Other,,given to lali,
02/05/09,Groceries,Withdrawal,60,Food,Groceries,,Ice cream for all ,
05/05/09,Physcian,Withdrawal,400,Healthcare,Physician,,Consult to DR.ABBAS,
05/08/09,Dining Out,Withdrawal,35,Food,Dining out,,Juices,Ice cream,
05/07/09,Health,Withdrawal,120,Healthcare,Health,,Vasline Lotion,Cap Bcomplex,
05/10/09,clothing,Withdrawal,150,Homeneeds,Clothing,,Baby suit bought for Rupesh kid,
05/11/09,Physcian,Withdrawal,1500,Healthcare,Physician,,Ayurvedic Medicine from ABBASUsha,Me and Sonu,
05/11/09,Groceries,Withdrawal,55,Food,Groceries,,milk 1|2 kg, curd 1|2kg, sweet ,
05/17/09,Dining Out,Withdrawal,200,Food,Dining out,,me, sonu had dining out in bite-inkulfi,maze, special thali,
cold
05/17/09,Petorl,Withdrawal,300,Petrol,,,Petrol for CAR,
05/18/09,Health,Withdrawal,1500,Healthcare,Health,,Test : Ultrasound, Blood,
05/18/09,Physcian,Withdrawal,200,Healthcare,Physician,,ENT visit : Me,Poonam,
05/18/09,Health,Withdrawal,350,Healthcare,Health,,Medicine :-,
05/18/09,Dining Out,Withdrawal,165,Food,Dining out,,Dosa, lassi ,cold drinl ,shikanji , kulfi,
05/18/09,clothing,Withdrawal,730,Homeneeds,Clothing,,2 t shirt, 1lower , Braw , 1 purse
bindi , cluture , rubber bandlower Alok
05/21/09,Health,Withdrawal,325,Healthcare,Health,,Medicine bought for poona75+50.
Medicine start date is 21-may-09 for truboclosis,
05/21/09,Other,Withdrawal,100,Homeneeds,Other,,Rikshaw rent,
05/26/09,Internet,Withdrawal,550,Bills,Internet,,Gave sonu - 26-May-09,
05/30/09,Physcian,Withdrawal,200,Healthcare,Physician,,Dr. Sulabh Gupta consultation,
05/30/09,Groceries,Withdrawal,90,Food,Groceries,,Sweet ,
05/30/09,Party,Withdrawal,165,Leisure,Party,,Mc puff, Burger , Coke ,
05/30/09,Office food,Withdrawal,55,Food,Office food,,Lunch , Tea , Milk Bottel,
06/01/09,Medicine Alopathic,Withdrawal,538,Healthcare,Medicine Alopathic,,Poonam medicine + dendruf shampoo,
06/01/09,Fruits,Withdrawal,55,Food,Fruits,,Fruit juices + wayer bottel,
06/01/09,Gifts,Withdrawal,80,Gifts,,,sharpner, eraser,
06/01/09,Fruits,Withdrawal,55,Food,Fruits,,For poonam' home,
06/01/09,Dining Out,Withdrawal,50,Food,Dining out,,coke , curd , lays chipps,
06/04/09,Maintainance,Withdrawal,800,Maintainance,,,Bike Maintanence,
06/10/09,Medicine Alopathic,Withdrawal,250,Healthcare,Medicine Alopathic,,Medicine Sonu -- Dr Yogmaya.
06/10/09,Medicine Alopathic,Withdrawal,300,Healthcare,Medicine Alopathic,,Sonu medicine - Dr Salabh Gupta,
06/15/09,Medicine Alopathic,Withdrawal,570,Healthcare,Medicine Alopathic,,Medicine Poonam ,
06/22/09,Medicine Ayurvedic,Withdrawal,400,Healthcare,Medicine Ayurvedic,,Dr. Abbas,
06/22/09,Movies,Withdrawal,160,Leisure,Movies,,Movie -paying guest,
06/25/09,Dining Out,Withdrawal,150,Food,Dining out,,Chinees Combo+1 colddrink,
06/25/09,Internet,Withdrawal,550,Bills,Internet,,Internet payment,
06/29/09,Medical Test,Withdrawal,320,Healthcare,Medical Tests,,Blood Test :- Sonu - RA FACTOR,
06/29/09,Dining Out,Withdrawal,200,Food,Dining out,,Rasoi:- Special thali , cold drinks,
07/02/09,B'day,Withdrawal,1000,Leisure,B'day,,Mom's B'day,
07/01/09,Medicine Ayurvedic,Withdrawal,590,Healthcare,Medicine Ayurvedic,,Mine: Abbas,
07/02/09,B'day,Withdrawal,300,Leisure,B'day,,Afzal,
07/05/09,B'day,Withdrawal,1000,Leisure,B'day,,Amit B'day,
07/08/09,Travel,Withdrawal,2000,Vacation,Travel,,Trian tickets:-,
07/08/09,Dining Out,Withdrawal,1000,Food,Dining out,,During travelling to Gwalior,
07/16/09,Medicine Ayurvedic,Withdrawal,1000,Healthcare,Medicine Ayurvedic,,Dr. Abbas : Medicine Ayurvedic,
07/16/09,Petorl,Withdrawal,100,Petrol,,,For Bike,
07/17/09,Party,Withdrawal,290,Leisure,Party,,Office party : Dad earned golf medal.,
07/19/09,Other,Withdrawal,64,Homeneeds,Other,,Mother's in law--
07/20/09,Movies,Withdrawal,200,Leisure,Movies,,200 :- New York,
07/21/09,Dining Out,Withdrawal,200,Food,Dining out,,For movie,
07/21/09,clothing,Withdrawal,1700,Homeneeds,Clothing,,Sonu: 3 kurtas , one shirt usha,
07/23/09,Medicine Alopathic,Withdrawal,290,Healthcare,Medicine Alopathic,,Sonu's Medicine 10 days,
08/01/09,clothing,Withdrawal,200,Homeneeds,Clothing,,sonu leg-inns,
08/01/09,Give & take,Withdrawal,200,Bills,Give & Take,,sheetal for dress,
08/01/09,Give & take,Withdrawal,100,Bills,Give & Take,,given meenu,
08/02/09,Medicine Ayurvedic,Withdrawal,1400,Healthcare,Medicine Ayurvedic,,My Medicine from Dr Abbas,
08/03/09,Medicine Alopathic,Withdrawal,300,Healthcare,Medicine Alopathic,,Sonu's Medicine,
08/03/09,clothing,Withdrawal,100,Homeneeds,Clothing,,Stiching,
08/03/09,Cosmetics,Withdrawal,480,Homeneeds,Cosmetics,,Dio, Lipistick Nail color tec,
08/03/09,Outdoor foods,Withdrawal,100,Food,Outdoor foods,,lassi ras mala golgappe shikangi,
08/03/09,clothing,Withdrawal,60,Homeneeds,Clothing,,Peticote,
08/03/09,Give & take,Withdrawal,200,Bills,Give & Take,,given sheetal,
08/03/09,clothing,Withdrawal,150,Homeneeds,Clothing,,leg-inns for sonu,
08/06/09,Festivals,Withdrawal,1500,Leisure,Festivals,,1500 Rakshabandhan,
08/10/09,Dining Out,Withdrawal,150,Food,Dining out,,Bikaner-chineese plater-cold drink,
08/10/09,Medicine Ayurvedic,Withdrawal,150,Healthcare,Medicine Ayurvedic,,Sonu's Archiritis Medicine,
08/10/09,Give & take,Withdrawal,1000,Bills,Give & Take,,Komal's commettie,
08/19/09,clothing,Withdrawal,350,Homeneeds,Clothing,,Sonu's kurta,
08/20/09,Medicine Alopathic,Withdrawal,360,Healthcare,Medicine Alopathic,,Sonu's Medicine prscbd dr Alok,
08/20/09,Physcian,Withdrawal,200,Healthcare,Physician,,Ortopedic fee,
08/20/09,Dining Out,Withdrawal,75,Food,Dining out,,Burger carried from office,
08/20/09,other expences,Withdrawal,300,Vacation,other expences,,Return file fee,
08/20/09,Office food,Withdrawal,55,Food,Office food,,contrey rs 20 food rs 35,
hare krishna