Interview Questions

Load Data from Excel to Oracle database

In this article lets discuss about how to load data from an excel file in to oracle database table.

In many application projects you may be faced with a requirement to export data from a database table to an excel sheet or load data from an excel or csv file in to a database table. The former can be done using the help of any oracle client like TOAD. The latter can be done using an utility available in oracle called SQL Loader.

Let go through an example of how to import data from excel or csv file in to oracle db.

Have a look at the employee table below. It consists of EmpNo, EmpName, Salary, DOJ, Dept_Name columns and corresponding values.

8501 Jacob Marshall 15000 21/10/2009 Finance
8502 Chris Campell 50000 13/06/2008 R&D
8503 Simon Dough 50000 3/07/2008 Engineering
8504 Mark Haggai 75000 9/3/2006 Product Management
8505 Verica Beverley 45000 7/2/2008 HR

Copy the above table in to an excel sheet and save the file as emp_details.csv, in csv format.

Create a corresponding database table in oracle using the following DDL statement.

   
CREATE TABLE EXCELDEMO.EMP_DETAILS
(
  EMP_NO     NUMBER,
  EMP_NAME   VARCHAR2(100 BYTE),
  SALARY     NUMBER,
  DOJ        DATE,
  DEPT_NAME  VARCHAR2(100 BYTE)
)

Now we need to create a control(*.ctl) file, which contains data about the file to be loaded and the database table column details. Have a look at the emp.ctl file as follows

load data
infile 'C:\emp_details.csv'
into table DVA_TEST
fields terminated by ',' optionally enclosed by '"'
(EMP_NO,EMP_NAME ,SALARY,DOJ,DEPT_NAME NULLIF DEPT_NAME=BLANKS)

In the above file , provide the full path of the csv file in the second line after 'infile' command.

Table name in the database is to be provided in the third line after 'into table' command. Since its a csv file specify the field separator and mentioned in the fourth line. In the last line, the sequence of table column names are specified separated by commas. We can additionally specify a column value to be null if the actual column value is blank in the csv file. It can be done by specifying the column name in following format NULLIF =BLANKS.

Ensure that oracle client is installed in your PC or workstation. Now go to command line utility in windows to execute the SQL loader utility. Use the following command to load the csv file in to the DB.

sqlldr <db_user_name>/<db_pass_word>>@<db_name> control=<path of control file> log=<log file path>
   
eg: sqlldr scott/tiger@orcl control=c:\emp.ctl log=empLog.log

If there are any errors while executing the loading process, the details will be logged in the log file specified.
Additionally a '.bad' file will be created, specifying the records which could not be inserted, in the same location as the control file. Finally query the table in the database to check if the records are inserted.

In this way SQL loader utility of oracle can be used to load csv files in to database.