Load data from CSV file into oracle

Hi Friends, in this article, we will load data from the CSV file into oracle using SQL loader. SQL Loader is a tool which is providing by oracle.

Read: How to create a SEQUENCE step by step

What is SQL Loader?

SQL Loader helps us to load data from external files into tables of the oracle database. For more details Click Here.

Load data in empty or non-empty table

We can load data in the empty or non-empty table of the oracle database using the below parameters in SQL Loader.

Note:- The SQL Loader is not working on existing data.

1. INSERT
2. APPEND
3. REPLACE

1. INSERT : This is the default option which is just insert the data in empty table, if table is not empty the SQL Loader is exit with an error "Table is not empty".
2. APPEND : If table is not empty, the SQL*Loader appends the new rows and if the data is not exist in the table then new rows are simply loaded.
3. REPLACE : Just delete all the rows and loaded new rows in the table.

Load Data from CSV to ORACLE TABLE using SQL LOADER

Step 1. Save your excel file in CSV format, in my case, I have a test.csv file.

$cat test.csv

Shri,Noida,DBA
Aarav,Delhi,English
Dharm,Delhi,DBA
RAVI,Noida,HR

Step 2. Create a table as per your requirement. The table name is data which has three columns.

SQL> create table data (ename varchar2(20),loc varchar2(20),job varchar2(20));

Step 3. After table creation now we need to create a control file to load data from non-oracle into the oracle table.

$vi data_control.ctl

LOAD DATA
INFILE 'test.csv'

insert
into table data
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(ename,loc,job)

Step 4. Now run the SQL LOADER using the below command.

$sqlldr control=/u01/delhi/data_control.ctl data=/u01/delhi/test.csv log=/u01/delhi/load_data.log

Username:/ as sysdba

Output from the above query

Now login to the database and check records in the table.

You can see the records have been inserted into the table.

In another case if you want to load more data into the same table so just replace INSERT into APPEND or if you want to change all data then use the REPLACE into the command.

Load data from CSV file into oracle using SQL Loader