How to Upload Excel file into sqlserver asp.net

How to Import Excel File into SQL Server using SQLBULK in ASP.net
This example explains how to upload excel file, read Excel file data, save Excel file data and import into SQL Server using SQLBULK in ASP.Net.

Step:1 Create a Excel file:


Step:2 Create a Sql table in database:


Step:3 Now, add the code in "Default.aspx"



<asp:fileupload id="fupUpload" runat="server" xmlns:asp="#unknown">

<asp:button id="btnImport" font-bold="true" forecolor="White" xmlns:asp="#unknown">

BackColor="#136671" Height="23px" runat="server" Text="Import Excel Data"
onclick="btnImport_Click" />


Step:4 Add the code in "Default.aspx.cs"

Add these NameSpace

using System.IO;
using System.Data.OleDb;
using System.Data;


Write the code in Click Event of Import Button

protected void btnImport_Click(object sender, EventArgs e)
{
string strFilepPath;
DataSet ds = new DataSet();
string strConnection = ConfigurationManager.ConnectionStrings
["connectionString"].ConnectionString;
if (fupUpload.HasFile)
{
try
{
FileInfo fi = new FileInfo(fupUpload.PostedFile.FileName);
string ext = fi.Extension;
if (ext == ".xls" || ext == ".xlsx")
{
string filename = Path.GetFullPath(fupUpload.PostedFile.FileName);
string DirectoryPath = Server.MapPath("~/UploadExcelFile//");
strFilepPath = DirectoryPath + fupUpload.FileName;
Directory.CreateDirectory(DirectoryPath);
fupUpload.SaveAs(strFilepPath);
string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ strFilepPath + ";Extended Properties=\"Excel 12.0
Xml;HDR=YES;IMEX=1\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
DeleteExcelFile(fupUpload.FileName); // Delete File Log
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection,
SqlBulkCopyOptions.KeepIdentity);
sqlBulk.DestinationTableName = "Table_1";
sqlBulk.WriteToServer(ds.Tables[0]);
conn.Close();
sqlBulk.Close();
ScriptManager.RegisterStartupScript(Page, GetType(), "script1",
"alert('Excel file successfully imported into DB');", true);
return;
}
else
{
ScriptManager.RegisterStartupScript(Page, GetType(), "script1",
"alert('Please upload excel file only');", true);
return;
}
}
catch (Exception ex)
{
DeleteExcelFile(fupUpload.FileName);
ScriptManager.RegisterStartupScript(Page, GetType(), "script1",
"alert('error occured: " + ex.Message.ToString() + "');", true);
return;
}
}
else
{
ScriptManager.RegisterStartupScript(Page, GetType(), "script1",
"alert('Please upload excel file');", true);
return;
}
}

protected void DeleteExcelFile(string Name)
{
if (Directory.Exists(Request.PhysicalApplicationPath +
"UploadExcelFile\\"))
{
string[] logList = Directory.GetFiles(Request.PhysicalApplicationPath
+ "UploadExcelFile\\", "*.xls");
foreach (string log in logList)
{
FileInfo logInfo = new FileInfo(log);
string logInfoName = logInfo.Name.Substring(0,
logInfo.Name.LastIndexOf('.'));
if (logInfoName.Length >= Name.Length)
{
if (Name.Equals(logInfoName.Substring(0, Name.Length)))
{
logInfo.Delete();
}
}
}
}
}


Kishor Makwana
SoftWare Engineer
Insight Softech
www.insightsoftech.com