Tutorials and Code

ActionScript (5)
ASP (6)
ASP.NET (28)
Assembly (1)
C (6)
C# (122)
C++ (33)
CSS (6)
General (4)
HTML (5)
Java (26)
JavaScript (36)
MySQL (1)
Pascal (1)
PHP (51)
Python (3)
Ruby (14)
Silverlight (1)
SQL (7)
VB.NET (3)
Visual Basic (3)

Syndication

My Account

Community Forums

Webmaster Tools

Tech Jobs

Geekpedia » ASP Tutorials » 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.

On Thursday, September 29th 2005 at 10:06 AM

By Nabeel Akhtar (View Profile)

(Rated 4.6 with 37 votes)

Contextual Ads

More ASP Resources

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:

http://www.webdeveloper.com/forum/showthread.php?t=77596

http://www.carlprothman.net/Default.aspx?tabid=81

http://support.microsoft.com/kb/195951

MSDN

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

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

Digg It!

Del.icio.us Reddit

StumbleIt

Newsvine

Furl

BlinkList

Rate this tutorial

Current Comments

by shiva on Thursday, October 13th 2005 at 05:13 PM

Hi Nabeel Akhtar

Thanks much, its really good stuff.
have a nice time my friend

by Akmal on Thursday, November 24th 2005 at 02:45 AM

hi

its very helpful but still some information is needed

like while changin xls sheet its not retrivng dat
giving error

by Nabeel Akhtar on Saturday, November 26th 2005 at 09:12 AM

Akmal, I could not understand what the problem is... Could you give us more details on the error?

by trigun on Monday, November 28th 2005 at 07:40 AM

Thanks' Been looking for somthing like this for a while.

by radhika on Friday, December 2nd 2005 at 01:24 AM

the above code works preety well...but is there a way to extract data from excel without selecting a range.can there be an ASP coding to directly extract excel data without selecting range

by Olayinka on Thursday, January 12th 2006 at 01:05 PM

This program is good i love it because it is what i have been thinking of how to do for a very long time.

by zahid on Friday, January 13th 2006 at 06:14 AM

i implement this code but i dont know why i m receiving error eg.
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'range'. Make sure the object exists and that you spell its name and the path name correctly.

could u plz send me the detail

by Nabeel Akhtar on Friday, January 13th 2006 at 09:33 AM

Zahid, It looks like that the code could not find the range (my_range) in the excel spreadsheet. Are you sure you defined a range and named it "my_range" ?

by madeline on Tuesday, January 17th 2006 at 07:48 PM

Hey the code works fine! But is there a way to read from the excel file without defining the range?

by Nabeel Akhtar on Wednesday, January 18th 2006 at 09:18 AM

madeline, I havn't tried it without a range but im sure there is a way... try googling it... :)

by KALU N. I. on Tuesday, February 21st 2006 at 08:41 AM

I really need this code but it keeps on not recognising the range. Is there another way out? The error is this:
"Microsoft JET Database Engine error '80040e37'
The Microsoft Jet database engine could not find the object 'my_Range'. Make sure the object exists and that you spell its name and the path name correctly". I created range. Pls what could be wrong.

by Nabeel Akhtar on Tuesday, February 21st 2006 at 09:18 AM

try googling it... you'll find many solutions.

by Teddy on Tuesday, May 23rd 2006 at 06:06 AM

how to define cell range by asp automatically ?

by Blake on Monday, June 5th 2006 at 12:44 PM

I have been researching reading from an Excel file. Your site has been extremely useful. However, I what I have uncovered thus far informs me that ODBC does now allow you to read from an Excel file without setting a range. This is problematic for me because I am not the one creating the Excel files. I am dealing with a server/client situation where the client can upload their own Excel file and I process the data within these files. Very similar to your three column set up above. Any alternative suggestion?

by Nabeel Akhtar on Monday, June 5th 2006 at 01:27 PM

use "Select * from [Sheet1$]"
Yes, there is a '$' after Sheet1. (Sheet1 is the name of the spreadsheet)

by Blake on Monday, June 5th 2006 at 05:32 PM

Great! Thanks for your help.

by Bharti Handa on Tuesday, June 27th 2006 at 06:50 AM

\"Microsoft JET Database Engine error \'80040e37\'
The Microsoft Jet database engine could not find the object \'my_Range\'. Make sure the object exists and that you spell its name and the path name correctly\". I created range. Pls what could be wrong.

This Error message is coming because the Excel File is open. We need to close the File before exceuting the code. Save n Close the File, then try it It will work.

by USA Guide on Friday, July 28th 2006 at 06:50 AM

<a href="http://www.50usa.org">USA Guide</a>USA,US Citizenship,American Family,USA Jobs,USA Today,American Flag,USA

Immigration,Immigration Attorney,Visa USA,US Travel

by anshu anurag on Tuesday, November 21st 2006 at 06:19 AM

Used the code.. worked fine .. but for a column which has mixture of datatypes like .. 12DWE456 and 12345 ... The 1st one is mixture and second one is pure number .. The code doesn't show the second one ... what shall be done to fix this ?

