C# – how to import the data from excel sheet into sql server table

asp.netcimport-from-excelsql

Hi. I want to import the excel sheet data to SQL server table,for that I wrote code like as below. The sheet is uploading into folder,but the data is not imported to table,can anyone tell me please where is the mistake….

public partial class upload2 : System.Web.UI.Page
    {
        private string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                PopulateDatabaseTables();
            }
        }

        private void PopulateDatabaseTables()
        {
            string tableName = string.Empty;
            string sql = "SELECT *, name AS table_name " +
                " FROM sys.tables WHERE Type = 'U' ORDER BY table_name";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (DataTable table = new DataTable())
                {
                    conn.Open();
                    using (SqlDataAdapter dAd = new SqlDataAdapter(sql, conn))
                    {
                        dAd.Fill(table);
                    }
                    ListBox1.DataSource = table;
                    ListBox1.DataBind();
                }
            }
        }

        protected void ImportNow_Click(object sender, EventArgs e)
        {
            if (ListBox1.SelectedValue == "")
            {
                lblMessage.ForeColor = Color.Red;
                lblMessage.Text = "Please select table in which you want to import data from excel sheet";
            }
            else if ((fileuploadExcel.FileName != ""))
            {
                string extension = Path.GetExtension(fileuploadExcel.PostedFile.FileName);

                string excelConnectionString;
                SqlConnection conn = new SqlConnection(connStr);
                string tableName = ListBox1.SelectedValue;
               // string path = fileuploadExcel.PostedFile.FileName;
                string path = Server.MapPath("~/fileuploadExcel/" + fileuploadExcel.FileName);
                fileuploadExcel.SaveAs(path);
                Response.Write("path=" + path);
                return;
                //Create connection string to Excel work book
                if (extension == ".xlsx")
                {
                    excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=+ path +
                                                          ;Extended Properties=Excel 8.0;Persist Security Info=False";
                }
                else
                {
                    excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;sData Source= + path +
                                                         ;Extended Properties=Excel 12.0;Persist Security Info=False";
                }

                //Create Connection to Excel work book
                SqlConnection excelConnection = new SqlConnection(excelConnectionString);
                //Create OleDbCommand to fetch data from Excel             
                conn.Open();
                SqlCommand comm = new SqlCommand("truncate table " + tableName, conn);
                SqlCommand identityChange = conn.CreateCommand();
                identityChange.CommandText = "SET IDENTITY_INSERT " + tableName + " ON";
                SqlCommand cmd = new SqlCommand("Select * from [Sheet1$]", excelConnection);
                excelConnection.Open();
                SqlDataReader dReader;
                dReader = cmd.ExecuteReader();
                identityChange.ExecuteNonQuery();
                SqlBulkCopy sqlBulk = new SqlBulkCopy(connStr);
                //Give your Destination table name
                sqlBulk.DestinationTableName = tableName;
                sqlBulk.WriteToServer(dReader);
                excelConnection.Close();
                conn.Close();
                lblMessage.ForeColor = Color.Green;
                lblMessage.Text = "Import into table <b>" + tableName + "</b> successful!<br />";
            }
            else
            {
                lblMessage.ForeColor = Color.Red;
                lblMessage.Text = "Please first upload (Select) excel file.";
            }
        }

        protected void viewdata_Click(object sender, EventArgs e)
        {
            BindData();
        }

        private void BindData()
        {
            try
            {
                if (ListBox1.SelectedValue == "")
                {
                    lblMessage.ForeColor = Color.Red;
                    lblMessage.Text = "Please select table for which you want to view data in Gridview";
                }
                else
                {
                    string tableName = ListBox1.SelectedValue;
                    SqlConnection conn = new SqlConnection(connStr);
                    SqlDataAdapter sda = new SqlDataAdapter("select * from " + tableName, conn);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    gvdetails.DataSource = ds;
                    gvdetails.DataBind();
                }
            }
            catch (DataException de)
            {
                lblMessage.Text = de.Message;
                lblMessage.ForeColor = System.Drawing.Color.Red;
            }
        }

        protected void gvdetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gvdetails.PageIndex = e.NewPageIndex;
            BindData();
        }
    }
}

Best Answer

excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=+ path + ;Extended Properties=Excel 8.0;Persist Security Info=False";

here you have directly used path , modify excelConnectionString as follows

excelConnectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}
                                                      ;Extended Properties=Excel 8.0;Persist Security Info=False",path);