asked on

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

kevp75

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

CraigBFG

ASKER

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.

kevp75

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

ASKER CERTIFIED SOLUTION

kevp75

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kevp75

here's a couple links that will also help you out...

http://support.microsoft.com/default.aspx?scid=kb;en-us;278973
http://www.hotscripts.com/ASP/Scripts_and_Components/Excel_and_ASP/
http://www.codetoad.com/asp_excel.asp

HTAH

CraigBFG

ASKER

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.

kevp75

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....

kevp75

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.

kevp75

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