Generating Excel File Using SQL Server 2000
 
Published: 21 Jun 2006
Unedited - Community Contributed
Abstract
In this article Aravind demonstrates the creation of a new Excel file using SQL Server 2000. This technique does not require Microsoft Excel(Office) to be installed on the Web Server.
by Aravind Kumar
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 1266227/ 5007

Introduction

I dedicate this article to my brother's daughter Akshaya.

This article will explore the features of SQL Server 2000 for using Microsoft tools, such as MS-Excel and DOS.  Here we extensively use DOS Shell commands and ODBC database object connectivity for Excel.  The main objective of this article is to generate an excel file directly from MS SQL Server 2000.  This will be very useful when we have a huge amount of SQL data output that needs to be transferred to the excel file.  Usually Internet applications will generate an excel file by changing the Page’s Response-Content Type.  This allows users to open it with Internet Explorer and/or saving it in their Hard disk.  This can work well if we generate a fewer number of records.  However, for a large amount of records, it is better to generate the excel file in the web server and download it later.  The other advantage in this method is that we can also stream multiple SQL outputs at a single time into various excel worksheets present in that single downloadable excel file.  In other words, simultaneous SQL Data outputs can be generated to fill into different worksheets of the same excel file.

Template File

Before generating the output in an excel file, we first need to create an excel file (template file). This template file should have the column names of the result set to be generated through the SQL Query.  This is done by simply typing the Column names in the row of a excel sheet, below which the data will be generated by SQL Server 2000 (it is nothing but a Header column).  If we want to generate multiple query sets through the same SQL Query, we need to create multiple excel work sheets according to our result tables inside the template file.  Remember, Excel will have 3 sheets by default.  We can give the template formatting, such as color and background, for a better look and feel for the output file.  This template file will be copied using simple DOS "Copy" command through the stored procedure.

DOS File Copy

Microsoft SQL Server is a not only a powerful database, but also a tool for utilizing other Microsoft utilities like DOS, Office suite and other various software.  We can utilize various DOS internal commands using SQL Server for our needs.  And one good command, which will be used here, is the DOS File Copy command.

We can utilize DOS commands from SQL Server 2000 using the built-in stored procedure "Xp_CmdShell" provided by SQL Server 2000.  This built-in stored procedure is placed by default in the master database.  So, henceforth it is assumed that we will be working in the master database while running this particular command.

By default, only members (i.e. - sa) of the sysadmin fixed server role can execute this extended stored procedure.  You may, however, grant other users permission to execute this stored procedure.

For Example, if we need to delete a file we can run the following command in SQL.

Listing 1

EXEC MASTER..XP_CMDSHELL 'Del d:\sdf.xls'

In the above example we can see that the built-in stored procedure XP_CMDSHELL will be executed by passing the desired dos command.  Here the dos command "Del" has been used to instruct the stored procedure to delete the file in the path specified viz., 'd:\sdf.xls’;.

Using Excel Object

This gives us the template file through DOS File Copy.  Now we need to connect to the Excel file, which we have just copied through the stored procedure, for transferring the data generated by the SQL query from SQL Server to the excel worksheet.  Here we use Microsoft.Jet.OLEDB.4.0 as the database engine for connecting to this excel file.  We can connect to multiple sheets of the same excel file by specifying the worksheet name in the select query string in our stored procedure.

We can simulate the example by saying that the Database is the Excel file and the table is the Sheet1 present inside the Excel file.  So by default, we can copy data from three data sources to three default Sheets present in the excel file.  If we can add more worksheets to the template file, we can obviously get more data tables copied to the excel file.

Using OPENROWSET Command

SQL OpenRowSet command is usually used to connect to the linked server or remote server for fetching data.  It includes all connection information necessary to access remote data from an OLE DB data source.  This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB.

The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name.  The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE or DELETE statement and is subject to the capabilities of the OLE DB provider.  Although the query may return multiple result sets, OPENROWSET returns only the first one.

Listing 2

OPENROWSET ( 'provider_name' , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' } 
     , { [ catalog. ] [ schema. ] object | 'query' } ) 

So, if our need is to insert a data collection into an OLEDB data source (in this case, an Excel File), you have to specify the following:

·         OLEDB version of the SQL Server

·         Excel version and Excel FileName

·         Worksheet you are going to connect for data copying

The above will simply look like the following.

Listing 3

