SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to export sql server data into multiple excel sheets ?


How to export sql server data into multiple excel sheets ?

Author
Message
deepti811
deepti811
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 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. Smile
Adrian Nichols-360275
Adrian Nichols-360275
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3314 Visits: 915
Hi,

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

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...
deepti811
deepti811
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 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.
w.durkin@online.de
w.durkin@online.de
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1845 Visits: 1879
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
deepti811
deepti811
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 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.
w.durkin@online.de
w.durkin@online.de
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1845 Visits: 1879
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 BigGrin

I hope that helps you out.

Regards

GermanDBA

Regards,

WilliamD
a1_technology_geb
a1_technology_geb
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 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? BigGrin
w.durkin@online.de
w.durkin@online.de
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1845 Visits: 1879
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! Tongue

Regards,

WilliamD
deepti811
deepti811
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 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.
w.durkin@online.de
w.durkin@online.de
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1845 Visits: 1879
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 BigGrin

Regards,

WilliamD
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search