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 like:

Step:2 Create a Sql table in database like:

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

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

<asp:Button ID="btnImport" Font-Bold="true" ForeColor="White"

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();
           }
         }
      }
   }
}





and the result will be be like as: