SQL tutorial 44: How to import data from Microsoft Excel to Oracle Database using SQL Developer

Step by Step Oracle Database/ SQL tutorial on How to import Data from Microsoft excel to the oracle database using SQL Developer.

Celebrating 1000 subscribers. Thanks a lot guys for all your love and support.
------------------------------------------------------------------------ ►►►LINKS◄◄◄
Website: www,Rebellionrider.com

Create Table using
●SQL Developer & Command Prompt:
http://youtu.be/UU0EEfpa-2c
●Enterprise Manager: http://youtu.be/I-LUXP9GmPU
-------------------------------------------------------------------------
Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage
https://copy.com?r=kb4rc1

--------------------------------------------------------------------------
►Make sure you SUBSCRIBE and be the first one to see my videos!
--------------------------------------------------------------------------

Amazon Wishlist: http://bit.ly/wishlist-amazon

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
►►►Find me on Social Media◄◄◄
Follow What I am up to as it happens on

https://twitter.com/rebellionrider
https://www.facebook.com/imthebhardwaj
http://instagram.com/rebellionrider
https://plus.google.com/+Rebellionrider
http://in.linkedin.com/in/mannbhardwaj/
http://rebellionrider.tumblr.com/
http://www.pinterest.com/rebellionrider/

You can also Email me at
RebellionRiderYT@gmail.com

Connect with me on my LinkedIn and Endorse My Skills and Do you know that I share Tips and tricks On Google+ Account?

Please please LIKE and SHARE my videos it makes me happy.
Thanks for liking, commenting, sharing and watching more of our videos

This is Manish from RebellionRider.com
♥ I LOVE ALL MY VIEWERS AND SUBSCRIBERS

---

Closed Caption:

