Loading data into Oracle from Text File

SQLLOADER is a bulk loader utility used to move data from external files into oracle database. Simple example of loading external file is given below.

First you need to create a control file which should look similar to the below example

CONTROL FILE:

OPTIONS ( SKIP=1)
LOAD DATA
INFILE 'C:\TextFile\emp.csv'
BADFILE 'C:\TextFile\emp.bad'
DISCARDFILE 'C:\TextFile\emp.dsc'

INTO TABLE "SCOTT"."EMP"
INSERT FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'TRAILING NULLCOLS
(
EMP_ID sequence(Max, 1),
EMP_NAME,
EMP_DEPT,
EMP_ADDRESS,
EMP_DOB
)

If your .csv file has headers then you need to add SKIP = 1.

You need to make sure that you have emp.csv exist in the given location. Other files like .bad and .dsc will be generated by sqlloader itself, if required.

Now save this control file on any directory (eg. c:\LoadTest\Emp.ctl)

To execute this, Type following command in the command prompt

c:\sqlldr userid=scott/tiger@orcl control=c:\LoadTest\Emp.ctl log=c:\LoadTest\Emp.log