In my previous articles I have explained about different SQL interview questions and answers.In this article I would like to explain another most important interview question how to import excel in SQL.To import excel in SQL is most common scenario.In real project examples database programmer needs to import data in SQL for fulfilling the client requirements.There are multiple ways to import data in excel.
Import Excel in SQL using SQL Developer :
There are two ways to import excel in SQL
In this section I would like to give you the steps of importing data in SQL using Oracle SQL developer.First I would like to give you some steps and then I will explain real life industry example of same.
Step 1 : Check columns of excel and create table
Example : If user needs to import customer data from Customer excel which has customer_id ,customer_name columns.User needs to create empty table named Customer.
Create table Customer
(Customer_id varchar2(20)
Name varchar2(20));
Step 2 : Check for the data types in Excelsheet so as to create the table accordingly .
Example :
If excel sheet contains the name data more than 20 characters then it will fire the error while importing .So I am altering the table and keeping the length as 50.
Alter table Customer
Modify (customer_name varchar2(50));
Step 3 : Import functionality in SQL developer
To use import functionality you need to go to that specific table in SQL developer.
Open SQL developer – connect with database – go to tables- select specified table and right click.
User needs to select import option and select appropriate excel sheet.
Step 4 : Using Import Wizard
This is the most important step of importing data in to table.In import wizard you will see multiple options.
4.1.Select specified excel sheet by using browse button.
4.2.Check header checkbox if you want to keep first row of table is column of that excel.
4.3 Preview row limit check for verification of data .If preview row limit is 100 then you can verify data of first 100 rows.
4.4.click on next button .There are two methods here to import the data from excel sheets .If you simply select insert then it will run batch job to insert data in to table from excel.
4.5.There is another way to do this if you want to generate insert scripts.There is insert scripts option which will generate the insert script.
Step 5 : Ordering of data and columns according to table created.
Step 6: Mapping of data according to table
Using column definition option user can check for data before inserting in to specific column.
Step 7 : Click on next button and finish . It will run the batch job internally and import excel data in to specific table.
These are above most common steps to import data from excel in to SQL.
Following important things to check before importing data in to table:
1.Check the length of variable in SQL table and the excel data.If the length of excel data is greater than the table created it will fire the error.
2.Check for primary keys.If primary key columns are there in the table check for excel data is also unique.
3.Check for date format is uniform in excel sheet.
I hope this article is useful for you.I will try to add more screenshots so that you will get idea about import process in SQL.If you like this article or if you have any questions kindly comment in comments section.