Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

How to export sql server data into multiple excel sheets ? Expand / Collapse
Author
Message
Posted Monday, April 21, 2008 5:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2008 1:35 AM
Points: 6, Visits: 18
How to export sql server data into multiple excel sheets ?


I don't want to do this from DTS or SSIS. i have dalready done this from OPENROWSET, but only single excel sheet, i want to export data on multiple attached excel sheets on per day bases.

Pls. any one respond . this is urgent and important for me.

thanks. :)
Post #487837
Posted Monday, April 21, 2008 5:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 10:02 AM
Points: 810, Visits: 915
Hi,

Why not DTS or SSIS as this is one of the reasons that these facilities exist??

Also, we'd need much more information on what you are starting with (in terms of tables etc), and what you are trying to achieve exactly (many tables to one file, one table to many worksheets and so on).

?




Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
Post #487840
Posted Monday, April 21, 2008 5:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2008 1:35 AM
Points: 6, Visits: 18
Hi,

I have a select query and want to pass the result of this query to excel sheets as per day bases.



i m working on sql server 2005. how can i use SSIS in sql jobs?

thanks.
Post #487859
Posted Tuesday, April 22, 2008 12:53 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 1:10 AM
Points: 449, Visits: 1,868
Hi there,

I wanted to do the same thing and managed to find this great little explanation on the interweb: http://www.databasejournal.com/features/mssql/article.php/10894_3331881_1

Please be aware that these methods of "speaking" to txt, csv and excel files will only work on SQL 2005 32 Bit version. The 64 Bit version has no Jet 4.0 drivers (MS didn't port them).

You can use the examples from the page to read from Excel, but you can use the connection to the openrowset to write back, just use insert into or select into.

Hope that helps

GermanDBA



Regards,

WilliamD
Post #488383
Posted Tuesday, April 22, 2008 11:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2008 1:35 AM
Points: 6, Visits: 18
hi,

thanks 4r reply.

i want to know , how can i export the sql server query result to multiple excel sheets.

through sql jobs. i can do thid with openrowset on single excel sheet but unable to do on multiple excel sheets (date wise.).

thanks.

Post #488799
Posted Wednesday, April 23, 2008 1:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 1:10 AM
Points: 449, Visits: 1,868
Hi there,

If I understand correctly, you want to export information into one excel file with multiple sheets.

The way I have done this is as follows:

1. Create an empty Excel file with the sheets in it you need (my example sales.xls with sheets "sheet1","sheet2")

2. Copy empty file to desired location/name

copy d:\templates\sales.xls d:\export\sales.xls

3. Using your select statement to get the desired information for sheet1; insert the data into the excel file:

 insert into OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\export\sales.xls;;HDR=YES',
'SELECT * FROM [Sheet1$]')
select * from sales_part1

4. Using your select statement to get the desired information for sheet2; insert the data into the excel file:

 insert into OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\export\sales.xls;;HDR=YES',
'SELECT * FROM [Sheet2$]')
select * from sales_part2

A few things to note here:

- When you make the template file, make sure to change the column format to whatever you need (especially important if the information contains decimal point information)
Excel uses the standard format and could drop some stuff like that.
- The only change made for point 3 and 4 was the select * from sales_part* - You can select whatever you want!
- The only change made for the select * from [Sheet?$] from the openrowset was the sheetname (Sheet1 to Sheet2).
- The sheet must be declared the same way as described (in square-brackets and with a dollar behind the actual name of the sheet)
- You have to have the Jet 4.0 Driver installed (32 Bit SQL 2005 has this automatically, up till now 64 Bit Jet 4.0 does not exist)

I have a complete example of the described scenario as a sp. If you need to see how it all works together in real life, just let me know and I can post it here. It is far from perfect (e.g. it uses xp_cmdshell), but I did write it in the first 2 months of ever using a Database, so I think that's ok :D

I hope that helps you out.

Regards

GermanDBA



Regards,

WilliamD
Post #489075
Posted Sunday, April 27, 2008 9:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 29, 2011 5:53 PM
Points: 11, Visits: 85
Sure, By all means, please post the stored procedure example.

I would imagine there would some looping involved if you actually expect to dynamically name the sheets.
This would be helpful if one were to display different resultsets on each of the multiple sheets$.

Is it possible to dynamically name each sheet prior to inserting the query results while the file is being edited? :D
Post #491171
Posted Monday, April 28, 2008 1:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 1:10 AM
Points: 449, Visits: 1,868
Hi there,

here is the code - anonymised (sp?) of course.

As said in a previous post, this was written a long time ago when I had just learned how to write selects and very basic programming, if there are syntax errors I would guess it has to do with the @cmdsql statement building in step 3 of the programming.

