urgent, sqlldr question....
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: urgent, sqlldr question....

  1. #1
    Join Date
    Jan 2001
    Posts
    59
    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.





  2. #2
    Join Date
    May 2000
    Posts
    58
    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"



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width