How to use sqlloader to load a csv file into Oracle database table

Scenario:
Employee table is created to store employee details. Load the employee details from the given csv(comma seperated values) file into the employee table using sqlloader.


Create the table to store employee details:
create table emp
(emp_id number(10),
emp_name varchar2(25),
emp_dept_id number(10),
constraint emp primary key (emp_id));


Prerequisites to load csv file into the given table:
1. Table to be loaded.
2. Control file to load the data into employee table. Sample control file is given below.
3. CSV file.
4. SQL script to trigger the data load.

Control file specifies:
csv file name and path
Table to be loaded
Options to SKIP the header line
Option to truncate the table before loading.
Column Names to be loaded.

Sample control file defined:
Sample control file used here is named as EmployeeDetails.ctl
SKIP=1 will skip the heard in the file. Header info is nothing but column names. If column names are not there in the csv file then we can skip this line

Table to be loaded has to be truncated before loading the file (this method is to flush and load the table), else will through error saying "Table is not empty". Truncate option will truncate the table before loading the data. Alternative is to truncate the table manually before triggering the sqlloader.

Values in csv can be seperated by characters like ', ;, | etc. This has to be mentioned in the control like Terminated by ";" (if the column values are seperated by ',' then repalce this with ",")

Column names need to mentioned in the control file


Sample control file:
OPTIONS (SKIP=1)load data infile 'c:\shiyas\EmployeeDetails.csv'
TRUNCATE into table emp fields
terminated by ";" optionally enclosed by '"'
TRAILING NULLCOLS
(emp_id,emp_name,emp_dept_id)

Use the below script and execute from sqlplus to load the csv file:
Give the control file name as mentioned above
sqlldr username/password@host:port/servicename control=EmployeeDetails.ctl log=EmployeeDetails.log;
This will load the csv file to the employee table


Report on records inserted:
Refer EmployeeDetails.log to view the report on data load. It gives you information like how many records are inserted succesfully and how many records are rejected.

The log file will be flushed and loaded with new data evey time

Alert: If you really dont want to flush the table before loading data into the table please use APPEND instead of TRUNCATE. Else you will lose the data. Append act as a normal insert, whereas when you mention TRUNCATE, the table will be truncated before laoding.

****Happy CoDiNg!!****