| loading data from more than 1 .TXT files using one control file..? [message #72165] |
Fri, 28 March 2003 10:31  |
Sabrina
Messages: 76
Registered: February 2002
|
Member |
|
|
My table looks like,
SQL> desc icd_9_diagnosis_codes_hist;
Name Null? Type
------------------------------- -------- ----
DX_CODE NOT NULL VARCHAR2(50)
SHORT_DESC VARCHAR2(50)
MED_DESC VARCHAR2(50)
LONG_DESC VARCHAR2(50)
I have data coming from more than one source (.TXT) files.. e.g. icd9a01.txt, icd9b01.txt, icd9c01.txt.
The format of all these .TXT files is similar.
e.g.
My icd9a01.TXT file looks like, (comma delimited, and contains the data for columns DX_CODE and SHORT_DESC)
"001 ","CHOLERA"
"001.0 ","CHOLERA DUE TO VIBRIO CHOLERAE"
My icd9b01.TXT file looks like, (comma delimited, and contains the data for columns DX_CODE and SHORT_DESC)
"390 ","RHEUMATIC FEVER WITHOUT MENTION OF HEART INVOLVEMENT"
"391 ","RHEUMATIC FEVER WITH HEART INVOLVEMENT"
"391.0 ","ACUTE RHEUMATIC PERICARDITIS"
My .CTL file (RIGHT NOW)looks like,
Load data infile icd9a01.txt
Into table ICD_9_DIAGNOSIS_CODES_HIST append
(
DX_CODE POSITION(1-50) TERMINATED BY , ENCLOSED BY ,
SHORT_DESC POSITION(51-100) TERMINATED BY , ENCLOSED BY ,
DX_CODE POSITION(101-151) TERMINATED BY , ENCLOSED BY ,
DX_CODE POSITION(151-200) TERMINATED BY , ENCLOSED BY ,
)
And the icd_9_2001.SH file RIGHT NOW LOOKS LIKE,
rm icd_9_2001.log
rm icd_9_2001.bad
sqlldr userid=test1/test control-icd_9_2001.ctl bad=icd_9_2001.bad log=icd_9_2001.log
My .CTL and .SH files right now are only loading data from one file i.e. icd9a01.TXT file
How do I change it so that one .CTL and one .SH and one .LOG file would load data from all these .TXT files. So that I dont have to write 4 control files? Should I just put commas
e.g. in my .CTL file, should I make the following changes..
Load data infile icd9a01.txt,icd9b01.txt,icd9c01.txt
Into table ICD_9_DIAGNOSIS_CODES_HIST append
(
DX_CODE POSITION(1-50) TERMINATED BY , ENCLOSED BY ,
SHORT_DESC POSITION(51-100) TERMINATED BY , ENCLOSED BY ,
DX_CODE POSITION(101-151) TERMINATED BY , ENCLOSED BY ,
DX_CODE POSITION(151-200) TERMINATED BY , ENCLOSED BY ,
)
Thank you.
|
|
|
|
| Re: loading data from more than 1 .TXT files using one control file..? [message #72166 is a reply to message #72165] |
Fri, 28 March 2003 11:17  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
use infile clause like this
C:>cat test.ctl
LOAD DATA
INFILE 'test.dat'
infile 'test1.dat'
append
iNTO table test262
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
DX_CODE,
SHORT_DESC,
MED_DESC,
LONG_DESC
)
C:>count test262
Table: test262
COUNT(*)
----------
0
C:>sqlldr userid=mag/mag control=test.ctl
SQL*Loader: Release 8.1.6.0.0 - Production on Fri Mar 28 13:03:58 2003
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Commit point reached - logical record count 11
Commit point reached - logical record count 23
C:>count test262
Table: test262
COUNT(*)
----------
22
C:>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
