Home » RDBMS Server » Server Utilities » sqlldr data issue
| sqlldr data issue [message #120085] |
Tue, 17 May 2005 13:48  |
whited05
Messages: 10
Registered: May 2005
Location: Lansdale, PA
|
Junior Member |
|
|
hello all I have the following file
H200504290330392005042900000020050429000000600739036000481offer_data_v153-00_200505.dat MKT CSM
D200505,123456,1001,2,INS12345,500000,
D200505,223456,1001,3,RES10000,500000,
D200505,323456,1002,4,EVS10001,500000,
D200505,423456,1001,8,FIS33334,500000,
D200505,523456,1002,2,INS12345,500000,
D200505,623456,1001,3,RES10020,500000,
D200505,723456,1002,4,EVS10001,500000,
D200505,823456,1001,8,FIS33335,500000,
D200505,923456,1002,2,INS12345,500000,
D200505,113456,1001,3,RES10010,500000,
D200505,143456,1002,4,EVS10001,500000,
TD00000001100010001000000000000000000000000000000000000000000000000000000000000000000000000000
I have 2 issues the first field of the file is actually a date field. I do not need the firct byte (the "D") the second issue is that is there a way to skip the last line of the file?
Thanks in advance!!
Dan
here is my current ctl file
INFILE 'Sample_OFFER_INVENTORY_YYYYMM.dat'
APPEND
INTO TABLE ofr_cpnt_inV
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(oput_prd_dt date "YYYYMM",
offer_id,
prnt_fcly_id,
media_id,
ofr_cpnt_id,
cpnt_inv_qt,
cre_ts sysdate)
|
|
|
|
|
|
| Re: sqlldr data issue [message #120090 is a reply to message #120086] |
Tue, 17 May 2005 14:21  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
To answer the first,Use functions
bash-2.03$ cat sample.dat
D200505,123456
D200505,223456
D200505,323456
D200505,423456
D200505,523456
D200505,623456
D200505,723456
D200505,823456
D200505,923456
D200505,113456
D200505,143456
bash-2.03$
bash-2.03$ cat sample.ctl
load data
infile 'sample.dat'
replace into table sample fields terminated by ','
(
somedate "to_date(substr(:somedate,2,length(:somedate)),'YYYYMM')" ,
someid
)
bash-2.03$ desc mutation scott.sample
Table:scott.sample
Name Null? Type
----------------------------------- -------- ------------------------
SOMEDATE DATE
SOMEID NUMBER
bash-2.03$ sqlldr userid=scott/tiger control=sample.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Tue May 17 14:15:10 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 11
bash-2.03$ query mutation scott.sample
SOMEDATE SOMEID
--------- ----------
01-MAY-05 123456
01-MAY-05 223456
01-MAY-05 323456
01-MAY-05 423456
01-MAY-05 523456
01-MAY-05 623456
01-MAY-05 723456
01-MAY-05 823456
01-MAY-05 923456
01-MAY-05 113456
01-MAY-05 143456
11 rows selected.
bash-2.03$
|
|
|
|
| Re: sqlldr data issue [message #120092 is a reply to message #120090] |
Tue, 17 May 2005 14:32 |
whited05
Messages: 10
Registered: May 2005
Location: Lansdale, PA
|
Junior Member |
|
|
Mahesh,
Thanks for the reply, I tried you example and it works great except for one issue
LOAD DATA
INFILE 'Sample_OFFER_INVENTORY_YYYYMM.dat'
replace
INTO TABLE ofr_cpnt_inV
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(oput_prd_dt "to_date(substr(:oput_prd_dt,2,6),'YYYYMM')" ,
offer_id,
prnt_fcly_id,
media_id,
ofr_cpnt_id,
cpnt_inv_qt,
cre_ts sysdate)
SQL> SELECT oput_prd_dt
2 FROM ofr_cpnt_inv;
OPUT_PRD_
---------
01-MAY-50
01-MAY-50
01-MAY-50
01-MAY-50
01-MAY-50
01-MAY-50
01-MAY-50
01-MAY-50
01-MAY-50
01-MAY-50
01-MAY-50
11 rows selected.
SQL>
| Mahesh Rajendran wrote on Tue, 17 May 2005 15:21 |
To answer the first,Use functions
bash-2.03$ cat sample.dat
D200505,123456
D200505,223456
D200505,323456
D200505,423456
D200505,523456
D200505,623456
D200505,723456
D200505,823456
D200505,923456
D200505,113456
D200505,143456
bash-2.03$
bash-2.03$ cat sample.ctl
load data
infile 'sample.dat'
replace into table sample fields terminated by ','
(
somedate "to_date(substr(:somedate,2,length(:somedate)),'YYYYMM')" ,
someid
)
bash-2.03$ desc mutation scott.sample
Table:scott.sample
Name Null? Type
----------------------------------- -------- ------------------------
SOMEDATE DATE
SOMEID NUMBER
bash-2.03$ sqlldr userid=scott/tiger control=sample.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Tue May 17 14:15:10 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 11
bash-2.03$ query mutation scott.sample
SOMEDATE SOMEID
--------- ----------
01-MAY-05 123456
01-MAY-05 223456
01-MAY-05 323456
01-MAY-05 423456
01-MAY-05 523456
01-MAY-05 623456
01-MAY-05 723456
01-MAY-05 823456
01-MAY-05 923456
01-MAY-05 113456
01-MAY-05 143456
11 rows selected.
bash-2.03$
|
|
|
|
|
| Re: sqlldr data issue [message #120100 is a reply to message #120092] |
Tue, 17 May 2005 15:08 |
|
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>> except for one issue
what issue?
If you are mentioning 01-MAY-50, that YYYY format is wrong,
Then there might be some difference in your environment.
Check the input datafiles.
CHeck the NLS_DATE_FORMAT for the sql*plus session.
|
|
|
|
Goto Forum:
Current Time: Mon Feb 03 12:30:48 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
