-
hi guys,
I have a question regarding the sqlldr in oracle. It is about the control file. I have a input .csv file, and I would like to load the csv column 'COUPON' to two of my database columns, let's say 'INTEREST_RATE' and 'COUPON'. is it possible to do that in sqlldr? How should the control file look like? Please take a look at what I have here.
my input file: 'data190.csv'
----------------------------------------------
DEAL_NUM, LOAN_NUM, COUPON
MyTest,100,8.7
MyTest2,201, 9.3
MY control file
---------------------
Code:
LOAD DATA
INFILE 'data190.csv'
REPLACE
INTO TABLE LOAN_TEMP
fields terminated by ',' TRAILING NULLCOLS
(
DEAL_NUM CHAR NULLIF DEAL_NUM=BLANKS,
LOAN_NUM INTEGER EXTERNAL NULLIF LOAN_NUM=BLANKS,
COUPON DECIMAL EXTERNAL NULLIF LOAN_WAC=BLANKS,
INTEREST_RATE DECIMAL EXTERNAL NULLIF LOAN_WAC=BLANKS
)
In the data file, I have 3 columns but in the control file I have 4 columns, so I don't think the control file above will work. Anyone has an idea on how to handle this kind of problem?
Thanks.
-
First install the delimited package( code is below ), you can then load whatever columns you want. The below control file would load columns 1 and 4 of the input data, skipping columns 2 and 3. The way this works is that FIELD1 is mapped to the entire INPUT record (postion 1-4096 or whatever your max record may be). We send field1 down to the delimited.word subroutine for EVERY column. The delimited.word routine compares the string it was called with against the last string it parsed and if they differ -- delimited.word parses the string and caches the results .
create or replace package delimited
as
function word( p_str in varchar2,
p_n in varchar2,
p_enclosed_by in varchar2 default '''',
p_separated_by in varchar2 default ',' )
return varchar2;
pragma restrict_references( word, WNDS, RNDS );
end;
/
create or replace package body delimited
as
type vcArray is table of varchar2(2000) index by binary_integer;
g_words vcArray;
g_empty vcArray;
g_last_string varchar2(4096);
function de_quote( p_str in varchar2, p_enc_by in varchar2 ) return varchar2
is
begin
return replace( ltrim( rtrim( p_str, p_enc_by ), p_enc_by ),
p_enc_by||p_enc_by, p_enc_by );
end de_quote;
procedure parse( p_str in varchar2,
p_delim in varchar2,
p_sep in varchar2 )
is
l_n number default 1;
l_in_quote boolean default FALSE;
l_ch char(1);
l_len number default nvl(length( p_str ),0);
begin
if ( l_len = 0 ) then
return;
end if;
g_words := g_empty;
g_words(1) := NULL;
for i in 1 .. l_len loop
l_ch := substr( p_str, i, 1 );
if ( l_ch = p_delim ) then
l_in_quote := NOT l_in_quote;
end if;
if ( l_ch = p_sep AND NOT l_in_quote ) then
l_n := l_n + 1;
g_words(l_n) := NULL;
else
g_words(l_n) := g_words(l_n)||l_ch;
end if;
end loop;
for i in 1 .. l_n loop
g_words(i) := de_quote( g_words(i), p_delim );
end loop;
end parse;
function word( p_str in varchar2,
p_n in varchar2,
p_enclosed_by in varchar2 default '''',
p_separated_by in varchar2 default ',' ) return varchar2
is
begin
if ( g_last_string is NULL or p_str <> g_last_string ) then
g_last_string := p_str;
parse( p_str, p_enclosed_by, p_separated_by );
end if;
return g_words( p_n );
exception
when no_data_found then return NULL;
end;
end delimited;
---------
LOAD DATA
INFILE *
REPLACE
INTO TABLE DELIMITED_TEST
(
FIELD1 position(1:4096) "delimited.word(:field1,1,chr(34),chr(44))",
FIELD2 position(1:1) "delimited.word(:field1,4,chr(34),chr(44))"
)
BEGINDATA
John Jones,"Elm St",Junk,123 Main Street
Jeff Boehlert,abcdef," 5555","po box, 1848, abc"
