Oracle PL/SQL/Table/CSV Load

FIELDS TERMINATED BY WHITESPACE

 
create table dept
( deptno  number(2),
  dname   varchar2(14),
  loc     varchar2(13)
)
/
LOAD DATA                                                  
INFILE *                                                   
INTO TABLE DEPT                                            
REPLACE                                                    
FIELDS TERMINATED BY WHITESPACE
-- FIELDS TERMINATED BY x"09"
(DEPTNO,
DNAME,
LOC
)                                                          
BEGINDATA                                                 
10    Sales    BC
drop table dept;

Indicate columns in load data statement

 
create table dept
( deptno  number(2),
  dname   varchar2(14),
  loc     varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """
( DEPTNO,
  FILLER_1 FILLER,
  DNAME,
  LOC
)
BEGINDATA
20,Something Not To Be Loaded,Accounting,"BC,USA"

drop table dept;

Leave start position open in load data statement

 
create table dept
( deptno  number(2),
  dname   varchar2(14),
  loc     varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO      position(1:2),
  DNAME       position(*:16),
  LOC         position(*:29),
  ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting    BC,USA

drop table dept;

Load CSV data to a table

 
set echo on
create table dept
( deptno  number(2),
  dname   varchar2(14),
  loc     varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ","
(DEPTNO,
  DNAME,
  LOC
)
BEGINDATA
10,Sales,BC
20,Accounting,BC
30,Consulting,BC
40,Finance,BC
drop table dept;

Load data and set field terminated sign and OPTIONALLY ENCLOSed sign

 

create table dept
( deptno  number(2),
  dname   varchar2(14),
  loc     varchar2(13)
)
/
LOAD DATA                                                  
INFILE *                                                   
INTO TABLE DEPT                                            
REPLACE                                                    
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY """        
(DEPTNO,
DNAME,
LOC
)                                                          
BEGINDATA                                                 
10,Sales,"""USA"""
20,Accounting,"BC,USA"
30,Consulting,BC
40,Finance,BC
50,"Finance","",BC
60,"Finance",,BC
drop table dept;

Set char(count) for load data statement

 
create table dept
( deptno  number(2),
  dname   varchar2(14),
  loc     varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO      position(1) char(2),
  DNAME       position(*) char(14),
  LOC         position(*) char(13),
  ENTIRE_LINE position(1) char(29)
)
BEGINDATA
10Accounting    BC,USA

drop table dept;

Set date format in load data statement

 
create table dept
( deptno  number(2) constraint emp_pk primary key,
  dname   varchar2(14),
  loc     varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ","
(DEPTNO,
  DNAME,
  LOC,
  LAST_UPDATED date "dd/mm/yyyy"
)
BEGINDATA
10,Sales,BC,1/5/2000
20,Accounting,BC,21/6/1999
30,Consulting,BC,5/1/2000
40,Finance,BC,15/3/2001
drop table dept;

Set ENTIRE_LINE in load data statement

 
create table dept
( deptno  number(2),
  dname   varchar2(14),
  loc     varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO      position(1:2),
  DNAME       position(3:16),
  LOC         position(17:29),
  ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting    BC,USA

drop table dept;

Set entry line in load data statement

 
create table dept
( deptno  number(2) constraint emp_pk primary key,
  dname   varchar2(14),
  loc     varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ","
(DEPTNO,
  DNAME        "upper(:dname)",
  LOC          "upper(:loc)",
  LAST_UPDATED date "dd/mm/yyyy",
  ENTIRE_LINE  ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Sales,BC,1/5/2000
20,Accounting,BC,21/6/1999
30,Consulting,BC,5/1/2000
40,Finance,BC,15/3/2001

drop table dept;

Set value position in load statement

 
create table dept
( deptno  number(2),
  dname   varchar2(14),
  loc     varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
  DNAME  position(3:16),
  LOC    position(17:29)
)
BEGINDATA
10Accounting    BC,USA
drop table dept;

Trailing null columns when loading data

 
create table dept
( deptno  number(2) constraint emp_pk primary key,
  dname   varchar2(14),
  loc     varchar2(13)
)
/

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(DEPTNO,
  DNAME        "upper(:dname)",
  LOC          "upper(:loc)",
  LAST_UPDATED date "dd/mm/yyyy",
  ENTIRE_LINE  ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Sales,BC,1/5/2000 
20,Accounting,BC,21/6/1999
30,Consulting,BC,5/1/2000
40,Finance,BC,15/3/2001
drop table dept;

use case statement to check the value in load data statement

 
create table dept
( deptno  number(2) constraint emp_pk primary key,
  dname   varchar2(14),
  loc     varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(DEPTNO,
  DNAME        "upper(:dname)",
  LOC          "upper(:loc)",
  LAST_UPDATED "case when length(:last_updated) <= 10 
                     then to_date(:last_updated,"dd/mm/yyyy")
         else to_date(:last_updated,"dd/mm/yyyy hh24:mi:ss")
    end"
)
BEGINDATA
10,Sales,BC,1/5/2000 12:03:03
20,Accounting,BC,21/6/1999
30,Consulting,BC,5/1/2000 01:23:00
40,Finance,BC,15/3/2001
drop table dept;

Use string function in load data statement

 
create table dept
( deptno  number(2) constraint emp_pk primary key,
  dname   varchar2(14),
  loc     varchar2(13)
)
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ","
(DEPTNO,
  DNAME        "upper(:dname)",
  LOC          "upper(:loc)",
  LAST_UPDATED date "dd/mm/yyyy"
)
BEGINDATA
10,Sales,BC,1/5/2000
20,Accounting,BC,21/6/1999
30,Consulting,BC,5/1/2000
40,Finance,BC,15/3/2001
drop table dept;

Use user-defined columns in load data statement

 
create table dept
( deptno  number(2) constraint emp_pk primary key,
  dname   varchar2(14),
  loc     varchar2(13)
)
/
create or replace 
function my_to_date( p_string in varchar2 ) return date
as
    type fmtArray is table of varchar2(25);
    l_fmts  fmtArray := fmtArray( "dd-mon-yyyy", "dd-month-yyyy", 
                                  "dd/mm/yyyy",
                                  "dd/mm/yyyy hh24:mi:ss" );
    l_return date;
begin
    for i in 1 .. l_fmts.count 
    loop
        begin
            l_return := to_date( p_string, l_fmts(i) );
        exception    
            when others then null;
        end;
        EXIT when l_return is not null;
    end loop;
    if ( l_return is null ) 
    then
        l_return := 
           new_time( to_date("01011970","ddmmyyyy") + 1/24/60/60 *  
                     p_string, "GMT", "EST" );
    end if;
    return l_return;
end;
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(DEPTNO,
  DNAME        "upper(:dname)",
  LOC          "upper(:loc)",
  LAST_UPDATED "my_to_date( :last_updated )"
)
BEGINDATA
10,Sales,BC,01-april-2001
20,Accounting,BC,13/04/2001
30,Consulting,BC,14/04/2001 12:02:02
40,Finance,BC,987268297
50,Finance,BC,02-apr-2001
60,Finance,BC,Not a date
drop table dept;