Using SQL Loader in Oracle to Import CSV File

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 2 mins read

In my previous post, I have given the example to import CSV file into Oracle Table using the stored procedure. And in this post, I am giving an example to import CSV file in Oracle using SQL Loader. There is a lot of difference between loading a file using the stored procedure and using SQL loader in Oracle to import CSV file. That you can see and compare both the methods and you will know.

To import CSV data into Oracle table using SQL Loader, you need to create a control file in which you will sequentially define column names as per the CSV file comma separated fields. For example, below is the one line of CSV file containing fields of EMP table:

7369,SMITH,CLERK,7902,17/12/1980,800,,20

In this case, you must know the field mapping from CSV file to the Oracle table in which you want to import the data using SQL Loader. Below is the SQL Loader control file example to import the above CSV file format into Scott schema EMP table.

SQL Loader Control File Example

LOAD DATA
APPEND
INTO TABLE emp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE DATE "dd/mm/yyyy",
SAL,
COMM,
DEPTNO
)

You can see in above control file example that the table name specified there and also the column names. Save the file with extension name .ctl and call it using SQLLDR command to import data. Below is the example to import data in Oracle table using SQL Loader (SQLLDR command).

Using SQL Loader in Oracle to Import CSV File

Syntax:

sqlldr UserID/Password@ConnString data=DataFileNameWithPath control=ControlFileNameWithPath log=LogFileNameWithPath

Example:

sqlldr vinish/vinish@orcl data=f:\temp\abc.csv control=f:\temp\sqlldrex.ctl log=f:\temp\sqllog.log

The above command will first connect to Oracle database then will check for the data, control and log file and will start the import.

import csv file in oracle table

There are more parameters for SQLLDR command you can check it in Oracle help manual click here.