Im currently building a project that lets users import Excel files via a web interface (built), which saves the file to the server (built), and then imports the data into the SQL Database on the server depending on a few of the user options (not built).
Im not familiar with SQL database tools within VS at all so I have been fumbling around for the better part of two days just trying to get everything set up. Im pretty sure I need to use BulkCopy, but Im not quite sure how to use it and I can't seem to find specific examples that explain it pertaining to my specific application.
So in my App_Data folder I have an .mdf title "Device Database." In that database I have three tables: "Galaxy Nexus", "Hercules" , and "Ruby"
I am trying to import four cells from each imported excel sheet to their respective tables.
I would like to import cell(2,2) to column1 in the table, cell(2,3) to column2, cell(3,2) to column3 and cell(1,1) to column4.
The code I am trying to accomplish this with is:
Dim ExcelContentType As String = "application/vnd.ms-excel"
Dim Excel2010ContentType As String = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Dim excelConnectionString As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", SavedFile)
Using connection As New OleDbConnection(excelConnectionString)
Dim Command As OleDbCommand = New OleDbCommand("Select * FROM [Sheet1$]", connection)
connection.Open()
Using reader As DbDataReader = Command.ExecuteReader()
Dim sqlConnectionString As String = "Data Source=.\sqlexpress;Initial Catalog=ExcelDB;Integrated Security=True"
Using bulkCopy As New SqlBulkCopy(sqlConnectionString)
bulkCopy.DestinationTableName = DropDown1.SelectedItem.ToString
bulkCopy.WriteToServer(reader)
End Using
End Using
End Using
Where I am having trouble is, I do not know how to select certain cells from the excel sheet to import and I do not know how to copy those cells to specific columns in the specified table.
Any and all help is always appreciated. Thanks, Zach