Use external table to load data in Oracle from text / .csv or excel files

External tables are used to load data into database from text,csv or xml files.


First we need to create a directory object on Server where we will place our files that we want to load.

--Connect to your database as sys user.
sqlplus  / as sysdba

--Query to find existing Directory objects  
select * from all_objects where object_type ='DIRECTORY';

--Query to find directory object path to operating system directory .
SELECT OWNER, DIRECTORY_NAME, DIRECTORY_PATH
  FROM ALL_DIRECTORIES;

--IF DIRECTORY DOES NOT EXITS USE FOLLOWING COMMAND TO CREATE DIRECTORY object  .
--Directory object should point to local path on the database machine.

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'c:\external';

--Grant Read and write privilige to user who will load data .
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO ali;

-- connect the user that will load data.
conn ali/ali

--- Use following command to create external table.

CREATE TABLE EXT_TABLE (
DATESTIME    VARCHAR2(500),
LATITUDE   varchar2(500),
FIELD1   varchar2(500),
FIELD2   varchar2(500),
FIELD3   varchar2(500),
FIELD4   varchar2(500),
FIELD5   varchar2(500),
FIELD6   varchar2(500),
FIELD7   varchar2(500),
FIELD8   varchar2(500),
FIELD9   varchar2(500),
FIELD10   varchar2(500),
FIELD11   varchar2(500),
FIELD12   varchar2(500),
FIELD13   varchar2(500),
FIELD14   varchar2(500),
FIELD15   varchar2(500),
FIELD16   varchar2(500),
FIELD17   varchar2(500),
FIELD18   varchar2(500),
FIELD19   varchar2(500),
FIELD20   varchar2(500),
FIELD21   varchar2(500),
FIELD22   varchar2(500),
FIELD23   varchar2(500),
FIELD24   varchar2(500),
FIELD25   varchar2(500),
FIELD26   varchar2(500),
FIELD27   varchar2(500),
FIELD28   varchar2(500),
FIELD29   varchar2(500),
FIELD30   varchar2(500)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DATA_PUMP_DIR
  ACCESS PARAMETERS (
  RECORDS DELIMITED BY NEWLINE
  READSIZE 5004288 
  BADFILE DATA_PUMP_DIR:'bad_%a_%p.bad'
  LOGFILE DATA_PUMP_DIR:'log_%a_%p.log'
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  MISSING FIELD VALUES ARE NULL
  REJECT ROWS WITH ALL NULL FIELDS
    (DATESTIME,    LATITUDE,    FIELD1,    FIELD2,    FIELD3,    FIELD4,    FIELD5,    FIELD6,    FIELD7,    FIELD8,    FIELD9,    FIELD10,    FIELD11,    FIELD12,    FIELD13,    FIELD14,    FIELD15,    FIELD16,    FIELD17,    FIELD18,    FIELD19,    FIELD20,    FIELD21,    FIELD22,    FIELD23,    FIELD24,    FIELD25,    FIELD26,    FIELD27,    FIELD28,    FIELD29,    FIELD30
    ))
    LOCATION (DATA_PUMP_DIR:'demo1.dat'))
PARALLEL
REJECT LIMIT UNLIMITED;


--- Use following command to view structure of external table.
desc ali.ext_table

-- Create file demo1.dat with following data and place is on database server in the directory path where directory object is pointing.

1111,MORGAN,DIRECTOR,10
2222,HARDIE,MANAGER,30
3333,HAVEMEYER,CTO,10
4444,LOFSTROM,DEVELOPER,10
5555,TOWNSEND,MANAGER,30


-- use following command to load data from demo1.dat file.

ALTER TABLE ALI.EXT_TABLE LOCATION('demo1.dat');

--- Use following command to view data loaded in the external table.

SELECT * FROM ali.EXT_TABLE;

-- Create file demo1.dat with following data and place is on database server in the directory path where directory object is pointing.

1111,MORGAN,DIRECTOR,10
2222,HARDIE,MANAGER,30
3333,HAVEMEYER,CTO,10
4444,LOFSTROM,DEVELOPER,10
5555,TOWNSEND,MANAGER,30
1111,MORGAN,DIRECTOR,10
2222,HARDIE,MANAGER,30
3333,HAVEMEYER,CTO,10
4444,LOFSTROM,DEVELOPER,10
5555,TOWNSEND,MANAGER,30
1111,MORGAN,DIRECTOR,10
2222,HARDIE,MANAGER,30
3333,HAVEMEYER,CTO,10
4444,LOFSTROM,DEVELOPER,10
5555,TOWNSEND,MANAGER,30


-- use following command to load data from demo2.dat file.

ALTER TABLE ALI.EXT_TABLE LOCATION('demo2.dat');

--- Use following command to view data loaded in the external table.

SELECT * FROM ALI.EXT_TABLE;

FILENAME.TXT IS FILEPATH, ANY TXT FILE, XML FILE , CSV FILE