In this article I will explain with an example, how to import Excel data to SQL Server Database using Stored Procedure in ASP.Net using C# and VB.Net.
The Excel file will be first uploaded and copied to a folder on Server. Then using Stored Procedure and OLEDB, the data from Excel file will be read and imported into SQL Server Database Table.
Database
I have created a simple table named tblPersons whose schema is shown below.
![Import Excel data to SQL Server Database using Stored Procedure in ASP.Net]()
Note: You can download the database table SQL by clicking the download link below.
Download SQL file
Stored Procedures for importing Excel data
Excel 97 – 2003 Format
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spx_ImportFromExcel03
@SheetName varchar(20),
@FilePath varchar(100),
@HDR varchar(3),
@TableName varchar(50)
AS
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
GO
Excel 2007 and higher format
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spx_ImportFromExcel07
@SheetName varchar(20),
@FilePath varchar(100),
@HDR varchar(3),
@TableName varchar(50)
AS
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
GO
Connection String for Excel 2003 and Excel 2007 or higher formats
The Connection Strings for the Excel files of both 2003 and 2007 or higher formats have been specified in the Web.Config file.
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
HTML Markup
The HTML Markup consists of ASP.Net FileUpload control and a Button to trigger the file upload process.
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button Text="Upload" OnClick="Upload" runat="server" />
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration
Import Excel data to SQL Server Database using Stored Procedure
On the click of the button the Excel file is first uploaded and then saved inside a folder named Files.
Then based on the extension of the Excel file i.e. xls or xlsx, the appropriate connection sting is fetched from the Web.Config file.
Then a connection is established with the Excel file and the name of the first sheet is read into a variable.
Note: I am considering all Excel files with the first row as the Header Row containing the names of the columns, you can set HDR=’No’ if your excel file does not have a Header Row.
Then based on the extension of the Excel file i.e. xls or xlsx, the appropriate Stored Procedure is called and the following parameters are passed.
SheetName – The name of the Excel Sheet to be read.
FilePath – The complete Path of the Excel file.
HDR – Value YES if your Excel file has Header Row else NO.
TableName – Name of the Table in SQL Server Database.
C#
protected void Upload(object sender, EventArgs e)
{
//Upload and save the File.
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string storedProc = string.Empty;
string sheet1 = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03.
storedProc = "spx_ImportFromExcel03";
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher.
storedProc = "spx_ImportFromExcel07";
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
//Read the Sheet Name.
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
excel_con.Close();
}
//Call the Stored Procedure to import Excel data in Table.
string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(storedProc, con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SheetName", sheet1);
cmd.Parameters.AddWithValue("@FilePath", excelPath);
cmd.Parameters.AddWithValue("@HDR", "YES");
cmd.Parameters.AddWithValue("@TableName", "tblPersons");
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
VB.Net
Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)
'Upload and save the File.
Dim excelPath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
FileUpload1.SaveAs(excelPath)
Dim conString As String = String.Empty
Dim storedProc As String = String.Empty
Dim sheet1 As String = String.Empty
Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
Select Case extension
Case ".xls" 'Excel 97-03.
storedProc = "spx_ImportFromExcel03"
conString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
Case ".xlsx" 'Excel 07 or higher.
storedProc = "spx_ImportFromExcel07"
conString = ConfigurationManager.ConnectionStrings("Excel07+ConString").ConnectionString
End Select
'Read the Sheet Name.
conString = String.Format(conString, excelPath)
Using excel_con As OleDbConnection = New OleDbConnection(conString)
excel_con.Open()
sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString()
excel_con.Close()
End Using
'Call the Stored Procedure to import Excel data in Table.
Dim constr As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(storedProc, con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@SheetName", sheet1)
cmd.Parameters.AddWithValue("@FilePath", excelPath)
cmd.Parameters.AddWithValue("@HDR", "YES")
cmd.Parameters.AddWithValue("@TableName", "tblPersons")
cmd.Connection = con
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
End Sub
Screenshots
Excel File
![Import Excel data to SQL Server Database using Stored Procedure in ASP.Net]()
Table containing the data from the Excel file
![Import Excel data to SQL Server Database using Stored Procedure in ASP.Net]()
![Import Excel data to SQL Server Database using Stored Procedure in ASP.Net]()
Downloads
