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
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
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.
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 '
In this way SQL loader utility of oracle can be used to load csv files in to database.