Extract data from Excel Spreadsheet using ASP

Extract data from Excel Spreadsheet using ASP
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

MSDN

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

Nathan Pakovskie is an esteemed senior developer and educator in the tech community, best known for his contributions to Geekpedia.com. With a passion for coding and a knack for simplifying complex tech concepts, Nathan has authored several popular tutorials on C# programming, ranging from basic operations to advanced coding techniques. His articles, often characterized by clarity and precision, serve as invaluable resources for both novice and experienced programmers. Beyond his technical expertise, Nathan is an advocate for continuous learning and enjoys exploring emerging technologies in AI and software development. When he’s not coding or writing, Nathan engages in mentoring upcoming developers, emphasizing the importance of both technical skills and creative problem-solving in the ever-evolving world of technology. Specialties: C# Programming, Technical Writing, Software Development, AI Technologies, Educational Outreach

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top