Wednesday, August 18, 2010

How to import an Microsoft Excel file to database table using BulkCopy

It is a common scenario that we need to import excel files to database tables. This article demonstrate how to copy a excel file to a database table using BulkCopy. There are two scenarios in this case.
  • Copy the excel file in to server direcotry (upload file) while uploading the file it is vital to validate the file type. For detail demonstration of how to validate a file using regular expressions, please refer this article, which is not included this snippet for the clarity.
  • Process the file.
Download Source Files
    Database table

    Microsoft Excel sheet

    <%@ Page Language="C#" %>
    <html xmlns="">
    <head id="Head1" runat="server">
        <script runat="server">
            protected void Import(object sender, EventArgs e)
                String path = Server.MapPath("~/Data/");
                    if (this.fuFile.HasFile)
                        string fileName = this.fuFile.FileName;
                        string fullName = Path.Combine(path, fileName);
                        if (File.Exists(fullName)) File.Delete(fullName);
                        StringBuilder sb = new StringBuilder(string.Empty);
                        using (SqlConnection con = new SqlConnection(
                            SqlCommand cmd = new SqlCommand("SELECT Id FROM Student", con);
                            SqlDataReader reader = cmd.ExecuteReader();
                            while (reader.Read())
                                sb.AppendFormat("{0},", reader.GetInt32(0).ToString());
                        string ids = string.Empty;
                        if (!string.IsNullOrEmpty(sb.ToString())) ids = sb.ToString().Remove(sb.Length - 1, 1);
                        string excelConnectionString = string.Format(
                                @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; 
                                Extended Properties='Excel 8.0;HDR=YES;'", fullName);
                        using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
                            OleDbCommand ocmd = new OleDbCommand(
                                string.Format("SELECT * FROM [Results$]{0}"string.IsNullOrEmpty(ids) ? 
                                string.Empty : string.Format(" WHERE StudentId NOT IN ({0})", ids)), excelConnection);
                            OleDbDataReader dReader;
                            dReader = ocmd.ExecuteReader();
                            SqlBulkCopy sqlBulk = new SqlBulkCopy(
                            sqlBulk.DestinationTableName = "Student";
                    throw new Exception("Import Faild");
        <form id="form1" runat="server">
            <asp:FileUpload runat="server" ID="fuFile" />
            <asp:Button runat="server" ID="btnImport" OnClick="Import" Text="Import" />

    No comments:

    iPhone Launch Screen Sizes

    iPhone Portrait iOS 8 Retina HT 5.5 = 1242 X 2208 Retna HD 4.7 = 750 X 1134 iPhone Landscape iOS 8 Retina HD 5.5  2208 X 1242 iPho...