5 different ways to load flat file into Oracle table

We need to move data from flat file to Oracle table frequently. For example sales department sends daily sale data in excel sheet to IT department, how this data feed into Oracle database (to tables) ? we will examine different methods.

1.  SQLLOADER 

sqlloader is an Oracle utility to load data from external files to table. This is one of the most used utility in Oracle database.

EMP
——- ——– ————
EMPNO      NOT NULL NUMBER(38)
EMPNAME                    VARCHAR2(20)
SALARY                          NUMBER
DEPTNO                         NUMBER(38)

First create a control file with details of flat file and table column prameters

emp.ctl

LOAD DATA
INFILE ‘C:\Temp\emp.csv’
BADFILE ‘C:\Temp\emp.bad’
DISCARDFILE ‘C:\Temp\emp.dsc’
INSERT INTO TABLE emp
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ TRAILING NULLCOLS
(empno,empname,salary,deptno)

Flat file as emp.csv

10,Bill,12000,5
11,Solomon, 10000, 5
12,Susan, 10000, 5
13,Wendy, 9000 , 1
14,Benjamin, 7500, 1
15,Tom, 7600 , 1
16,Henry, 8500, 2
17,Robert, 9500, 2
18,Paul, 7700, 2

run below command in command prompt (Generally ORACLE_HOME\bin)

sqlldr userid=username/password@connect_string control=c:\temp\emp.ctllog=c:\temp\emp.log

Bad data will log into bad file. Note that sqlloader has many many options in it, many professionals use it to automate the loading process.

2. EXTERNAL TABLES

External tables is an advanced feature of Oracle SQLLOADER. You can write sql on top of the External Tables.

Step 1 :- Create directory ( with the help of DBA may be) and grant permissions to the user

sql>create directory load_dir as ‘C:\Temp’;

sql>grant read,write on directory load_dir to user;

Step 2 :- Create flat file in directory

emp.csv

10,Bill,12000,5
11,Solomon, 10000, 5
12,Susan, 10000, 5
13,Wendy, 9000 , 1
14,Benjamin, 7500, 1
15,Tom, 7600 , 1
16,Henry, 8500, 2
17,Robert, 9500, 2
18,Paul, 7700, 2

Step 3 :- Create EXTERNAL TABLE

Drop table EMP if it exists and then create it using below sql

CREATE TABLE emp
(EMPNO integer,
EMPNAME VARCHAR2(20),
SALARY integer,
DEPTNO integer)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY load_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
fields terminated by ‘,’
)
LOCATION (’emp.csv’)
);

Step 4 :- Run select query ( This will select data from External Table which is associated with a flat file )

Select * from emp;

10 Bill 12000 5
11 Solomon 10000 5
12 Susan 10000 5
13 Wendy 9000 1
14 Benjamin 7500 1
15 Tom 7600 1
16 Henry 8500 2
17 Robert 9500 2
18 Paul 7700 2

3. UTL

UTL is another Oracle utility to load data from flat file to Oracle and vice versa.

Step1 :- Check the utl_directory using below sql (Ask DBA if you do not have priviliges)

Here “C:\TEMP”is the utl directory. So we can use any files in the directory for utl operations. I have an emp.dat file in it;

emp.dat

10,Bill
20,John
30,Bruce

USe below plsql block to read all lines from emp.dat

DECLARE
filehand UTL_FILE.FILE_TYPE;
line VARCHAR2(4000);
v_empno varchar2(10);
v_empname varchar2(10);
BEGIN
filehand := UTL_FILE.FOPEN(‘C:\TEMP’, ’emp.dat’, ‘R’);
LOOP
UTL_FILE.GET_LINE(filehand, line);
if line is not null then
v_empno := substr(line,1,instr(line,’,’)-1);
v_empname := substr(line,instr(line,’,’)+1,length(line));
dbms_output.put_line(v_empno);
dbms_output.put_line(v_empname);
insert into emp(empno,empname) values(v_empno,v_empname);
commit;
else
exit;
end if;
END LOOP;
commit;
utl_file.fclose(filehand);
EXCEPTION
WHEN others THEN
null;
END;
/

3 rows inserted into emp table.

4. Using Tools (SQLDEVELOPER)

EMP
——- ——– ————
EMPNO           NOT NULL    NUMBER(38)
EMPNAME                             VARCHAR2(20)

emp.csv

10,Bill
11,Solomon
12,Susan
13,Wendy
14,Benjamin
15,Tom

Step 1 – Click on Tables –> EMP

Step 2 – Click on Actions –> Import Data –> Choose csv file

Step 3 – Click next  –> choose column details –> next

Data successfully loaded into EMP table

similarly TOAD also have

5. INSERT script

Data can be loaded using insert script. You can use excel to create dynamic insert scripts

Copy insert script from C column and run

insert into EMP(empno,empname) values (11,’Solomon’);

insert into EMP(empno,empname) values (12,’Susan’);

insert into EMP(empno,empname) values (13,’Wendy’);

insert into EMP(empno,empname) values (14,’Benjamin’);

insert into EMP(empno,empname) values (14,’Tom’);

Generally for large data sets, sqlloader and external tables are preferred methods.

( Note :-  I have used very basic examples only to explain the basics of flat file loading, it may have some logical errors )