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.

Post Your Comment

Enter the code given in the above image :

Enter Your Name :

Enter Your Email Address :

Enter Your Comment :

Comments
Posted By srinivasu namburi On 02-Jun-10 12:46:33 PM Hi , This is good one in order to transfer bulk data from excel sheet to backend database. But the major bottle neck of this load data infile command is we need to store the excel file in oracle environment like HR account. I think this command will not work when the excel file is in external(remote) environment.

Posted By Ganesh On 02-Jun-10 11:07:52 PM Its very good.

Posted By Sagar On 06-Jun-10 08:16:01 AM A question sir. Is the same possible when the delimiter is a tab(space) & 2. Is the same possible when there are more than 2 fields present ?

Posted By Sagar On 06-Jun-10 08:25:57 AM Also, when I tried to access sqlldr from the bin directory of oracle. It is opening and closing immediately. What can be the reason for this ?

Posted By Sagar On 07-Jun-10 11:03:37 AM Sir, nice to hav this...as in steelplant we used to store huge data in excel sheets....Recently i found --> one tool (it is 2.5 mb) to load data from Excel Sheet to Oracle Tables..It'll also serve the same purpose.... But its always good to have this feature from oracle itself --Thanking You Sir!

Posted By aravind On 13-Jun-10 03:34:06 PM I thanks to u sir because ur the one of the best trainer who can make ur students successful

Posted By Suresh On 09-Oct-10 03:20:28 PM thanks to u sir because ur the one of the best trainer in my life

Posted By Preethi On 19-Jul-11 09:12:16 PM I read the articles in you blog. All are motivating the students to learn whats meant by a program and to improve their skills. I need a clarification over replacing strings in an existing word document. So, I have developed a java pprogram which first makes a copy of the existing word file. Then, I have opened the new file as a Random Access File and used file pointer to replace the particular word. Its replacing the word by the new word, what I give as input. The problem is the alignment is collapsed such as if the replaced word is in the center, the replacing word goes to the left alignment. Also unnecessarily, blankspaces are appended at the end of the replacing word. How come this? Provided, I am reading the file as bytes. How can I overcome this problem. Hopefully looking for ur suggestion. Thanks in Advance, Preethi

Posted By kishore gajula On 19-Jun-12 10:02:07 PM i need stores consumables format for steel company in excel

Posted By Sebin Tony On 04-Nov-13 04:03:05 PM Thanks for sharing this. And while am trying to convert excel file to csv file some of the values became junk values(showing ####). so how to create the csv file properly. Thanks

Posted By Bibhuti On 28-May-19 12:32:18 PM Thank you for SQL loader concept ,but I have question regarding excel file ,if I have multiple tabs at down in a excel sheet then how I will insert the data through SQL loader

Posted By V SAIRAM On 22-Jun-20 05:19:44 PM how can we migrate a TABLE Data from one user to another user using sql loader.

Comments

Posted By srinivasu namburi On 02-Jun-10 12:46:33 PM

Hi ,
This is good one in order to transfer bulk data from excel sheet to backend database. But the major bottle neck of this load data infile command is we need to store the excel file in oracle environment like HR account.
I think this command will not work when the excel file is in external(remote) environment.

Posted By Ganesh On 02-Jun-10 11:07:52 PM

Its very good.

Posted By Sagar On 06-Jun-10 08:16:01 AM

A question sir. Is the same possible when the delimiter is a tab(space)
& 2. Is the same possible when there are more than 2 fields present ?

Posted By Sagar On 06-Jun-10 08:25:57 AM

Also, when I tried to access sqlldr from the bin directory of oracle. It is opening and closing immediately. What can be the reason for this ?

Posted By Sagar On 07-Jun-10 11:03:37 AM

Sir, nice to hav this...as in steelplant we used to store huge data in excel sheets....Recently i found --> one tool (it is 2.5 mb) to load data from Excel Sheet to Oracle Tables..It'll also serve the same purpose....

*But its always good to have this feature from oracle itself*

--Thanking You Sir!

Posted By aravind On 13-Jun-10 03:34:06 PM

I thanks to u sir because ur the one of the best trainer who can make ur students successful

Posted By Suresh On 09-Oct-10 03:20:28 PM

thanks to u sir because ur the one of the best trainer in my life

Posted By Preethi On 19-Jul-11 09:12:16 PM

I read the articles in you blog. All are motivating the students to learn whats meant by a program and to improve their skills. I need a clarification over replacing strings in an existing word document. So, I have developed a java pprogram which first makes a copy of the existing word file. Then, I have opened the new file as a Random Access File and used file pointer to replace the particular word. Its replacing the word by the new word, what I give as input. The problem is the alignment is collapsed such as if the replaced word is in the center, the replacing word goes to the left alignment. Also unnecessarily, blankspaces are appended at the end of the replacing word. How come this? Provided, I am reading the file as bytes. How can I overcome this problem. Hopefully looking for ur suggestion.
Thanks in Advance, Preethi

Posted By kishore gajula On 19-Jun-12 10:02:07 PM

i need stores consumables format for steel company in excel

Posted By Sebin Tony On 04-Nov-13 04:03:05 PM

Thanks for sharing this. And while am trying to convert excel file to csv file some of the values became junk values(showing ####). so how to create the csv file properly.
Thanks

Posted By Bibhuti On 28-May-19 12:32:18 PM

Thank you for SQL loader concept ,but I have question regarding excel file ,if I have multiple tabs at down in a excel sheet then how I will insert the data through SQL loader

Posted By V SAIRAM On 22-Jun-20 05:19:44 PM

how can we migrate a TABLE Data from one user to another user using sql loader.