Dim excelPath As String = Server.MapPath("~/Uploads/") + Path.GetFileName(FileUpload1.PostedFile.FileName) FileUpload1.SaveAs(excelPath) Dim connString As String = String.Empty Dim extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName) Select Case extension Case ".xls" 'Excel 97-03 connString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString Exit Select Case ".xlsx" 'Excel 07 or higher connString = ConfigurationManager.ConnectionStrings("Excel07+ConString").ConnectionString Exit Select End Select connString = String.Format(connString, excelPath) Using excel_con As New OleDbConnection(connString) excel_con.Open() Dim sheet1 As String = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing).Rows(0)("TABLE_NAME").ToString() Dim dtExcelData As New DataTable() Using oda As New OleDbDataAdapter((Convert.ToString("SELECT * FROM [") & sheet1) + "]", excel_con) oda.Fill(dtExcelData) End Using excel_con.Close() Dim conString As String = ConfigurationManager.ConnectionStrings("SQLDbConnection").ConnectionString Using con As New SqlConnection(conString) Using sqlBulkCopy As New SqlBulkCopy(con) 'Set the database table name sqlBulkCopy.DestinationTableName = "dbo.TESTTBL" con.Open() sqlBulkCopy.WriteToServer(dtExcelData) con.Close() End Using End Using End Using
Improve question
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)