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); }