How to import data from Excel to SQL Server 2008 using ASPNet

In one of my previous articles I explained Read and Import Excel Sheet into ASP.Net GridView Control

Here I am explaining how to import Excel Sheet Rows into SQL Server Database table using ADO.Net in an ASP.Net Web Application.

Concept

1. User uploads the Excel File.

2. Based on the extension it is decided whether it is Excel 97 – 2003 or Excel 2007 format.

3. User can select whether the Excel Sheet has header row or not using the Radio Buttons

4. The Excel file is uploaded and then sheets in the Excel workbook are read into a DropDownList.

5. User has to now select the Sheet from the dropdown whose data he wants to import.

6. User has to enter the name of the table which he wants the data to be imported.

7. User presses OK Button and the data is imported into the SQL Server Database table and the user is updated with the status.

Stored Procedures

For this article I have created two stored procedures one to read the Excel 97 – 2003 format and other Excel 2007 format. Though the Microsoft Ace Driver can read both still I have used Jet for Excel 97 - 2003 formats.

Excel 97 – 2003 Format

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE spx_ImportFromExcel03

@SheetName varchar(20),

@FilePath varchar(100),

@HDR varchar(3),

@TableName varchar(50)

BEGIN

DECLARE @SQL nvarchar(1000)

IF OBJECT_ID (@TableName,'U') IS NOT NULL

SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'

ELSE

SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'

SET @SQL = @SQL + '(''Microsoft.Jet.OLEDB.4.0'',''Data Source='

SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 8.0;HDR='

SET @SQL = @SQL + @HDR + ''''''')...['

SET @SQL = @SQL + @SheetName + ']'

EXEC sp_executesql @SQL

END

Excel 2007 Format

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE spx_ImportFromExcel07

@SheetName varchar(20),

@FilePath varchar(100),

@HDR varchar(3),

@TableName varchar(50)

BEGIN

DECLARE @SQL nvarchar(1000)

IF OBJECT_ID (@TableName,'U') IS NOT NULL

SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'

ELSE

SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'

SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='

SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR='

SET @SQL = @SQL + @HDR + ''''''')...['

SET @SQL = @SQL + @SheetName + ']'

EXEC sp_executesql @SQL

END

In the above stored procedures, I have used four input parameters

1.@SheetName - Name of the Excel Sheet to be read.

2.@FilePath - Path of the Excel File

3.@HDR - Indicates whether first row in the excel sheet will be considered as Header row or not.

4.@TableName - The name of the table in which the Excel Sheet data will be transferred if the table is not present it will be created.

When you run the above stored procedure first time you might get the following error message.

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

In order to run the above stored procedure you will need to enable Surface Area Configuration in your SQL Server in the following way

Execute the below four statements one by one in the SQL Server Query Analyzer

sp_configure 'show advanced options', 1

reconfigure

sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure

Also in order to use the Microsoft OLEDB Ace Driver you will need to install the 2007 Office System Driver: Data Connectivity Components which is necessary for Microsoft OLEDB ACE 12.0 driver to work using the link below

2007 Office System Driver: Data Connectivity Components

I faced the following Error while running the OLEDB Ace stored procedure

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 2

Cannot get the column information from OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)".

I have written an article on the solutions for it. You can visit it using the link below

The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)"

Front End Design

Below is the markup of the HTML mark of the asp.net web page. There are 2 panels. First with an upload button, Asp.Net FileUpload control and a label to display the status used to upload the Excel File. Second one with label to display the uploaded Excel file name, DropDownList which contain the names of Sheets of the Excel workbook, an ASP.Net RadioButtonList to capture whether the Sheet has header row and finally two buttons one to import the Excel Sheet rows into the SQL Server Database table and other one to cancel.

<asp:Panel ID="Panel1" runat="server">

<asp:FileUpload ID="FileUpload1" runat="server" />

<asp:Button ID="btnUpload" runat="server" Text="Upload"

OnClick="btnUpload_Click" />

<asp:Label ID="lblMessage" runat="server" Text="" />

asp:Panel>

<asp:Panel ID="Panel2" runat="server" Visible = "false" >

<asp:Label ID="Label5" runat="server" Text="File Name"/>

<asp:Label ID="lblFileName" runat="server" Text=""/>

<asp:Label ID="Label2" runat="server" Text="Select Sheet" />

<asp:DropDownList ID="ddlSheets" runat="server"

AppendDataBoundItems = "true">

asp:DropDownList>

<asp:Label ID="Label3" runat="server" Text="Enter Source Table Name"/>

<asp:TextBox ID="txtTable" runat="server">asp:TextBox>

