Upload excel file content to SQl server using SQL bulk copy method
This code shows how to upload excel file content to SQL Server
protected void btnImport_Click(object sender, EventArgs e)
{
lblError.Visible = false;
string xlsFile = fileImport.FileName; //fileImport is the file upload control
if (xlsFile == "" || xlsFile.EndsWith(".xls") != true)
{
lblError.Text = "Please select an Excel File";
lblError.Visible = true;
return;
}
string targetFileName = MapPath("~/_OrgData/Excel/" + xlsFile);
fileImport.PostedFile.SaveAs(targetFileName);
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + targetFileName + ";Extended Properties='Excel 4.0;HDR=YES;'";
OleDbConnection xlsConn = new OleDbConnection(excelConnectionString);
xlsConn.Open();
try
{
OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$] WHERE Username <> NULL", xlsConn);
DbDataReader dr = command.ExecuteReader();
string sqlConnectionString = TAData.Common.GetConnectionString();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "tblPeopleTemp";
bulkCopy.ColumnMappings.Add("Username", "UserName");
bulkCopy.ColumnMappings.Add("Password", "Password");
bulkCopy.ColumnMappings.Add("Surname", "LName");
bulkCopy.ColumnMappings.Add("First Name", "FName");
bulkCopy.WriteToServer(dr);
}
}
catch (Exception ex)
{
lblError.Text = "The Excel content is not valid";
lblError.Visible = true;
}
xlsConn.Close();
File.Delete(targetFileName);
}