Import excel data to SQL Server using ASP.NET

SQL bulk copy class provides a lots advantage to developers by providing efficient mechanism of importing data in few lines of code. Here is an example of transferring data from Excel 2007 to SQL Server via ASP.NET using SQL bulk copy class.

Idea behind the code below is to take file, that is located in users local drive, and create a copy of that file in a server folder(under app_data folder) then it query Excel file to select specific columns (in my case here CompanyName and Phone) and read data do bulkcopy using SQL bulk copy class.

1)      Create a folder named “tmpUpload” inside App_Data folder (As app_data folder can not be browsed using directory browsing)

2)     Create a FileUpload,Button and a Label control on your page.

 

Fileupload control displays a text box control and a browse button which helps users to locate specific file.

Button control to raise click event.

Label control to display exceptions or other messages.

 

<%@ Page Language=”C#” AutoEventWireup=”true”  CodeFile=”Default.aspx.cs” Inherits=”_Default” %>

DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd“>

xmlns=”http://www.w3.org/1999/xhtml“>
<head runat=”server”>
    <title></title>
</head>
<body>
    <form id=”form1′ runat=”server”>
    <div>
        FileUpload ID=”FileUpload1′ runat=”server” />    
        <asp:Button ID=”Button1′ runat=”server” Text=”Button” onclick=”Button1_Click” />
    </div>
  
    <asp:Label ID=”Label1′ runat=”server” Text=”Label”></asp:Label>
    </form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Common;

 

public partial class _Default : System.Web.UI.Page
{
    private string saveLocation;
    protected void Page_Load(object sender, EventArgs e)
    {

    }

protected void Button1_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string tempFileName = FileUpload1.FileName;
            string tempFileLocation = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["FileLocation"]);
            saveLocation = Path.Combine(tempFileLocation, tempFileName);

            FileUpload1.SaveAs(saveLocation);
            if (File.Exists(saveLocation))
            {
                string strExcelConnection = string.Format(@”Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;”, saveLocation);
                try
                {
                    using (OleDbConnection conncetion= new OleDbConnection(strExcelConnection))
                    {
                        OleDbCommand command = new OleDbCommand(“SELECT CompanyName,Phone FROM [Sheet1$]“,conncetion);
                        conncetion.Open();
                       
                        using (DbDataReader dr=command.ExecuteReader())
                        {
                            string sqlConncetionString = ConfigurationManager.ConnectionStrings["NorthWindConnectionString"].ConnectionString;
                           
                            using (SqlBulkCopy bulkCopy=new SqlBulkCopy(sqlConncetionString))
                            {

                                bulkCopy.DestinationTableName = “tblUploadTest”;
                                bulkCopy.WriteToServer(dr);
                            }
                           
                        }
                       
                       
                    }
                }
                catch (Exception ex)
                {
                    Label1.Text = ex.ToString();
                }
            }
        }
    }

}

WebConfig settings:

1) Add a connection string to Northwind database.

2) Add Following before connection string:

 

 

<appSettings>
    <add key=”FileLocation” value=”~/App_Data/tmpUpload”/>
</appSettings>

 

Happy Coding!!!

 

 

Tags: , ,

ASP.NET