Import data from Excel to SQL Server

Import data from excel is a very basic feature available in SQL server. But I tend to think that not every one is aware of this function.Let me share the work flow and some of my thoughts on it.

1)      Right  click on the database to which we want to import and click on the “Import Data” option there 

2)      Click on next and get to the following  screen

Here first we need to select “Excel” from the data source value list. Then we can specify the path of the excel data file.

If we can notice at the bottom, there is an option called “First row has Column names” which used to specify whether the first row in the excel file is the header row (which has SQL column names). If we unselect this then SQL will name the columns of the imported table on its own.

On the other hand if it is ticked, those headings will be taken as column names by SQL.

Let say our excel file contains some data like this

Here the ID, First Name and Last Name will be taken as the field names by SQL.

3)      Click on next and we will get to the following screen.

Here we can specify the database to which we are going to import the data.

4)      Click next twice and now we are here.

SQL will name the destination table something like “[dbo].[Sheet 1 $]” as shown in the screen shot. Actually the table name value “[Sheet 1 $]” taken from excel sheet’s name. We can rename excel sheet name to “Employee” to keep the table name meaningful.

Also we can rename it here as well.

 
There is a button called "Edit Mappings". Let's  click on it and go to the column mappings screen.

Here we can change the data types as well as the size of the fields. As you see we also have "Drop and re-create destination table " and "Enable identity insert" options there. For the ID field, SQL put "float" type by default. I have changed it to integer. Same goes for the size of the name fields too.


Now let it have the default values for the rest of the screens and complete the execution.

The last screen will look like this

Now if we go to the DB and check the “Employee” (the table name which given in the previous step) table, imported data will be there.

Few additional notes

• Let say we did not select the option “First row has Column names” mentioned in step 2, then our result set will look like this

 

  

     Here the header row also has been taken as a data row. And SQL itself will name the columns as F1, F2 and F3.

• We should not leave any space in between rows since that will result in records with null values.                          

           Let’s have the excel file like this

 

      If we import this to the DB then the result set will be

•   And it is very important that we should start from the first row itself in the excel file as the first row will be taken as the header row. 

      Hope this helps. Have a nice day.