insert into OPENrowset( Provider, ExcelConnectionString, 
'SELECT [Column Names] FROM [SheetN$]''select [Column Names] from TableName'

After the query execution, the select query mentioned after OPENrowset parameters will be inserted into datasheet of the mentioned excel file connected through the OPENrowset function.

Example

We take an example here to generate Authors and Sales data of pubs database into the first two sheets of the excel file.  Here we specify three parameters for the OPENROWSET Function.

·         OLEDB Engine provider as "Microsoft.Jet.OLEDB 4.0"

·         Excel Connectivity string as "Excel 8.0; Database=FileName.xls"

·         Select query of the columns framed in the Excel File

Mentioning the OLEDB Provider and excel destination filename

Listing 4

set @provider = 'Microsoft.Jet.OLEDB.4.0'
set @ExcelString = 'Excel 8.0;Database=' + @fn

Executing the OPENROWSET Command for copying the select contents to Excel sheet

Listing 5

exec('insert into OPENrowset (''' + @provider +
 ''',''' + @ExcelString + ''',''SELECT FirstName, LastName, Phone, Address,
 City, State, Zip FROM [Sheet1$]'') select au_fname as FirstName, au_lname as
 LastName, phone, address, city, State, Zip from authors')
 
exec('insert into OPENrowset (''' + @provider +
 ''',''' + @ExcelString + ''',''SELECT StoreId, OrderNo, OrderDate, Quantity
 FROM [Sheet2$]'') select stor_id as StoreId,Ord_Num as OrderNo,Ord_Date as
 OrderDate,qty as Quantity from sales')

After the execution of the above statements, the results obtained from the select statements mentioned after the each OPENROWSET commands will be copied to the two sheets of the excel file respectively.

Guidelines for running the sample query

Download the sample and unzip it.  Copy the Template.xls excel file to "D:\".  Copy and run the stored procedure from PUBS database as sa.  While running the stored procedure, we can specify a name in which the resultant data will be generated; else it will automatically generate the data in a file called "D:\Test.xls."

Summary

This article is concerned with reducing network traffic while generating big excel files (with multiple excel sheets) through web applications.  It does this by utilizing SQL Server’s unique features.  Generally when we respond the excel data into the web browser, it will take more time to generate huge data and it is hard to generate multiple sheets of data.  I hope the above will be a good solution for you.

Editor's Note: If you are considering third party tools, you may want to consider SoftArtisans OfficeWriter as another alternative for keeping Microsoft Office off the server.



User Comments

Title: Link Server Error   
Name: Shankar
Date: 2010-08-30 3:24:57 AM
Comment:
hi, I am getting link server error
Title: when i close the excel file, then automatically to excel data is save the sql server database   
Name: Buvaneshwaran.J
Date: 2009-04-28 3:53:11 AM
Comment:
when i close the excel file, then automatically to excel data is save the sql server database....

whether it is possible or not... it's possible please send me code....

Please send me my mail:puvaneswaranbe@yahoo.co.in


it's very urgent.....please help me......



Thanks

Buvaneswaran.J
Title: Nice Article   
Name: Ravinder
Date: 2008-04-13 10:46:37 PM
Comment:
Hey Thanks for the great information. I am new to SQL Server, had to to lot of job on servers without excel and this has saved me a lot of time.

Great Job.

Cheers,
Ravinder
Title: File Download - Security Warning   
Name: Srinivas Alwala
Date: 2008-04-03 7:08:50 AM
Comment:
Hi,

While downloading an Excel file in internet explorer, it prompts an file download window with open,save and cancel buttons.
On clicking on open button, it gives an error as "Could not open 'filepathtoopen'".
Pls let me know how to rectify this error.

Regards,
Srinivas Alwala
Email : srinivas.alwala@yahoo.com
Title: Generating Excel File Using SQL Server 2000   
Name: Francis
Date: 2008-04-01 9:39:58 PM
Comment:
I am using SQL 2005 on a 64 bit platform. The 'Microsoft.Jet.OLEDB.4.0' provider is not working producing an error of The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered. I believe this is a 64bit OS issue. However, I have an urgent need to be able to delete and refill Excel files from my SQL code. Any ideas as to how I can manage this with our system?

Thanks
Title: OLE DB error trace   
Name: Karthik
Date: 2008-02-27 2:33:00 AM
Comment:
[OLE/DB provider returned message: Could not find installable ISAM.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Title: numbers are being stored on the sheet as text   
Name: Nat
Date: 2007-12-20 12:32:41 PM
Comment:
PLEASE HELP: The data is arriving in the Excel file but... the numbers are being stored on the sheet as text. Unfortunately the formatting of the TEMPLATE file does not work! Accounting can not use those files for analysis.

Any other ideas???
Title: Re : enable the use of 'Ad Hoc Distributed Queries'   
Name: Author
Date: 2007-12-14 5:19:35 AM
Comment:
SQL Server 2005 is more secured than SQL Server 2000 and by default it blocks Distributed Queries, Shell Command etc. So to enable 'Ad Hoc Distributed Queries', Please run the following commands as Administrator.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Title: enable the use of 'Ad Hoc Distributed Queries'   
Name: Nat
Date: 2007-12-13 10:59:19 AM
Comment:
Please note, on SQL 2005 you may need modify the configuration a little. See below the warning I've got:


Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
Title: How to open Excel file which is already generated??   
Name: Shailesh
Date: 2007-11-21 11:28:55 PM
Comment:
I can't open excel file from asp which is already generated...plzzz help me..

thanx

regards,
Shailesh
Title: MS-Excel file executing but not showing the data   
Name: Abdul Majeed
Date: 2007-11-10 3:29:50 AM
Comment:
My problem is excel file not opening directly, Excel application working fine but not showing file data. if i open excel files Manually it then showing file data,
I have reinstalled excel then also the same problem.

thank and best regards
majeed
Title: how+to+generate+excel+file+using+vbscript+in+asp   
Name: saurabh agrawal
Date: 2007-10-25 3:06:11 AM
Comment:
hello all,

my problem is that, i want to genrate one file in .xls format. the record which is exist in excel sheet ,comes via sql query.
plz give the excet format with correct syntex.
tanks and best regards
saurabh
Title: How to add instance of excelsheet in Sql database   
Name: safi1981
Date: 2007-09-04 2:25:49 PM
Comment:
Hi
This artical is really gud but do u have any idea how us save the instance of excelsheet in sql server database.
Title: How to create Excel file   
Name: Rohith
Date: 2007-08-06 5:11:02 AM
Comment:
i have created excel file using Excel9.0 object but at the client place its 10.0 its giving error as "Cannot create Activex component" pls Help me

bluefoster@rediffmail.com
Title: How to create Excel file   
Name: Rohith
Date: 2007-08-06 5:07:59 AM
Comment:
i have created excel file using Excel9.0 object but at the client place its 10.0 its giving error as "Cannot create Activex component" pls Help me
Title: Query   
Name: Prashant
Date: 2007-06-07 6:30:46 AM
Comment:
Hi,
I have one problem i want to merge 10 excel files in to singel excel,excel file contain images also .
but i have to do all this in my .net code.
is there any way to do that

choudhary_prashant@rediffmail.com
Title: Response to Creation of Excel file without Data   
Name: Rosemary
Date: 2007-05-29 5:44:22 AM
Comment:
It's Excellent Article but I could not get the data in the excell file after running the procedure
Title: Response to the error by Tirthesh Jain   
Name: Chaitu
Date: 2007-05-10 9:25:59 AM
Comment:
Hello friends,

While executing stored procedure
if your Specified excel sheet is open then only you will get the following error

"OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
"
Just Checkout

Regards
Chaitu
Title: Got An error   
Name: Tirthesh Jain
Date: 2007-04-26 3:08:21 AM
Comment:
While running the same Proc I am getting the under written Error Given by SQL Server 2000 and I am using the 2003 Excel version.

"OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
"
Title: Great Script   
Name: Ariest
Date: 2007-04-14 7:42:18 AM
Comment:
Good article Aravind
Title: Nice one   
Name: Rama Krishna
Date: 2007-02-05 12:43:29 AM
Comment:
A good article Mr Aravind. Keep it up.
Title: Error   
Name: Narendra
Date: 2007-01-27 3:28:42 PM
Comment:
I have created the Template of the Table structure, changed the column names and Table name. Run the scripts in Different Database. When I execute the procedure I got the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' supplied invalid metadata for column 'item'. The data type is not supported.
OLE DB error trace [Non-interface error: Column 'item' (ordinal 1) of object 'SELECT item,Warehouse,Quantity FROM [Sheet1$]' reported an unsupported value for DBTYPE of 0].


Please can you help me to solve this error.

My email id is mail2narendra@gmail.com

Thanks in advance.

Regards:
Narendra
Title: Nice One   
Name: Narendra Babu A
Date: 2007-01-27 2:39:21 PM
Comment:
Thanks a lot. It helps me a lot.
Title: Error   
Name: Sreeni
Date: 2007-01-23 7:20:51 AM
Comment:
i got the following error while executing your code for my xl template,

Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' supplied invalid metadata for column 'SNo'. The data type is not supported.

Shall you help me to solve this problem?

Thanks,
Sreeni
Title: Fantastic   
Name: Prajeesh V
Date: 2007-01-18 1:30:19 AM
Comment:
Its really fantastic... Very much helpful to SQL Programmers.


Regards,
Praji
Title: Length of character in excel   
Name: ?
Date: 2007-01-15 11:05:01 AM
Comment:
This is very good article, when i tried this, i am getting an error if the number of the charters in the column inserting inot excel is more then 450. Any idea why it is causing this problem, any help is appreciated.
Title: Nice and Easy for any user   
Name: Neel
Date: 2007-01-08 4:24:14 AM
Comment:
Hi Aravind,

Thanks for ur nice article. Its realy useful and easy to use for everybody. we expect more from u..

One question, is it possible to generate such file from recordset with some data menipulation?

Thanks again
Neel
Title: Mr.   
Name: Vikram Singh
Date: 2006-12-21 12:17:06 AM
Comment:
Its really cool but if possible please also try to put some internal overview on commands like : "MASTER..XP_CMDSHELL"
thnx
Title: a Question   
Name: Adnan
Date: 2006-12-14 8:48:32 AM
Comment:
Everything is good except i DO NOT want the column headers to show in the excel sheet. How do I go about doing that ?
Title: Excellent man!!!   
Name: Beny
Date: 2006-11-29 1:25:18 AM
Comment:
Ive been searching the net for this kind of excel generation for quite a long time....Keep up the goog work..!!!
Excellent method...Is it possible for cross-mapping also??
Title: Mr   
Name: Prabhu
Date: 2006-11-14 1:35:34 PM
Comment:
It is a very useful article.
Title: good one   
Name: cecil
Date: 2006-10-29 10:33:21 PM
Comment:
Really a good one.
Title: if file path contain space, giving error   
Name: bhupal
Date: 2006-10-24 7:45:46 AM
Comment:
if excel file path contains spaces its giving error.
Title: Getting error while executing SP   
Name: Anju Pandey
Date: 2006-09-13 1:21:34 AM
Comment:
while executing SP_Excelman stored prcedure I am getting this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
Can you please help me out?
Thanks.
Title: Thanks   
Name: Rani
Date: 2006-09-12 4:03:23 PM
Comment:
I got an emergency request to do exactly what is in this article and this article was the life saver for me. thank you so much Arvind for sharing this good article with us.
Title: thanks   
Name: Ilango
Date: 2006-08-28 3:05:04 AM
Comment:
This is a very good example for who directly extract the large amount of data from SQL server then store stright to the Excel file.
Title: Prabhu rk   
Name: Prabhu Rk
Date: 2006-08-23 6:53:41 AM
Comment:
Excellent article ... !!!!
Title: Excellent, cute, flamboyant code   
Name: Jinesh Aiyer Madhavan
Date: 2006-08-04 2:37:15 AM
Comment:
Excellent, cute, flamboyant code
Title: Very Good   
Name: Deependra Joshi
Date: 2006-08-03 8:19:40 AM
Comment:
This is really very nice article. Since a week I was looking for the same.

Thank you.
Title: Mr.   
Name: Farrukh
Date: 2006-07-25 2:02:57 AM
Comment:
Realy very very nice article.
Title: Very Good   
Name: Prabhakar.T
Date: 2006-07-05 10:32:06 AM
Comment:
Really Nice article Keep it up.

thks and Regards,
Prabhakar.T
Title: Reply for Fabio   
Name: Aravind Kumar - Author
Date: 2006-07-05 1:11:09 AM
Comment:
I suspect that the "sa" user id has been restricted for Linked Server access in your SQL Server. Anyway try this link for solution - http://support.microsoft.com/default.aspx?scid=285833
Title: Error running   
Name: Fabio Nunes de Souza
Date: 2006-07-04 9:41:27 AM
Comment:
error.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
Title: Good!   
Name: Umesh Kumar
Date: 2006-06-27 6:37:25 AM
Comment:
Iam generating reports in excel and am having some problem in closing the excel instance after generating reports. I think this article may help a lot to me. Thanks Aravind!. Shall you help me to resolve my problem with this.
Title: Thank U   
Name: Bala
Date: 2006-06-27 5:57:25 AM
Comment:
Very Usefull,,,,Thank U.......
Title: Mr.   
Name: Ashvani
Date: 2006-06-26 11:06:03 AM
Comment:
Really nice article and helped me lot.

Thanks & Regards
Ashvani
Title: good   
Name: krishnamoorty
Date: 2006-06-26 12:26:16 AM
Comment:
very useful article for all.

thks.
krish
Title: Good One   
Name: Hamzah
Date: 2006-06-23 1:37:22 PM
Comment:
Nice article. Just a quick question. Is there any way you can create Test.xls on fly using the CMDSHELL.(Dynamically)

Thanks
Hamzah
Title: Ms.   
Name: Malar
Date: 2006-06-22 5:59:00 AM
Comment:
Really nice one!!!!! Very useful...






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-12-18 4:04:16 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search