<asp:Label ID="Label1" runat="server" Text="Has Header Row?" />

<asp:RadioButtonList ID="rbHDR" runat="server">

<asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" >

asp:ListItem>

<asp:ListItem Text = "No" Value = "No">asp:ListItem>

asp:RadioButtonList>

<asp:Button ID="btnSave" runat="server" Text="Save"

OnClick="btnSave_Click" />

<asp:Button ID="btnCancel" runat="server" Text="Cancel"

OnClick="btnCancel_Click" />

asp:Panel>

Namespaces

You will require to import the following namespaces

using System.Data;

using System.Data.OleDb;

using System.IO;

using System.Configuration;

using System.Data.SqlClient;

VB.Net

Imports System.Data

Imports System.Data.OleDb

Imports System.IO

Imports System.Data.SqlClient

Imports System.Configuration

Web.Config Configurations

I have used a AppSettings key FolderPath to store the path of the folder where the uploaded excel file will be stored.

There are three connections strings as described below

1. Excel03ConString - Conncection String for Excel 97 – 2003 formats

2. Excel07ConString - Connection String for Excel 2007 format

3. conString - Connection String for the SQL Server 2005 Express Database.

appSettings>

<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;

Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>

<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;

Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>

<add name="conString" connectionString="Data Source=.\SQLEXPRESS;

database=ExcelImport;Integrated Security=true"/>

connectionStrings>

Uploading the excel Workbook

User has to first upload the Excel File whose data he wants to transfer to the SQL Server database onto the server using ASP.Net FileUpload Control and a Upload button. The code snippet for the Upload Button is given below.

protected void btnUpload_Click(object sender, EventArgs e)

{

if (FileUpload1.HasFile)

{

string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

string FilePath = Server.MapPath(FolderPath + FileName);

FileUpload1.SaveAs(FilePath);

GetExcelSheets(FilePath, Extension, "Yes");

}

VB.Net

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs)

If FileUpload1.HasFile Then

Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)

Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)

Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")

Dim FilePath As String = Server.MapPath(FolderPath + FileName)

FileUpload1.SaveAs(FilePath)

GetExcelSheets(FilePath, Extension, "Yes")

End If

End Sub

The above code snippet simply uploads the Excel Workbook into the path defined in the Web.Config key. The figure below displays the User Interface for uploading the Excel File.

User Inteface : Uploading the Excel WorkBook

You will notice GetExcelSheets function being called on the click of Upload Button. As the name suggests the function reads the names of all the sheets present in the Excel Workbook and binds the result to DropDownList. The complete function is given below

private void GetExcelSheets(string FilePath, string Extension, string isHDR)

{

string conStr="";

switch (Extension)

{

case ".xls": //Excel 97-03

conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]

.ConnectionString;

break;

case ".xlsx": //Excel 07

conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]

.ConnectionString;

break;

}

//Get the Sheets in Excel WorkBoo

conStr = String.Format(conStr, FilePath, isHDR);

OleDbConnection connExcel = new OleDbConnection(conStr);

OleDbCommand cmdExcel = new OleDbCommand();

OleDbDataAdapter oda = new OleDbDataAdapter();

cmdExcel.Connection = connExcel;

connExcel.Open();

//Bind the Sheets to DropDownList

ddlSheets.Items.Clear();

ddlSheets.Items.Add(new ListItem("--Select Sheet--", ""));

ddlSheets.DataSource=connExcel

.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

ddlSheets.DataTextField = "TABLE_NAME";

ddlSheets.DataValueField = "TABLE_NAME";

ddlSheets.DataBind();

connExcel.Close();

txtTable.Text = "";

lblFileName.Text = Path.GetFileName(FilePath);

Panel2.Visible = true;

Panel1.Visible = false;

}

VB.Net

Private Sub GetExcelSheets(ByVal FilePath As String, ByVal Extension As String, ByVal isHDR As String)

Dim conStr As String = ""

Select Case Extension

Case ".xls"

'Excel 97-03

conStr = ConfigurationManager.ConnectionStrings("Excel03ConString") _

.ConnectionString

Exit Select

Case ".xlsx"

'Excel 07

conStr = ConfigurationManager.ConnectionStrings("Excel07ConString") _

.ConnectionString

Exit Select

End Select

'Get the Sheets in Excel WorkBoo

conStr = String.Format(conStr, FilePath, isHDR)

Dim connExcel As New OleDbConnection(conStr)

Dim cmdExcel As New OleDbCommand()

Dim oda As New OleDbDataAdapter()

cmdExcel.Connection = connExcel

connExcel.Open()

'Bind the Sheets to DropDownList

ddlSheets.Items.Clear()

