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.
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.
- 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”.
- 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.
- 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