Home » RDBMS Server » Server Utilities » SQLLoader
SQLLoader [message #367928] Mon, 10 April 2000 09:09 Go to next message
L Peaslee
Messages: 1
Registered: April 2000
Junior Member
Is it possible to skip a column when using sqlloader
ex. 1, 2, 3, 4, 5 want to load 1, 3, 4, 5 into my table but not 2 and I am not using positional.

Also I was given a tab delimited file to load and partnumber is a field I am loading but the field partnumber has spaces after the last char and the tab. How do I handle this without loading the spaces?
Re: SQLLoader [message #367929 is a reply to message #367928] Tue, 11 April 2000 12:54 Go to previous messageGo to next message
hmg
Messages: 40
Registered: March 1999
Member
Hi,

here is my suggestion about your problem.

<TT>
----------------------------------------------------------------
-- function to extract specified columns from a line of text
----------------------------------------------------------------
create or replace function getcol(line         in varchar2,
                                  colnum       in number,
                                  delimiter    in char default ';',
                                  enclosed_by  in char default null,
                                  trim         in boolean default true,
                                  trimset      in varchar2 default chr(9) || chr(32) )
return varchar2
is
   retval   varchar2(2000);
   
   v_line   varchar2(2000);
   v_col    varchar2(2000);
   
   v_pos1   number(4);
   v_pos2   number(4);
begin
   -- make sure that the first and last character 
   -- of the line is a delimiter
   v_line := rtrim( line, delimiter ) || delimiter;
   v_line := delimiter || ltrim( v_line, delimiter );
    
   v_pos1 := instr( v_line, delimiter, 1, colnum );
   v_pos2 := instr( v_line, delimiter, 1, colnum + 1 );
   
   -- extract the column
   v_col  := substr( v_line, v_pos1 + 1, v_pos2 - (v_pos1 + 1) );
   
   -- delete the whitespace
   if trim = true then
      v_col := rtrim( v_col, trimset );
      v_col := ltrim( v_col, trimset );
   end if;
   
   -- delete the enclosing character
   if enclosed_by is not null then
      if substr(v_col,1,1) = enclosed_by 
            and substr(v_col,length(v_col),1) = enclosed_by
               and length(v_col) > 1         then
         v_col := substr( v_col, 1 + 1, length( v_col ) - (1 + 1) );
      end if;   
   end if;
   
   -- delete again the whitespace
   if trim = true then
      v_col := rtrim( v_col, trimset );
      v_col := ltrim( v_col, trimset );
   end if;

   return ( v_col );
end;
/
show errors
 
 
--------------------------------------------
-- plsql block for testing function getcol
--------------------------------------------
set serveroutput on
declare
   v_line varchar2(2000);
   v_col varchar2(2000);
begin
   v_line := 
   '7369,  "SMITH", "CLERK", 7902, "17-DEC-80", 800, , 20  ';
   
   -- empno
   v_col := getcol( v_line, 1, ',');
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- ename
   v_col := getcol( v_line, 2, ',', '"' );
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- job
   v_col := getcol( v_line, 3, ',', '"' );
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- mgr
   v_col := getcol( v_line, 4, ',' );
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- hiredate
   v_col := getcol( v_line, 5, ',', '"' );
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- sal
   v_col := getcol( v_line, 6, ',');
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- comm
   v_col := getcol( v_line, 7, ',');
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
   
   -- deptno
   v_col := getcol( v_line, 8, ',');
   dbms_output.put_line('col   : ' || v_col );
   dbms_output.put_line('length: ' || nvl(length(v_col),0) );
end;
/
   
  
----------------------------------
-- create test table for loading 
----------------------------------
drop table loadtest;
create table loadtest (
   empno    number(4),
   ename    varchar2(20),
   hiredate date,
   sal      number(18,2),
   comm      number(18,2)
);
  
  
------------------------------------------------------
-- contents from file "scott.txt"
------------------------------------------------------
7369,"SMITH","CLERK",7902,"17-DEC-80",800,,20
7499,"ALLEN","SALESMAN",7698,"20-FEB-81",1600,300,30
7521,"WARD","SALESMAN",7698,"22-FEB-81",1250,500,30
7566,"JONES","MANAGER",7839,"02-APR-81",2975,,20
7654,"MARTIN","SALESMAN",7698,"28-SEP-81",1250,1400,30
7698,"BLAKE","MANAGER",7839,"01-MAY-81",2850,,30
7782,"CLARK","MANAGER",7839,"09-JUN-81",2450,,10
7788,"SCOTT","ANALYST",7566,"19-APR-87",3000,,20
7839,"KING","PRESIDENT",,"17-NOV-81",5000,,10
7844,"TURNER","SALESMAN",7698,"08-SEP-81",1500,0,30
7876,"ADAMS","CLERK",7788,"23-MAY-87",1100,,20
7900,"JAMES","CLERK",7698,"03-DEC-81",950,,30
7902,"FORD","ANALYST",7566,"03-DEC-81",3000,,20
7934,"MILLER","CLERK",7782,"23-JAN-82",1300,,10
  
    
-----------------------------------------
-- contents from file scott.ctl
-----------------------------------------
load data
 
infile      'd:\temp\loader\scott.txt'
badfile     'd:\temp\loader\scott.bad'
discardfile 'd:\temp\loader\scott.dsc'
 
replace
 
into table loadtest
(
   empno      CHAR(2000)   "getcol( :empno, 1, \',\' )",
   ename      CHAR(2000)   "getcol( :empno, 2, \',\', \'\"\' )",
   hiredate   CHAR(2000)   "to_date( getcol( :empno, 5, \',\', \'\"\' ), \'DD-MON-YY\' )",
   sal        CHAR(2000)   "to_number( getcol( :empno, 6, \',\' ) )",
   comm       CHAR(2000)   "to_number( getcol( :empno, 7, \',\' ) )"
)
</TT>
how to read txt file [message #368244 is a reply to message #367929] Thu, 12 October 2000 06:58 Go to previous message
deonarayan
Messages: 1
Registered: October 2000
Junior Member
Hi
I wanted to read my txt file and store into
the orcle database , can i use sql loader
and how to use it. and site or idea

thanks
Previous Topic: Export
Next Topic: SQL*Loader - multiple line load - HELP!!
Goto Forum:
  


Current Time: Fri Mar 29 06:42:32 CDT 2024