This tutorial focuses on extracting data from a Microsoft Excel Spreadsheet. This tutorial will walk you through step-by-step to setup the spreadsheet and how to write the code in ASP to get the required data.
Click Here to view this tutorial in FlashPaper 2 format.
You can store data in a Microsoft Excel Spreadsheet and then use ASP to extract the infromation. The spreadsheet acts like a database and you can use standard SQL statements to query the data. The process is fairly simple and I will break it down into three steps:
STEP-1: Create an Excel Spreadsheet
STEP-2: Define named ranges in the spreadsheet
STEP-3: Write ASP code read the file
STEP-1:
Lets get started with the spreadsheet. You MUST have Microsoft Excel installed on your computer to create and Excel Spreadsheet. I have created a folder called “excel” under C:\Inetpub\wwwroot\
and thats where I will create/save my Excel spreadsheet.
a) Open Excel and create a spreadsheet that looks like this:
In this sheet the SR, NAME and EMAIL are the column names. When we query the data from this spreadsheet, we can limit the results by selecting only one or two columns e.g. SELECT NAME FROM my_range;
STEP-2:
Now that we have created a spreadsheet, its time to define a named range within Excel that will be treated as a table for our SQL statement. To create a named range, select all the fields that have data in them, with the column names, then go to INSERT > Name > Define… > Type in my_range > Press OK > Save your file in C:\inetpub\wwwroot\excel\excel.xls
I have saved my excel file as excel.xls.
STEP-3:
Now that we have the excel file and the named range in place, we can start working on the ASP code. Here is the code to read this excel file using a DSN-LESS approach
<%
' Set Connection Params
Set oConn = Server.CreateObject("ADODB.connection")
oConn.Open "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790;" &_
"DBQ=c:\Inetpub\wwwroot\excel\excel.xls;" &_
"DefaultDir = C:\Inetpub\wwwroot\excel\"
Set RS=Server.CreateObject("ADODB.recordset")
' Write the SQL Query
RS.open "SELECT * FROM my_range", oConn
do until RS.EOF
Response.Write ( RS("NAME") & " -- " & RS("EMAIL") & "")
RS.movenext
Loop
'Close the recordset/connection
RS.Close
oConn.Close
Set RS = Nothing
%>
To test the code, point your browser to http://127.0.0.1/excel/read_excel.asp and you will see the following output:
You can customize the out anyway you want and the possibilities are endless 🙂
Enjoy and Happy ASP’ing.
Following are some links that were very helpful when I was digging up this information: https://webdeveloper.com/community/75114-forum-within-a-website/
Update: We would like to inform our readers that the website “carlprothman.net,” which has been referenced in our content, is no longer operational as of 28/11/2023. We regret any inconvenience this may cause and are in the process of updating our articles with alternative sources. We appreciate your understanding and patience.
http://www.carlprothman.net/Default.aspx?tabid=81
http://support.microsoft.com/kb/195951
Update: Please note that the website NabeelAkhtar.NET, previously referenced in this article, is no longer active as of 28/11/2023. We apologize for any inconvenience and are working to update the content with alternative resources. Thank you for your understanding.
web.archive.org link: More Tutorials at http://www.NabeelAkhtar.NET
web.archive.org link: Tutorial Discussion Forums: http://Forum.NabeelAkhtar.NET
