Import Excel XLS cell data into ASP form

Posted on 2006-04-12
Last Modified: 2008-03-10
I'm trying to import specific cell data/values from an uploaded XLS into a web form - or alternatively directly into SQL.

The file upload routine works ok, so that's not an issue, but the excel file name will be different for each record, so the routine needs to handle that.

I've tried the MS option of using named ranges, but whilst it works, its a bit clunky. I need to be able to use cell ref's (A1 etc) or (2,1).

What are my options ??

Many thanks.

MySheet1 - Cell A1 or (1,1) --> MyFormField1
MySheet2 - Cell A1 or (1,1) --> MyFormField2
Question by:CraigBFG
    LVL 25

    Expert Comment

    call the records from the excell sheet using ADO

    then all you would need to do is loop through the records and call the value from the cells you want, for example:
    rs.MoveFirst 'This tells it to move to the first row
    While Not rs.EOF 'This starts the loop through the rows
          sqlToUpdate = "UPDATE table SET (field1 = '"&rs.Fields.Item(0).Value&"', field2 = '"&rs.Fields.Item(1).Value&"') WHERE filteringby = " & rs.Fields.Item(1).Value
          'rs.Fields.Item(0).Value = the 1st delimited column
          'rs.Fields.Item(1).Value = the 2nd delimited column
          'rs.Fields.Item(x).Value, change x to the next column# 0 is always the 1st column, and increment by one
          'so if your text file is "value1", "value2", "value3" then Item(0) would be "value1", etc
          response.write("Data updated!")
          response.write sqlToUpdate 'just comment out this line so it doesn't display the data posted, or put in a response.redirect("/somepage.asp") to redirect, or whatever else you would need to display after the record is updated

    the above code loops through each row, and grabs the values of the cells, please see the comments for the cell values

    Author Comment

    Thanks for that. So, this routine updates the SQL db directly with the individual cell values? You also say delimited column, so does that mean it has to be a csv or will this work with a STD Excel file?

    Any possibility of a complete example, say using Northwind?

    LVL 25

    Expert Comment

    yes.  This example updates a table based on the individual cell values.  I believe you can do the same for an STD Excel file.  I will have to test though.

    I'll get back to you in a little while
    LVL 25

    Accepted Solution

    ok.  Here is a simplified display of the Excel sheet.  I Exported the table 'Customers' from the Northwind DB to an Excel file.  Please note that this is just a simplified example, there are much better ways to loop through a recodset than the one I use below, but you will get the general idea.

    You can see a working example of it at:

    <style type="text/css">
    td {
          font-family: Verdana, Arial, Helvetica, sans-serif;
          font-size: xx-small;
    Dim strConn: strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\to\your\excel\file\test.xls;Extended Properties=""Excel 8.0;"""'This is the connection string, make sure to change the Data Cource to match where your Excel file is
    Dim oConn: set oConn=createobject("ADODB.Connection") 'Set connection for ADODB
    oConn.Open strConn 'Open the connection
          set rs=oConn.execute("SELECT * FROM [$Customers]") 'Select SQL statement from the worksheet Customers
                if not rs.eof then 'if recordset is not at the end of file
                      rs.movefirst'move to the first recordset (this happens to be the titles of the columns)
                            response.write("<table width='100%' cellpadding='2' cellspacing='0' border='1'>"&_
                                                 "<tr style='background-color:#CCC;font-weight:bold;'>"&_
                                                 "<td>Cust ID&nbsp;</td>"&_
                                                 "<td>Company Name&nbsp;</td>"&_
                                                 "<td>Contact Name&nbsp;</td>"&_
                                                 "<td>P. Code&nbsp;</td>"&_
                                                 "</tr>")'writing out my version of the header column
                      do'start the loop
                                                 "</tr>")'write out each recordset
                                                 'you can also do an INSERT, DELETE, UPDATE query here if needed.
                      rs.movenext'moves to next recordset
                      loop until rs.eof'do the loop
                      response.write("There are no records.")
                end if
                'Clean up
          set rs=nothing
    LVL 25

    Expert Comment


    Author Comment

    Sorry, I think there may be a bit of confusion here.

    I need to update the table FROM specific Excel cell references. I see that your example is using the XLS file as a datasource to display.

    UPDATE tblMyTable SET myField1 = MyXLS.Sheet1.Cell(a1), myField2 = MyXLS.Sheet2.(A1)

    LVL 25

    Expert Comment

    then use the first example, just make your connection string like:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\to\your\excel\file\test.xls;Extended Properties=""Excel 8.0;"""

    you will have to experiment a little bit to get the cell correct, but it is similar to the 1st example I posted.  Just remember that 0 is always the 1st reference.

    actually.......I may have a function that may help you better than that.  Let me see if I can get it to work with Excel as the DB....
    LVL 25

    Expert Comment

    I didn't think it would work, but it does.  Have a look at and read the note at the top of the page.  If that is what you are talking about I'll post the code for that page here.
    LVL 25

    Expert Comment

    I'd be interested to know if they have it working.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
    I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now