locked
EXCEL to SQL RRS feed

  • Question

  • User1973741707 posted

    i want to upload Excel file using fileupload control and i want to store excel data in SQL server database ...plz help

    Monday, October 28, 2013 2:54 PM

All replies

  • User1508394307 posted

    Example:

    public static void ExportToSql(string filename,string savepath)
            {
                
                string connectionString = "";
                savepath = savepath + "\\" + filename;
                if (System.IO.Path.GetExtension(filename) == ".xls")
                {
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savepath + ";Extended Properties=Excel 8.0";
                }
                else if (System.IO.Path.GetExtension(filename) == ".xlsx")
                {
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +savepath  + "; Extended Properties=Excel 12.0;";
                }
    
                using (OleDbConnection con = new OleDbConnection(connectionString ))
                {
                    
                    con.Open();
    
                    OleDbCommand com = new OleDbCommand("Select * from [Sheet1$]", con);
    
                    OleDbDataReader dr = com.ExecuteReader();
                    
                    using (SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["OnlineExamConnectionString"].ConnectionString))
                    {
    
                        sqlcon.Open();
    
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
                        {
    
                            //bulkCopy.ColumnMappings.Add("[SrNo]", "CollID");
                           
                            //bulkCopy.ColumnMappings.Add("[College Name]", "Name");
    
                            //bulkCopy.ColumnMappings.Add("Address", "Address");
    
                            //bulkCopy.ColumnMappings.Add("Phone", "Phone");
    
                            bulkCopy.DestinationTableName = "tblCollege";
    
                            bulkCopy.WriteToServer(dr);
    
                        }
    
                    }
    
                    dr.Close();
    
                    dr.Dispose();
                }
    
    
            }

    Source: http://forums.asp.net/t/1744063.aspx

    Similar threads

    http://forums.asp.net/t/1845014.aspx
    http://forums.asp.net/t/1609319.aspx
    http://forums.asp.net/t/1162069.aspx

    Monday, October 28, 2013 3:49 PM