Import data from Excel to SQL Server using C#.NET

Introduction

In this example  I am going to explain how to import data from Microsoft Excel to SQL Server. And, in the next blog post we will see how to export data from SQL Server back to Microsoft Excel. First, launch Visual studio and create a new project. Give it a name (ie, ImportExcel2SQL).

Create a database in the SQL Server, ExcelDataDB and create a table with the columns that matches to the columns in the Excel file.


CREATE TABLE [dbo].[Books](
[ID] [int] NOT NULL,
[BookName] [varchar](200) NOT NULL,
        [Author] [varchar](200) NOT NULL,
 CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


Drop in a couple of controls from the Toolbox on to the page, FileUpload and a Button. Re-name the Button Text and say "Import".

Default.aspx

<h1>Import data from Excel to SQL server</h1>
<asp:FileUpload ID="FileUpload1" runat="server" />
&nbsp;
<asp:Button ID="Import" runat="server" Text="Import" onclick="Import_Click" />
<br />
<asp:Label ID="Label1" runat="server" Text=""></asp:Label>

Create a temporary folder(eg: TempExcelFiles) in the root of the directory to save the excel file before being imported to the SQL Server. Delete the Excel file in the temporary folder after the data has been imported.

For Microsoft office Excel 2007, please use the following connection string,

string excelConnectionString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",path);

and for other versions of Excel use,

string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

Default.aspx.cs

using System.Configuration;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;


SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Import_Click(object sender, EventArgs e)
    {
        string excelContentType = "application/vnd.ms-excel";
        string excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string contentType = FileUpload1.PostedFile.ContentType;
        if (FileUpload1.HasFile)
        {
            if (contentType == excelContentType || contentType == excel2010ContentType)
            {
                try
                {
                    string path = string.Concat(Server.MapPath("~/TempExcelFiles/"), FileUpload1.FileName);
                    FileUpload1.SaveAs(path);
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
                    using (OleDbConnection connection =
                                 new OleDbConnection(excelConnectionString))
                    {
                        OleDbCommand command = new OleDbCommand
                                ("Select * FROM [Sheet1$]", connection);

                        connection.Open();
                        using (DbDataReader dr = command.ExecuteReader())
                        {
                            using (SqlBulkCopy bulkCopy =
                                       new SqlBulkCopy(sqlcon))
                            {
                                bulkCopy.DestinationTableName = "Books";
                                bulkCopy.WriteToServer(dr);
                                Label1.Text = "The data has been exported successfully from Excel to SQL";
                            }
                        }
                    }
                }

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