Monthly Archives: March 2012

Microsoft Community Contributor Award


I’ve received a mail from Microsoft and this is what mail was said

Congratulations! We’re pleased to inform you that your contributions to Microsoft online technical communities have been recognized with the Microsoft Community Contributor Award.
The Microsoft Community Contributor Award is reserved for participants who have made notable contributions in Microsoft online community forums such as TechNet, MSDN and Answers. The value of these resources is greatly enhanced by participants like you, who voluntarily contribute your time and energy to improve the online community experience for others.

It’s great honor to me and I want to see I could do not done it without you.

Thanks’ to everyone who helped me to reach to this point and I will try my best as much as I can


Leave a comment

Posted by on March 31, 2012 in General


Tags: , , , ,

Import data from Excel to SQL Server

in this article I’m going to show you how to import data from Excel to SQL Server in few steps but first take a look to import/export screen


As you can see the screen is so simple it’s have three controls

  • FileUpload control to help you to find the location of your Excel file.
  • Button to import data from Excel and save it in SQL Server.
  • Label control which is just message to tell of operation result(Success or Fail)

Steps to make application run successfully

Note: before start reading the steps I want let you to know that I haven’t test this sample either on ASP.NET 3.5 or 32-bit MS Office,so may be you do not need the step 1 and step 2.

  1. Create an IIS web site
  2. Change .Net Framework to 4.0 for Application Pool of this site
  3. Create Excel file and make sure that file contain the column name as following image


4.Create Table in SQL Server and make sure has the same Columns name with appropriate columns data type.


The following code snippet will show you how upload file in import data from it and save it to SQL Server.

In C#

// if you have Excel 2007 uncomment this line of code
//  string excelConnectionString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",path);

string ExcelContentType = "application/";
string Excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
if (FileUpload1.HasFile)
//Check the Content Type of the file
if(FileUpload1.PostedFile.ContentType==ExcelContentType || FileUpload1.PostedFile.ContentType==Excel2010ContentType)
//Save file path
string path = string.Concat(Server.MapPath("~/TempFiles/"), FileUpload1.FileName);
//Save File as Temp then you can delete it if you want
//string path = @"C:\Users\Johnney\Desktop\ExcelData.xls";
//For Office Excel 2010  please take a look to the followng link
string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
OleDbCommand command = new OleDbCommand
("Select * FROM [Sheet1$]", connection);


// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())

// SQL Server Connection String
string sqlConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=ExcelDB;Integrated Security=True";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
bulkCopy.DestinationTableName = "Employee";
Label1.Text = "The data has been exported succefuly from Excel to SQL";

catch (Exception ex)
Label1.Text = ex.Message;


'if you have Excel 2007 uncomment this line of code
'  string excelConnectionString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",path);
'Define the content type
Dim ExcelContentType As String = "application/"
Dim Excel2010ContentType As String = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
If FileUpload1.HasFile Then
If FileUpload1.PostedFile.ContentType = ExcelContentType Or FileUpload1.PostedFile.ContentType = Excel2010ContentType Then
'Save file path
Dim path As String = String.Concat(Server.MapPath("~/TempFiles/"), FileUpload1.FileName)
'Save File as Temp then you can delete it if you want
'For Office Excel 2010  please take a look to the followng link
Dim excelConnectionString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path)

' Create Connection to Excel Workbook
Using connection As New OleDbConnection(excelConnectionString)

Dim Command As OleDbCommand = New OleDbCommand("Select * FROM [Sheet1$]", connection)


'Create DbDataReader to Data Worksheet
Using reader As DbDataReader = Command.ExecuteReader()

' SQL Server Connection String
Dim sqlConnectionString As String = "Data Source=.\sqlexpress;Initial Catalog=ExcelDB;Integrated Security=True"

' Bulk Copy to SQL Server
Using bulkCopy As New SqlBulkCopy(sqlConnectionString)

bulkCopy.DestinationTableName = "Employee"
Label1.Text = "The data has been exported succefuly from Excel to SQL"
End Using
End Using
End Using
Catch ex As Exception
Label1.Text = ex.Message
End Try
End If
End If

Run and test Application

after following the above steps you can run web and click on the browse then select Excel file then click import to save Excel file data on SQL Server side.

Download the sample

You can download code from the following link ( Please do not forget to rate it)


I hope you find this sample useful and i will be happy to answer your questions.


1 Comment

Posted by on March 20, 2012 in ASP.NET, C#, VB.NET


Tags: , , , ,