Update database table column values with new Excel column values

Question

I have a table in the database.

id : Name : Category
--------------------
1  : jake : admin

I have used sqlbulkcopy for inserting new rows into the database table from an excel sheet containing two columns 'Name' and 'Category'.

Now, I have another requirement and that is updating the values of the column Name and Category from the same Excel sheet but with newly updated values. My question is, is this possible? I just need a way to update the values of the column. I went through this reference.

update SQl table from values in excel

but I don't know how to implement it.

Any help or suggestion will be greatly appreciate.

Thanks.

The code for uploading and importing excel file using sqlbulkcopy is below.

protected void Button1_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        FileUpload1.SaveAs(Server.MapPath("~/" + Path.GetFileName(FileUpload1.FileName)));
        string file = Server.MapPath("~/"+Path.GetFileName(FileUpload1.FileName));
        string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Excel 8.0; Extended Properties=HDR=Yes;IMEX=1; Data Source=" + file + ";";

        using (OleDbConnection olecon = new OleDbConnection(constr))
        {
            OleDbCommand olecmd = new OleDbCommand("select Name, Category FROM [Sheet1$]", olecon);
            olecon.Open();

            using (DbDataReader dbrdr = olecmd.ExecuteReader())
            {
                string sqlcon = "Data Source=matty2011-PC\\SQLEXPRESS;Initial Catalog=mydb; Integrated Security=True";

                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(sqlcon))
                {
                    sqlbulkcopy.ColumnMappings.Add(0, 1);
                    sqlbulkcopy.ColumnMappings.Add(1, 2);
                    sqlbulkcopy.DestinationTableName = "exceldata";
                    sqlbulkcopy.WriteToServer(dbrdr);
                }
            }
        }
    }
    else
    {
        Response.Write("Please select a file for upload.!"); return;
    }
}

jack.mike.info jack.mike.info · Accepted Answer · 2011-05-03T18:56:03

hi

if possible use SSIS to solve Simply by DTS

note : SSIS DTS will append the existing data

Update database table column values with new Excel column values

1 Answers