Need to upload .xls file to SQL Server table via ASP.NET app


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 10 of 10

Thread: Need to upload .xls file to SQL Server table via ASP.NET app

  1. #1
    Join Date
    Aug 2005
    Posts
    79

    Unhappy Need to upload .xls file to SQL Server table via ASP.NET app

    Here's what I have to do:
    1. Allow user to locate a .xls file on their machine
    2. Upload this .xls data into an existing table on a remote SQL Server

    I can pull the file from my local machine to another directory on the local machien, but can't figure out have to configure the saveas() to save on the remote db server.

    It seems you have to save the the db server first on the hard drive, then you can insert the .xls file data into the table.

    Here's my code so far that works to save on the local machine to another directory on that local machine:

    Dim getmyFile As HttpPostedFile = myfile.PostedFile
    If IsNothing(getmyFile) Then
    Label2.Text = "Please select a file to upload"
    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
    sCon1.Open()
    Dim strSQL As String
    Dim err As Integer
    strSQL = "Insert into ActivityTest Select * FROM OPENROWSET"
    strSQL &= "('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;"
    strSQL &= "HDR = YES ','SELECT * FROM [Sheet1$]')"
    Label3.Text = strSQL.ToString()
    Dim cmd As New SqlCommand(strSQL, sCon1)
    Try
    cmd.ExecuteNonQuery()
    err = "Select @@Error"
    If err <> 0 Then
    Label4.Text = err.ToString()
    Else
    Label4.Text = "No Error...line 91!"
    End If
    Catch ex As Exception
    Label2.Text = "Line 82 Error Updating Table: "
    Label2.Text &= ex.Message
    Finally
    sCon1.Close()
    End Try
    End If

    Any books that you know of to help with this topic, since I'm sure I'll be doing more of it in the future, will be apppreciated too!

    Thanks for the help in advance!!!!

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    You don't need to physically save the file to the hard drive first. See the following:

    http://SteveOrr.net/Articles/EasyUploads.aspx
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Aug 2005
    Posts
    79
    I tried the article, and have the following in the button click event to Upload:
    Dim iLength As Integer = CType(myfile.PostedFile.InputStream.Length, Integer)
    If iLength = 0 Then Exit Sub 'not a valid file
    Dim sContentType As String = myfile.PostedFile.ContentType
    Dim sFileName As String, i As Integer
    Dim bytContent As Byte()
    ReDim bytContent(iLength) 'byte array, set to file size

    'strip the path off the filename
    i = InStrRev(myfile.PostedFile.FileName.Trim, "\")
    If i = 0 Then
    sFileName = myfile.PostedFile.FileName.Trim
    Else
    sFileName = Right(myfile.PostedFile.FileName.Trim, Len(myfile.PostedFile.FileName.Trim) - i)
    End If

    Try
    sCon1.Open()
    myfile.PostedFile.InputStream.Read(bytContent, 0, iLength)
    With cmdInsertAttachment
    .Parameters("@FileName").Value = sFileName
    .Parameters("@FileSize").Value = iLength
    .Parameters("@FileData").Value = bytContent
    .Parameters("@ContentType").Value = sContentType
    .ExecuteNonQuery()
    End With
    Catch ex As Exception
    'Handle your database error here
    sCon1.Close()
    End Try
    Response.Redirect(Request.Url.ToString) 'Refresh page

    ******************************
    I'm not seeing anything in the table. Do I have to do something with the Parameter Collection for the Insert Statement? The value spot is left blank, and the name looks like it should be: FileName, FileSize, FileData.........do these need to be @FileName, @FileSize, @FileData...?

  4. #4
    Join Date
    Aug 2005
    Posts
    79
    Thanks Paul!

    I got the .xls file over there to this new table, now.........how do I pull the data out of the .xls file and insert the rows into the SQL Server table.

    Thanks!

  5. #5
    Join Date
    Dec 2003
    Posts
    2,750
    OK, I'm not sure if I'm clear on something. Do you need to import the actual data from the Excel Workbook, save the file to the database, or both?

    If you need to import the data then yes you will need to save the Excel Workbook as a file. There is no point saving the Workbook to the database if all you need is the data from the Workbook.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  6. #6
    Join Date
    Aug 2005
    Posts
    79
    Paul,

    All I need to do is take the rows / data from the .xls file and insert them into the database table. So, if the table has 5 rows as of now, and the .xls file has 10 rows, once I have the user click the "Upload" button, I want 15 rows in the table.

    This one's kinda stumpin' me.

    Thanks!

  7. #7
    Join Date
    Dec 2003
    Posts
    2,750
    OK, so looking back at your first message it appears you have code to upload to a file and then perform the insert. At what point are you stuck or having a problem?
    Last edited by pclement; 09-23-2005 at 09:19 AM.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  8. #8
    Join Date
    Jun 2008
    Posts
    1
    ok, so 3 years later...

    The question is: Can you read the rows/data from the .xls file WITHOUT writing the file to the disc? Perhaps from a buffer/memory? then you can either save the stream into the DB or discard it - all the while without needing filesystem permissions. ???

  9. #9
    Join Date
    Oct 2008
    Posts
    142

    Use SSIS if uploading to sql server 2005

    Hi,

    2 cases:

    1. If your backend is sql server 2000, you should create a dts package and call it from your asp.net web page to put records from an excel file to sql server 2000

    2. if your backend is sql server 2005, you should the same, but with a SSIS package

    Tonci Korsano

  10. #10
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by Arcadian
    Can you read the rows/data from the .xls file WITHOUT writing the file to the disc?
    The file already exists, so there would be no need to recreate it. It would seem to me that you could use VB.NET to read the rows you want into a datareader and then write the contents of the reader off to a database table.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. query tuning
    By dhaya in forum Database
    Replies: 11
    Last Post: 08-25-2003, 06:24 PM
  2. How would u do it (design question) ?
    By Joe in forum Database
    Replies: 17
    Last Post: 04-04-2003, 05:55 PM
  3. Syntax Error Updating SQL Server Table
    By Ralf in forum Database
    Replies: 26
    Last Post: 08-08-2002, 09:58 AM
  4. Access to SQL server
    By Nate in forum Database
    Replies: 29
    Last Post: 05-09-2001, 11:04 AM
  5. Replies: 1
    Last Post: 03-06-2001, 09:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center