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.

eg
c:\inetpub\wwwroot\mywebsite\imports\MyExcel.xls
MySheet1 - Cell A1 or (1,1) --> MyFormField1
MySheet2 - Cell A1 or (1,1) --> MyFormField2
CraigBFGAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kevp75Commented:
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
      conn.execute(sqlToUpdate)
      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
      rs.MoveNext
Wend

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?

Thanks.
kevp75Commented:
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
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

kevp75Commented:
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:http://www.portalfanatic.com/temp/test16.asp

<style type="text/css">
td {
      font-family: Verdana, Arial, Helvetica, sans-serif;
      font-size: xx-small;
}
</style>
<%
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>Title&nbsp;</td>"&_
                                             "<td>Address&nbsp;</td>"&_
                                             "<td>City&nbsp;</td>"&_
                                             "<td>Region&nbsp;</td>"&_
                                             "<td>P. Code&nbsp;</td>"&_
                                             "<td>Country&nbsp;</td>"&_
                                             "<td>Phone&nbsp;</td>"&_
                                             "<td>Fax&nbsp;</td>"&_
                                             "</tr>")'writing out my version of the header column
                  do'start the loop
                        response.write("<tr>"&_
                                             "<td>"&rs("customerid")&"&nbsp;</td>"&_
                                             "<td>"&rs("companyName")&"&nbsp;</td>"&_
                                             "<td>"&rs("contactName")&"&nbsp;</td>"&_
                                             "<td>"&rs("contactTitle")&"&nbsp;</td>"&_
                                             "<td>"&rs("address")&"&nbsp;</td>"&_
                                             "<td>"&rs("city")&"&nbsp;</td>"&_
                                             "<td>"&rs("region")&"&nbsp;</td>"&_
                                             "<td>"&rs("postalcode")&"&nbsp;</td>"&_
                                             "<td>"&rs("country")&"&nbsp;</td>"&_
                                             "<td>"&rs("phone")&"&nbsp;</td>"&_
                                             "<td>"&rs("fax")&"&nbsp;</td>"&_
                                             "</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
            else
                  response.write("There are no records.")
            end if
            'Clean up
      set rs=nothing
oConn.Close
%>

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

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

Thanks.
C.
kevp75Commented:
np.
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....
kevp75Commented:
SWEET!
I didn't think it would work, but it does.  Have a look at http://www.portalfanatic.com/temp/test16.asp 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.
kevp75Commented:
I'd be interested to know if they have it working.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.