ddlSheets.Items.Add(New ListItem("--Select Sheet--", ""))

ddlSheets.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid _

.Tables, Nothing)

ddlSheets.DataTextField = "TABLE_NAME"

ddlSheets.DataValueField = "TABLE_NAME"

ddlSheets.DataBind()

connExcel.Close()

txtTable.Text = ""

lblFileName.Text = Path.GetFileName(FilePath)

Panel2.Visible = True

Panel1.Visible = False

End Sub

Importing the rows from the excel sheet to the database table

Once the Sheets are filed in the DropDownList the user interface looks as like below.

User Inteface : Selection of Excel Sheets and the destination database table

As you can see the excel file name is displayed along with all the sheets in the DropDownList. The RadioButtonList captures the information about the header row in the Excel Sheet. Then once the user presses Save button all the rows are read into the database table which the user has entered in the textbox if the table is not present it will be created.

The code snippet for the Save button is give below

protected void btnSave_Click(object sender, EventArgs e)

{

string FileName = lblFileName.Text;

string Extension = Path.GetExtension(FileName);

string FolderPath = Server.MapPath (ConfigurationManager

.AppSettings["FolderPath"]);

string CommandText = "";

switch (Extension)

{

case ".xls": //Excel 97-03

CommandText = "spx_ImportFromExcel03";

break;

case ".xlsx": //Excel 07

CommandText = "spx_ImportFromExcel07";

break;

}

//Read Excel Sheet using Stored Procedure

//And import the data into Database Table

String strConnString = ConfigurationManager

.ConnectionStrings["conString"].ConnectionString;

SqlConnection con = new SqlConnection(strConnString);

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = CommandText;

cmd.Parameters.Add("@SheetName", SqlDbType.VarChar).Value =

ddlSheets.SelectedItem.Text;

cmd.Parameters.Add("@FilePath", SqlDbType.VarChar).Value =

FolderPath + FileName;

cmd.Parameters.Add("@HDR", SqlDbType.VarChar).Value =

rbHDR.SelectedItem.Text;

cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value =

txtTable.Text;

cmd.Connection = con;

try

{

con.Open();

object count = cmd.ExecuteNonQuery();

lblMessage.ForeColor = System.Drawing.Color.Green;

lblMessage.Text = count.ToString() + " records inserted.";

}

catch (Exception ex)

{

lblMessage.ForeColor = System.Drawing.Color.Red;

lblMessage.Text = ex.Message;

}

finally

{

con.Close();

con.Dispose();

Panel1.Visible = true;

Panel2.Visible = false;

}

VB.Net

Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs)

Dim FileName As String = lblFileName.Text

Dim Extension As String = Path.GetExtension(FileName)

Dim FolderPath As String = Server.MapPath( _

ConfigurationManager.AppSettings("FolderPath"))

Dim CommandText As String = ""

Select Case Extension

Case ".xls"

'Excel 97-03

CommandText = "spx_ImportFromExcel03"

Exit Select

Case ".xlsx"

'Excel 07

CommandText = "spx_ImportFromExcel07"

Exit Select

End Select

'Read Excel Sheet using Stored Procedure

'And import the data into Database Table

Dim strConnString As String = ConfigurationManager _

.ConnectionStrings("conString").ConnectionString

Dim con As New SqlConnection(strConnString)

Dim cmd As New SqlCommand()

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = CommandText

cmd.Parameters.Add("@SheetName", SqlDbType.VarChar).Value = _

ddlSheets.SelectedItem.Text

cmd.Parameters.Add("@FilePath", SqlDbType.VarChar).Value = _

FolderPath + FileName

cmd.Parameters.Add("@HDR", SqlDbType.VarChar).Value = _

rbHDR.SelectedItem.Text

cmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value = _

txtTable.Text

cmd.Connection = con

Try

con.Open()

Dim count As Object = cmd.ExecuteNonQuery()

lblMessage.ForeColor = System.Drawing.Color.Green

lblMessage.Text = count.ToString() & " records inserted."

Catch ex As Exception

lblMessage.ForeColor = System.Drawing.Color.Red

lblMessage.Text = ex.Message

Finally

con.Close()

con.Dispose()

Panel1.Visible = True

Panel2.Visible = False

End Try

End Sub

The above code snippet simply calls the respective stored procedure based on the extension and the status is displayed to the user as shown in figure below

User Interface : Status notified to the user after completion of Excel Sheet import

This completes the article. You can download the code in VB.Net and C# along with the sample database backup and SQL scripts using the link below

Download Code (154.91 kb)

How to import data from Excel to SQL Server 2008 using ASP.Net

Related

Ask Question

Company

Explore

Follow us

Contact Us