• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

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
0
CraigBFG
Asked:
CraigBFG
  • 7
  • 2
1 Solution
 
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
0
 
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.
0
 
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
%>
0
 
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.
0
 
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....
0
 
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.
0
 
kevp75Commented:
I'd be interested to know if they have it working.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now