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

what's up internet welcome back once again I'm Anish from rebellion.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 a Microsoft Excel file with some data to import and a table in your database for holding the imported data to save the 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 four columns first name last name phone number and higher date first two columns first name and last name hold character data whereas column four number is a numeric column at the same time the last column higher date holds the data of type date I explained this to you because we have to take care of these data type while creating a table in our database moreover this Excel document has two worksheet sheet one and Sheet two but only sheet one has 12 rows of data and Sheet two 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 and yes I have done a 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 tutorial links are in the description box moreover you can also give me thumbs up on this tutorial and motivate me for doing more such video so let's create the table and here is our table here in this table I set the data type of first two column F name and L name on warar 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 higher 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 and 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 which is already expanded 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 filtered 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 so let's go here 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 and here it is 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 our Oracle database I have saved my Microsoft file by the name of Source on my desktop and here it is and here if you will look closely in this file type drop-down list you will find that all these are the type of files from which you can import the data okay now select the file and hit open as soon as you hit open this will open up the import visor there are total five steps which we have to perform to import the data from micros oft Excel to our Oracle database and the first step is data preview you can see there are five options on the frame header skip rows format preview row limit and worksheet head up 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 a ex ual data rather than the name of column or column header I will let it remain checked as my first row of Excel sheet consist the name of my columns next is Skip roles here you can enter the number of roles which you want to skip from the top for example say I want to skip my top two rows then I will write two here as you can see about two rows are removed let it again set on zero okay third option is format here you can choose between XLS or XLS X format for your Excel sheet fourth option is preview row limit 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 msxl has only one worksheet then this option will not be there on your screen here you can choose from which worksheet of your Microsoft Excel document you want to import the data I have two worksheet in my Microsoft Excel but only sheet one has the data and Sheet two is empty let me show you as you can see there is no data in sheet two but in sheet one we have some data okay now hit next Second Step is import method here we have three options import method table name and import row limit in import method there are two options insert and insert script if you choose insert then SQL Developer will directly insert the data in 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 a table name set it on demo which is the name of our table and the third is import row limit here you can choose how many rows you want to insert in your table at once okay let's hit next next step is choose column by default here SQL Developer has selected All The Columns of 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 higher date column then you can simply remove it but I want to import the values from higher date column so I will select it and put it in the selected columns panel okay let it be like this as we want to import the values from all the columns even you can change the order of the columns if you want from using these buttons okay hit next step four column definition ation here you can map The Columns Source data column panel consist of all the columns names of our Microsoft Excel sheet and the target table column panel has the information of The Columns of a table demo here you must take care that the data type of both side column must match if there is any date column then the date format of both side column 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 it's already on F name similarly I will map last name column over L name and phone number column over contact and at the end higher date column over Edge date but here we have to define the date format which is mandatory so I will set my date format on now 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 you the error here but for now everything is okay so hit finish as you can see we have successful import and here is the data let me hit okay here here is our data this means we have successfully imported the data from Microsoft Excel to our table demo do you know guys I share tips and tricks with other information on my go+ and we can be friend on my Twitter Instagram and Facebook find the links in the description box like what you saw then do hit the like button it encourages me to do more such interesting videos and 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