What’s up internet?
Welcome back once again, I am Manish from
RebellionRider.com
And today in this SQL tutorial we will learn
how to import data from Microsoft excel sheet
into oracle database using SQL Developer.
So without wasting any time, let’s start
today’s tutorial.
The prerequisite for this tutorial are
1. A Microsoft excel file with some data to
import. And
2. A table in your database for holding the
imported data.
To save time, I have already created a Microsoft
excel file by the name of source and I have
saved it on my desktop. Let’s take a look
at this file.
As you can see, this file has 4 columns. First
name, last name, phone number and hire date.
First two columns first name and last name
hold character data whereas column phone number
is a numeric column at the same time the last
column hire date holds the data of type DATE.
I explained this to you because we have to
take care of these data type while creating
table in our database.
Moreover this excel document has two worksheets
Sheet 1 and Sheet 2 but only sheet 1 has 12
rows of data and Sheet 2 is empty.
And now I will create a table in my database
using HR user, which will hold the data which
we are going to import from this Microsoft
excel sheet.
I have done series of videos on how to create
table using create table command, SQL Developer
and Enterprise manager. If you want you can
watch as well as like those tutorials. Links
are in the description box.
Moreover you can also give me thumps up on
this tutorial and motivate me for doing more
such videos.
So let’s create the table.
CREATE TABLE DEMO
(F_NAME VARCHAR2(20), L_NAME VARCHAR2(20),
CONTACT NUMBER(20), H_DATE DATE);
Here is our table.
Here in this table I set the data type of
first two column f-name and L-name on varchar
means they can hold the character text easily.
The data type of contact column is number
and column h-date has DATE data type perfect
for holding the values from column hire date
of our excel sheet.
Now the ground is all set. Let’s start the
process of importing data from Microsoft excel
sheet to this table.
First go to your connection tab then double
click and expand the connection in which you
have created the table. In my case I have
created my table in my Local HR connection.
This is the tree structure of all the database
objects owned by my HR user. From all these
database objects double click and expand this
table folder and after that locate your table
in which you want to import the data. In my
case the table is DEMO.
Let me confirm for you once that this table
does not have any data.
As you can see there is no data in this table.
Now right click and select the table and then
choose import data or you can also select
import data option from this Action drop down
list
Here as you can see in this window we have
to find and open the Microsoft excel file
from which we want to import the data into
the our oracle database. I have saved my Microsoft
file by the name of source on my desktop.
Here it is. Select the file and hit open.
Here if you will look closely in the file
type drop down list, you will find that all
these are the type of file from which you
can import the data.
As soon as you hit open this will open up
the import wizard, there are total 5 steps
which we have to perform to import the data
from Microsoft excel to our oracle database.
Step 1: First step is Data preview. You can
see there are 5 options on the frame Header,
Skip Rows, Format Preview Row Limits and Worksheet.
Header: If you want to treat the first row
of your excel sheet as your column header
or as your column name then let this checkbox
remain checked. Let’s uncheck this checkbox
and see what happens.
As you can see if I uncheck this box the SQL
Developer treats my first line of excel sheet
as the actual data rather than the name of
column or column header. I will let it remain
checked as my first row of excel sheet consists
the name of my columns.
Next is Skip Rows: Here you can enter the
number of rows which you want to skip from
the top. For example say I want to skip my
top 2 rows then I will write 2 here. As you
can see above 2 rows are removed. Let it again
set on zero
Third option is Format: Here you can chose
between xls or xlsx format for your excel
sheet.
Fourth option is Preview Row Limits: Here
you can set the limit on number of rows which
you wish to see in this preview panel at once.
Last option is Worksheet: This option is only
available when your excel document has more
than one worksheet. However if your MS Excel
has only 1 worksheet then this option will
not be there on your screen. Here you can
chose from which worksheet of your Microsoft
excel document you want to import the data.
I have two worksheets in my Microsoft excel
but only sheet 1 has data and sheet 2 is empty.
As you can see here.
Let me show you, as you can see there is no
data in sheet 2 but in sheet 1 we have some
data.
Now Hit next
Step 2: Second Step is the Import method.
Here we have 3 options import method, table
name and import row limit.
In Import method there are 2 options Insert
and Insert Script. If you choose Insert then
SQL Developer will directly insert the data
into your table but if you choose the Insert
Script then SQL Developer will create a Script
which you have to Execute to insert the data
into your table. I will choose Insert here.
Second option is the table name. Set it on
DEMO which is the name of our table.
Third is import row limit. Here you can choose
how many rows you want to insert in your table
at once. Let’s hit next
Step 3: Next Step is Choose Column: By Default
here SQL Developer has selected all the columns
of our Microsoft Excel Sheet. Here in this
step you can choose values from which column
you want to import.
Like if you don’t want to import values
from hire data column then you can simply
remove it but I want to import the values
from hire data column so I will select it
and put it select column panel
Let it be like this as we want to import the
values from all the columns.
Step 4: Match Column. Here you can map the
column. Source data column panel consists
of all the column names of our Microsoft Excel
sheet and Target table columns panel has the
information of the columns of our table demo.
Here you must take care that the data type
of both side columns must match. If there
is any date column then the date format of
both side columns must be the same.
For example I want to import values of first
name column of excel sheet into the f name
column of the demo table. Both have same data
type and compatible column width.
Similarly I will map last name column over
L-name and
Phone number column over contact column
And at the end hire data column over H-date
column but here we have to define the data
format which is mandatory. So I will set my
date format on DD-MON-YY.
Hit Next.
In the last step click this verify button.
So the status is success which means you are
good to go. If something is wrong then SQL
Developer will show the error here.
But for now everything is ok
So hit finish
Let’s check the data.
And here we go as you can see we have successfully
imported the data from Microsoft excel to
our table demo.
That’s it for today. Hope you learnt something.
Do you know guys I share tips and tricks with
other information on my google+ and we can
be friends on my twitter, Instagram and Facebook.
Find the links in the description box.
Liked what you saw? Then do hit the like button!!!
It encourages me to do more such interesting
videos. Please share my videos and help me
in reaching out to more people around the
world. And don’t forget to subscribe.
Thanks for watching.
Signing off for today
This is Manish.
4