current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite 
protected void btnup_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        if (System.IO.Path.GetExtension(FileUpload1.FileName) == ".xls" || System.IO.Path.GetExtension(FileUpload1.FileName) == ".xlsx")
        {
            FileUpload1.SaveAs(Server.MapPath("~/Excal/sample.xlsx"));
        }
    }
}
protected void Button2_Click(object sender, EventArgs e)
{ 
   string sSourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\sample.xlsx; Extended Properties=""Excel 12.0;HDR=YES;""";
    string sDestConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;

    OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
    using (sSourceConnection)
    {
        string sql = string.Format("Select [ID],[Name],[Designation] FROM [{0}]", "sample$");
        OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
        sSourceConnection.Open();
        using (OleDbDataReader dr = command.ExecuteReader())
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
            {
                bulkCopy.DestinationTableName = "rr";
                //You can mannualy set the column mapping by the following way.
                //bulkCopy.ColumnMappings.Add("MSISDN", "MSISDN");
                bulkCopy.WriteToServer(dr);
            }
        }
    }
    lblmsg.Text = "Record update";
}
share|improve this question
1  
and what is the problem with this? –  codingbiz Oct 8 '12 at 9:34
add comment

1 Answer 1

up vote 1 down vote

I have done that before few day. Below code works for me ..

   try
        {

           string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                               "Data Source=" + Path of File + ";" +
                              @"Extended Properties=""Excel 12.0 Xml;HDR=Yes""";

            //Create Connection to Excel work book
            OleDbConnection excelConnection =
            new OleDbConnection(excelConnectionString);

            //Create OleDbCommand to fetch data from Excel
            OleDbCommand cmd = new OleDbCommand
            ("Select * from [Sheet1$]",
            excelConnection);

            excelConnection.Open();

            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();

            SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
            sqlBulk.DestinationTableName = "ExcelTable";  // write your table name

            //sqlBulk.ColumnMappings.Add("ID", "ID");
            //sqlBulk.ColumnMappings.Add("Name", "Name");
            sqlBulk.WriteToServer(dReader);


        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.