The code can be used/modified and passed on (if it's good enough).

The only thing missing here is the loop to write to multiple sheets, but I think most of you could get that done. I had a similar bit of logic to get round the 64000 row problem in excel, but cheated by doing it in two steps and using TOP 64000 in the first select statement.


create  proc [dbo].[proc_Language_Export] (@language varchar(2),@email varchar(100),@emailsubject varchar(50)) as
--
-- Variable declaration
--

declare @cmdshell nvarchar(4000)
declare @cmdsql nvarchar(4000)
declare @basedir varchar(200)
declare @vorlage varchar(200)
declare @location varchar(200)
declare @dummy varchar(50)
declare @extension varchar(4)
declare @command varchar(20)
declare @filename varchar(200)
declare @year varchar(4)
declare @month varchar(2)
declare @day varchar(2)
declare @hour varchar(2)
declare @minute varchar(2)
declare @second varchar(2)
declare @attachment1 varchar(200)
declare @attachment2 varchar(200)
declare @attachment3 varchar(200)
declare @attachment4 varchar(200)
declare @attachment5 varchar(200)
declare @attachment6 varchar(200)
declare @attachment7 varchar(200)
declare @attachment8 varchar(200)
declare @attachment9 varchar(200)
declare @attachment10 varchar(200)
declare @attachall varchar(4000)


select @year = datepart(year,getdate())
select @month = right('0'+cast(datepart(month,getdate()) as varchar (2)),2)
select @day = right('0'+cast(datepart(day,getdate()) as varchar (2)),2)
select @hour = right('0'+cast(datepart(hour,getdate()) as varchar (2)),2)
select @minute = right('0'+cast(datepart(minute,getdate()) as varchar (2)),2)
select @second = right('0'+cast(datepart(second,getdate()) as varchar (2)),2)
set @basedir = '\\server1\c$\import_export\Translation\Export\'
set @vorlage = '\\server1\c$\import_export\Translation\Template\'

--
-- Step 1: Exportdirectory creation (if not already there)
--

set @command = 'if not exist '
set @location = @basedir+@language
set @cmdshell = @command+@location+' '
set @command = 'md '
set @cmdshell = @cmdshell+@command+@location
exec master..xp_cmdshell @cmdshell,no_output;
set @command = 'if not exist '
set @cmdshell = @command+@location+'\'+@year+@month+@day+' '
set @command = 'md '
set @cmdshell = @cmdshell+@command+@location+'\'+@year+@month+@day
set @location = @location+'\'+@year+@month+@day
exec master..xp_cmdshell @cmdshell,no_output;

--
-- Step 2: Create exportfiles with timestamp in name, fill the files once there
--

set @dummy = 'Exportfile1'
set @extension = '.xls'
set @command = 'copy '
set @filename = 'Exportfile1'+'_'+@hour+@minute+@second+@extension
set @cmdshell = @command+@vorlage+@dummy+@extension+' '+@location+'\'+@filename
exec master..xp_cmdshell @cmdshell,no_output;
set @attachment1 = @location+'\'+@filename
set @cmdsql='
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
insert into OPENROWSET(
''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database='+@location+'\'+@filename+';;HDR=YES'',
''SELECT * FROM [Sheet1$]'')
select

stuff

from
languageexporttable

where

language = '''''+@language+''''''
exec (@cmdsql)

--Step 3 - ? Do the other exports

--Step ?+1 send email with results using email address supplied at proc call.

That should hopefully point you in the right direction. Unfortunately I have no idea how you would dynamically name the sheets for the excel file. I only use an empty excel file with the default names. I know you can do it using the object creation sys.sps that are in sql server (BE CAREFUL they are resource killers AFAIK).

Well, enough babbling. Have fun and please be kind when slating the code quality: I was but a young pup! :P



Regards,

WilliamD
Post #491216
Posted Wednesday, April 30, 2008 12:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 7, 2008 1:35 AM
Points: 6, Visits: 18
Hi ,

I have tried this but didn't get how to use it.

i want to generate multiple excel sheets , date wise.

can i do this through TSQL.

pls. guide me.

urgent.
Post #492666
Posted Wednesday, April 30, 2008 12:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 1:10 AM
Points: 449, Visits: 1,868
Hi deepti811,

I will write an example again to show you what you need to do:

--Step 1
exec xp_cmdshell 'copy d:\templates\sales.xls d:\export\sales.xls'

--Step 2
insert into OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\export\sales.xls;;HDR=YES',
'SELECT * FROM [Sheet1$]')
select * from sales_part1

--Step 3
insert into OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=d:\export\sales.xls;;HDR=YES',
'SELECT * FROM [Sheet2$]')
select * from sales_part2



Step 1 copies the template excel file. The excel file has all the columns you want already in it (names only and columns formatted as needed).

Step 2 inserts the first set of data into Sheet1

Step 3 inserts the first set of data into Sheet2

You can put this code into a loop or whatever you need, this is just an example to show how to write to multiple sheets. The directories and select statement can be changed however you need. The openrowset can also use UNC targets.

It is quite simple really but if you don't understand what I mean please show me exactly where the problem is. Simply saying it doesn't work is an end-user error message :D



Regards,

WilliamD
Post #492673
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse