Loading Data From Excel Sheet To Oracle Table

In this blog, I show how to convert data in Excel sheet to Oracle table using Sql*Loader.

Saving Excel Sheet In Comma Separated Format

In order to convert data of Excel Sheet, we need to save the worksheet using Comma Separated Format by following the steps given below. First create an Excel sheet with two columns (or open it if it is already present) where first column contains name of the person and second column contains age.

Select Save As option and select CSV (Comma Delimited) format from Save As Type dropdown
Select Filename as persons.csv in c:\oracle folder. This will create persons.csv file that contains name and age sepearated by , (comma). You can open it with any text editor and check its contents.

Creating control file for Sql*Loader

Sql*Loader is a utility from Oracle, which loads data from the given source into Oracle tables. We need to create a control file to provide information regarding loading process like which table to use, where is the input file and the structure of the table.

Create control file using any text editor as follows:

load data
infile persons.csv
replace
into table persons
fields terminated by ','
(name,age)

The above control file specifies the following details :

Input file is persons.csv
Table to be used is PERSONS
Fields in the input file are terminated by , (comma)
Name and Age are the columns in PERSONS table

Save control file with the name control.ctl in c:\oracle folder.

Run Sql*Loader

The final step is to run Sql*Loader from command prompt as follows. I assume PERSONS table is already persent in HR account with the following structure.

Name   varchar(20)
Age    number(2)

Now invoke sqlldr (sql*loader) from command prompt to load data from persons.csv file into PERSONS table using control file - control.ctl. Make sure you are in c:\oracle folder as both CSV file and Control file are in that folder. SQLLDR is in BIN directory of Oracle, which is in the system path. If not, put BIN directory of Oracle installation in the system path before giving the following command.

sqlldr  userid=hr/hr,control=control.ctl

After the command is executed, login into HR account and check the data loaded into PERSONS table. You must find rows that contain the same data that Excel Sheet contains.