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


    Code
    <%@ Page Language="C#" %>
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head id="Head1" runat="server">
        <script runat="server">
            protected void Import(object sender, EventArgs e)
            {
                String path = Server.MapPath("~/Data/");
                try
                {
                    if (this.fuFile.HasFile)
                    {
                        string fileName = this.fuFile.FileName;
                        string fullName = Path.Combine(path, fileName);
                        if (File.Exists(fullName)) File.Delete(fullName);
                        this.fuFile.PostedFile.SaveAs(fullName);
                        StringBuilder sb = new StringBuilder(string.Empty);
                        using (SqlConnection con = new SqlConnection(
                            ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
                        {
                            SqlCommand cmd = new SqlCommand("SELECT Id FROM Student", con);
                            con.Open();
                            SqlDataReader reader = cmd.ExecuteReader();
                            while (reader.Read())
                                sb.AppendFormat("{0},", reader.GetInt32(0).ToString());
                            reader.Close();
                            con.Close();
                        }
                        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);
                            excelConnection.Open();
                            OleDbDataReader dReader;
                            dReader = ocmd.ExecuteReader();
                            SqlBulkCopy sqlBulk = new SqlBulkCopy(
                                ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
                            sqlBulk.DestinationTableName = "Student";
                            sqlBulk.ColumnMappings.Add("StudentId""Id");
                            sqlBulk.ColumnMappings.Add("Name""Name");
                            sqlBulk.ColumnMappings.Add("Grade""Grade");
                            sqlBulk.WriteToServer(dReader);
                            dReader.Close();
                            excelConnection.Close();
                        }
     
                    }
                }
                catch
                {
                    throw new Exception("Import Faild");
                }
            }        
        </script>
    </head>
    <body>
        <form id="form1" runat="server">
            <asp:FileUpload runat="server" ID="fuFile" />
            <asp:Button runat="server" ID="btnImport" OnClick="Import" Text="Import" />
        </form>
    </body>
    </html>

    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...