Introduction

In many time you will find yourself with some Excel file need to be saved or Export to SQL Server,I know it possible by using SQL Server Import and Export Wizard.But this depend on business Scenario itself ,so one  of the scenario is creating web application or windows application that read Excel file and Move it to SQL SERVER Database,so this sample assume that you have one of those scenarios.

Sample Demo

image

As you can see from the above image the sample contain the page which byself contain three controls

  1. FileUpload Control to allow you browse and upload excel file
  2. Button to Import data from excel and export it to SQL Server
  3. Label to show the message about uploading status

Building the Sample

Before run and test the sample you need to follow the next steps.

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

Description

This sample work as I mentioned above by ask user to upload Excel file (*.xsl,*.xslx) and then check the content type of that file then open excel file and save it's data to SQL Server Database. 

 

Visual BasicC#
Edit|Remove
'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

Source Code Files

More Information

For more information about this topic you can ask me here