A Cool Site for learning different

Monday, December 10, 2012

ASP Tutorials and Code 1

Extract data from Excel Spreadsheet using ASP

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


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;


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.


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") & "")

'Close the recordset/connection

Set RS = Nothing

To test the code, point your browser to 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:





More Tutorials at http://www.NabeelAkhtar.NET

Tutorial Discussion Forums: http://Forum.NabeelAkhtar.NET