Importing/Uploading Data From Excel Datasheets to SQL Server Database

Hi,

I am currently creating a web application with VB.NET and SQL Server 2000, which displays a record of glossaries from an SQL Server Database. I have completed the adding of individual glossaries from manual user input using web froms and such.

However, I am trying to implement a function whereby the user is able to upload an excel datasheet to my application or server(not sure how this works). My application will then be able to extract the data from the datasheet and write it into one of the tables in my SQL Server Database.

Any help in this matter will be greatly appreciated as I do not have much of an idea how do I go about to start coding this function.

I suggest you take alook at creating a connection to excel.

Here is some code to get you started

Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & SaveLocation & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")

conn.Open()

Dim da As New OleDb.OleDbDataAdapter("SELECT * From [Sheet1$]", conn)
da.Fill(MyDataTable)

Hi I understand that the above mentioned method allows me to extract the data from an excel sheet whish is located on the server's hard drive. Am i right about this? I have searched the web and found numerous codes on how to extract excel data from an excel file on the server's hard drive.

However, my situation here is that the user from my website will use a html <input type="file"/> tag type of input to browse his/her hard drive and upload his/her excel file to my server. Is this possible using vb.net and sql server 2000?

Give this a try

(filetimesheet is the name of the htmlinput file control)

VB Code:

Dim fn As String = "[" & Date.Now.ToString.Replace("/", "").Replace(":", "").Replace(" ", "-") & "]" & System.IO.Path.GetFileName(fileTimeSheet.PostedFile.FileName)
 
            Dim SaveLocation As String = Server.MapPath("Data") & "\" & fn
 
            Try
                fileTimeSheet.PostedFile.SaveAs(SaveLocation)
            Catch Ex As Exception
                ErrorMsg(Ex)
                LabelStatus = "An internal error has occoured. Unable to save file"
                Exit Sub
            End Try

Thanks for the reply guys. I've managed to solve it.