RSS

Import data from Excel to SQL Server

20 Mar

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

image

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

image

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

image

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/vnd.ms-excel";
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)
{
try
{
//Save file path
string path = string.Concat(Server.MapPath("~/TempFiles/"), FileUpload1.FileName);
//Save File as Temp then you can delete it if you want
FileUpload1.SaveAs(path);
//string path = @"C:\Users\Johnney\Desktop\ExcelData.xls";
//For Office Excel 2010  please take a look to the followng link  http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/0f03c2de-3ee2-475f-b6a2-f4efb97de302/#ae1e6748-297d-4c6e-8f1e-8108f438e62e
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);

connection.Open();

// 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";
bulkCopy.WriteToServer(dr);
Label1.Text = "The data has been exported succefuly from Excel to SQL";
}
}
}
}

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

in VB.NET

'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/vnd.ms-excel"
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
Try
'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
FileUpload1.SaveAs(path)
'For Office Excel 2010  please take a look to the followng link  http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/0f03c2de-3ee2-475f-b6a2-f4efb97de302/#ae1e6748-297d-4c6e-8f1e-8108f438e62e
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)

connection.Open()

'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"
bulkCopy.WriteToServer(reader)
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)

http://code.msdn.microsoft.com/Imoprt-Data-from-Excel-to-705ecfcd

References

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

Regards.

 
1 Comment

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

 

Tags: , , , ,

One response to “Import data from Excel to SQL Server

  1. daryl eaton

    April 26, 2012 at 9:50 pm

    thank you so much! This will save me hours. Great job, thanks for sharing.

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: