Import Excel XLS cell data into ASP form

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
Who is Participating?
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
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
CraigBFGAuthor Commented:
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?

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
CraigBFGAuthor Commented:
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)

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....
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.
I'd be interested to know if they have it working.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.