| How to load data to Oracle Database using sql * loader? [message #385378] |
Mon, 09 February 2009 06:01  |
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
|
Senior Member |
|
|
Dear All,
I have a flat file where in which the fiels are terminated by whitespace.Below is my control file for your kind
reference.But as mentioned in the below control file the line
fields are terminated by '' it means that spaces are there in
my flat file.But what i need to mention if the fields are
terminated by # or ||.What happened is my source data will be
getting loaded into a folder for day one the fields are
terminated by comma,day2 the fields are terminated by # and
day3 it is terminated by comma.IS there any option so that i can
mention in the control file all these three delimiters can be
mentioned.My concern is the data should load to the database
if the delimiter is anything.
OPTIONS (ROWS=1000, readsize=12582912, BINDSIZE=12582912, SILENT=FEEDBACK,DISCARDS)
LOAD DATA
infile 'C:\BULK\LOAD.TXT'
badfile 'C:\BULK\XYZ.BAD'
DISCARDFILE 'C:\BULK\XYZ.DSC'
INSERT INTO TABLE TEST
FIELDS TERMINATED BY ''
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(a,
b,
c,
d,
e,
f,
g,
h,
i,
j,
k,
l)
Thanks and Regards,
Hammer.
|
|
|
|
|
|
|
|
| Re: How to load data to Oracle Database using sql * loader? [message #385396 is a reply to message #385389] |
Mon, 09 February 2009 06:50  |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
As I said you can dynamically create your control file.
Something like:
cat >myctl.ctl <<EOF
OPTIONS (ROWS=1000, readsize=12582912, BINDSIZE=12582912, SILENT=FEEDBACK,DISCARDS)
LOAD DATA
infile '$1'
badfile 'C:\BULK\XYZ.BAD'
DISCARDFILE 'C:\BULK\XYZ.DSC'
INSERT INTO TABLE TEST
FIELDS TERMINATED BY '$2'
OPTIONALLY ENCLOSED BY '$3' TRAILING NULLCOLS
(a,
b,
c,
d,
e,
f,
g,
h,
i,
j,
k,
l)
EOF
sqlldr myuser/mypsw control=myctl.ctl ...
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
