Upload .xls file to SQL Server via ASP.NET app

[bubberz]

I got so far as to take a file from my local machine to another directory on my C: drive:

Dim getmyFile As HttpPostedFile = myfile.PostedFile
If IsNothing(getmyFile) Then
Label2.Text = "Please select a file to upload"
Label2.Text = "Line 78"
Else
If getmyFile.ContentLength = 0 Then
Label2.Text = "Cannot upload zero length file"
Else
Dim ServerFileName As String = Path.GetFileName(myfile.PostedFile.FileName)
getmyFile.SaveAs("C:\TestSaving\" & ServerFileName)
Label2.Text = "Successful upload to C:\TestSaving\" & ServerFileName
End If
End If

...............now, what I need to do is take an .xls file and allow users to upload that into a specific table.
Any suggestions or samples/articles are more than welcome!

Thanks!

 

[BluJag]

Hi bubberz - I would like to allow my users to upload an xls file to a table too, but so far haven't thought of a way of doing it.

But, one solution is to get your users to save their spreadsheet as a tab delimited text file, then upload that to a directory on the server. Then, open the file and parse it, adding each record to your table one row at a time.

I've done this for one of my sites and it works fine, but, it does mean that the users file must be set out in the correct order so as to ensure the correct data goes into the corresponding column of your table.

Here's the code. By the way, I use a standard HTML fileinput control for the user to browse to his .txt file.

'1. Uploads text file+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dim FileName As String
Dim Msg As String
Dim disc_FileName As String


With fileInput
If .PostedFile.FileName = "" Then
lblFileName.Text = "Please browse to a .txt file"
Exit Sub
End If

If Right(LCase(.PostedFile.FileName), 3) <> "txt" Then
lblFileName.Text = "You may only upload .txt file"
Exit Sub
End If


FileName = "test" & ".txt"
FileName = viewstate("Member_id") & "_" & FileName

disc_FileName = Server.MapPath(".\uploads\") & LCase(FileName)
.PostedFile.SaveAs(disc_FileName)
End With

'2. Now parse file and enter into DB+++++++++++++++++++++++++++++++++++++++++++++

Dim OSR As System.IO.StreamReader
Dim FileRow() As String

Try

Dim NP As New testrig.Helpers.members

OSR = New System.IO.StreamReader(disc_FileName)
While OSR.Peek() > -1

Try
FileRow = OSR.ReadLine().Split(vbTab)
Catch ex As Exception

End Try




With NP
.Surname = FileRow(0)
.Firstname = FileRow(1)
.Email = FileRow(2)
.Mobile1 = FileRow(3)
.Mobile2 = FileRow(4)
' now add to db
AddMember(NP) 'subroutine which adds line of data to database table
End With
End While

Catch bex As Exception

Finally
If Not OSR Is Nothing Then OSR.Close()

End Try

Cheers

PS this is the HTML code for the fileinput box which goes in your .aspx page

<INPUT id="fileInput" type="file" size="40" name="fileInput" runat="server">