Read from Excel in ASP

Warning! This post is either deprecated or outdated.

I always had a hard time finding correct documentation on the old ASP (active server pages) language and I needed it one more time in the last few days to create a script that read from an excel file.

As I believe that it might be handy for a handful of people around the world (is there actually anybody else still developing in ASP these days?), I hereby share my little piece of code with you guys.

Any suggestions or comments are always welcome.

In my example we read from an excel file that has 1 small table that has 1000 lines and columns from A until G.
The first row contains all the column names.

'initialize variables
Dim objConn, strSQL
Dim x

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
 "Excel 8.0; DBQ=" & Server.MapPath("filename.xls") & "; "
strSQL = "SELECT * FROM A1:G1000"

Response.Write("<table border=""1"">")
Response.Write("<tr>")
'write all columnNames
For x=0 To objRS.Fields.Count-1
 Response.Write("<th>" & objRS.Fields(x).Name & "</th>")
Next
Response.Write("</tr>")

Do Until objRS.EOF

' write as much columns as there are in your excel file
Response.Write("<td>" & objRS.Fields(0).Value & "</td>")
Response.Write("<td>" & objRS.Fields(1).Value & "</td>")
Response.Write("<td>" & objRS.Fields(2).Value & "</td>")
Response.Write("<td>" & objRS.Fields(3).Value & "</td>")
Response.Write("<td>" & objRS.Fields(4).Value & "</td>")
Response.Write("<td>" & objRS.Fields(5).Value & "</td>")
Response.Write("<td>" & objRS.Fields(6).Value & "</td>")

objRS.Close

Response.Write("</table>")

Set objRS=Nothing

By the way, if you found a typo, please fork and edit this post. Thank you so much! This post is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Comments

Fork me on GitHub