C# - How to Uploading an Excel sheet and importing the data into SQL Server database

Import Excel Data to Database Using C#

---

• Import and Export Data From Excel to Database - C#
• C# import Excel to Database
• Import excel data into database using c# | Microsoft ASP Solutions
• Import MS Excel data to SQL Server table using C#
• Office Import Data from Excel to SQL Server sample in C#
• The Best Way To Import Data From Excel To SQL Server via ASP.NET
• Import/Upload Excel Sheet data to Sql Server in C# and Asp.Net
• how to import excel file into mysql database using c#

CS Page Code Below

---

protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUp.HasFile == true)
        {

            //Upload and save the file
            string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUp.PostedFile.FileName);
            FileUp.SaveAs(excelPath);

            string conString = string.Empty;
            string extension = Path.GetExtension(FileUp.PostedFile.FileName);
            switch (extension)
            {
                case ".xls": //Excel 97-03
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07 or higher
                    //conString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 13.0");
                    conString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=Yes;IMEX=1\";", excelPath);
                    //conString = ConfigurationManager.ConnectionStrings["Excel13+ConString"].ConnectionString;
                    break;

            }
            conString = string.Format(conString, excelPath);
            using (OleDbConnection excel_con = new OleDbConnection(conString))
            {
                excel_con.Open();
                string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                
                DataTable dt = new DataTable();

                
                using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
                {                    
                    oda.Fill(dt);
                }
                                              
                excel_con.Close();


                


               
                //replace.("" , )(dt.Rows[0]["Date"])
                string consString = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
                using (SqlConnection con = new SqlConnection(consString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        //Set the database table name
                        
                        sqlBulkCopy.DestinationTableName = "dbo.UserData";

                        //[OPTIONAL]: Map the Excel columns with that of the database table
                        sqlBulkCopy.ColumnMappings.Add("Date", "Bill_Dt");
                        sqlBulkCopy.ColumnMappings.Add("Ref# No#", "Ref_no");
                        sqlBulkCopy.ColumnMappings.Add("Party's Name", "Party_name");
                        sqlBulkCopy.ColumnMappings.Add("Opening Amount", "Op_Amt");
                        sqlBulkCopy.ColumnMappings.Add("Pending Amount", "Pen_Amt");
                        sqlBulkCopy.ColumnMappings.Add("Post-Dated Amount", "PoDt_Amt");
                        sqlBulkCopy.ColumnMappings.Add("Final Balance", "Fi_Bal");
                        sqlBulkCopy.ColumnMappings.Add("Due on", "Due_Dt");
                        sqlBulkCopy.ColumnMappings.Add("Overdue by days", "OverDays");
                        con.Open();
                        sqlBulkCopy.WriteToServer(dt);
                        con.Close();
                    }
                }
            }
        }
    }