by Raj on Friday, February 23rd 2007 at 12:03 PM

Hi Mate,

I run the code i got a error message like this

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver]General error Unable to open registry key \'Temporary (volatile) Jet DSN for process 0xa5c Thread 0x14a4 DBC 0xd163d4 Excel\'.
/MyWeb/read_excel.asp, line 4

any help pls.

thanks
RAJ

by elisa on Tuesday, February 27th 2007 at 11:18 PM

i have the same problem with RAJ, and the solutions are:
1. check the consistency between cell\'s name and your RS\'s name
2. close your excel and dont click at the icon
3. reload or refresh it often

sometimes it will be happen anymore...

by elisa on Tuesday, February 27th 2007 at 11:27 PM

Hi, the code was very very helpful but
i have some question, :
my excel data was 7,153 and 11.88
the display result in ASP was 7153.786 and 1188.41575492341
how can i make the result the same as was shown in the exel file?

tq...

by elisa on Wednesday, February 28th 2007 at 02:04 AM

i've solved the problem
just using the formatnumber() function

btw,
i've got the problem
Error Type:
Provider (0x80004005)
Unspecified error

the error line is: oConn.Open "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790;" &_

how to solve it?
please help me....

by Sajid on Sunday, March 4th 2007 at 05:17 AM

Hello Nabeel,

By using "Select * from [Sheet1$]" command I could access only Sheet1 data. If I want to retrieve the entire sheet name (Whole Excel work book) and data without knowing Sheet name, which SQL commands required for that case. Please extend your cooperation in this case.

by Imam M on Sunday, April 15th 2007 at 11:53 PM

Hi Nabeel Akhtar

Thanks much, its really good stuff.
Can we handle the error when file is open?

As we know, if file is opened there is an error:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver]General error Unable to open registry key \'Temporary (volatile) Jet DSN for process 0xa5c Thread 0x14a4 DBC 0xd163d4 Excel\'.
/MyWeb/read_excel.asp, line 4

I want to show some message like: \"File is opened by some one, please try later\", when file is opened.

Can you help me for it?

by Steve on Monday, April 16th 2007 at 10:54 AM

Very cool. Thanks for the neat Excel trick. For the above, if you want to handle the file-opened error, you will need to do it when you open the connection. You might be able to catch oConn.open when it fails.

by Dewi on Friday, June 29th 2007 at 11:45 PM

Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object \'range\'. Make sure the object exists and that you spell its name and the path name correctly.

by Seyed Khadem on Wednesday, September 26th 2007 at 07:28 PM

This is great! However, I did have some problems. For some reason using the code above I could not read integers or number types!!! strings were fine, any idea what the problem is? I tried converting the column in excel to text, but that didn't seem to have any affect, neither did any type of type casting.

by Seyed Khadem on Wednesday, September 26th 2007 at 07:54 PM

I think figured it out, maybe a better way to do it, if so let me know very intrested. The excel driver doesn't like when text and numbers are stored in the same column, going into excel if I put a ' before the numbers that are mixed with the text column it reads it fine. Entire columns with ints also read fine, but don't need the ' in excel.

Any way to specific this in the code?

by sean on Friday, September 28th 2007 at 07:05 AM

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0xdcc Thread 0x784 DBC 0x1291014 Excel'.

In my case this error was actually caused by the formatting of the file. I copied and pasted into a new workbook (Paste Special - VALUES only). Then it worked fine for me.
Hope this helps some one.

by Wasim Sakharkar on Thursday, October 4th 2007 at 02:30 AM

Hi,

How to find out the Sheet name, if i want to validate the user inputs, OR I want to propmt the user to spacifically enter the some valid sheet name. OR the lenght of Sheet name should be some integer numbar.

by Amit on Wednesday, October 10th 2007 at 03:32 AM

Hey.. thanks a lot!! can u help me to integrate excel with Visual studio 2005?..

by Developer on Wednesday, October 31st 2007 at 12:13 PM

I have been using the code with some success, however on occassion data is not imported correctly as the import feature only seems to support strings of up to 255 characters. Is there any way to resolve this issue?

by Developer on Wednesday, October 31st 2007 at 12:21 PM

by Ajit on Tuesday, December 4th 2007 at 07:15 AM

hi

its very helpful but still some information is needed
Giving following error

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver]General error Unable to open registry key \\\'Temporary (volatile) Jet DSN for process 0xa0c Thread 0x81c DBC 0x188f024 Excel\\\'
Please help me

by Harry on Monday, December 24th 2007 at 03:26 AM

Thanks man. It is really helpful. However i get the following error on the
conn.Open connstr string

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver]General error Unable to open registry key \'Temporary (volatile) Jet DSN for process 0x1314 Thread 0x1744 DBC 0x12bf024 Excel\'.
details_xls2.asp, line 18

Can any body assist me on this? Thanks

by Me on Monday, January 14th 2008 at 11:48 PM

Try looking here Harry et al: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q295297

by Very Good on Thursday, July 10th 2008 at 02:26 PM

Wondeful.

This is an excellent piece of info. Helped me implement a solution where I was stuck up in the middle.

thanks a lot.