April 21, 2008 at 5:22 am
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. 🙂
April 21, 2008 at 5:28 am
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).
?
April 21, 2008 at 5:47 am
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.
April 22, 2008 at 12:53 am
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
April 22, 2008 at 11:51 am
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.
April 23, 2008 at 1:04 am
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 😀
I hope that helps you out.
Regards
GermanDBA
Regards,
WilliamD
April 27, 2008 at 9:20 pm
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? 😀
April 28, 2008 at 1:30 am
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@cmdshellnvarchar(4000)
declare @cmdsqlnvarchar(4000)
declare@basedirvarchar(200)
declare@vorlagevarchar(200)
declare@locationvarchar(200)
declare@dummyvarchar(50)
declare @extensionvarchar(4)
declare@commandvarchar(20)
declare@filenamevarchar(200)
declare@yearvarchar(4)
declare@monthvarchar(2)
declare@dayvarchar(2)
declare@hourvarchar(2)
declare@minutevarchar(2)
declare@secondvarchar(2)
declare @attachment1varchar(200)
declare @attachment2varchar(200)
declare @attachment3varchar(200)
declare @attachment4varchar(200)
declare @attachment5varchar(200)
declare @attachment6varchar(200)
declare @attachment7varchar(200)
declare @attachment8varchar(200)
declare @attachment9varchar(200)
declare@attachment10varchar(200)
declare @attachallvarchar(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
execmaster..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
execmaster..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
execmaster..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! 😛
Regards,
WilliamD
April 30, 2008 at 12:24 am
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.
April 30, 2008 at 12:45 am
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 😀
Regards,
WilliamD
May 1, 2008 at 9:13 pm
This is great. I am having trouble actually getting the dynamic execution of the openrowset call. That way I can feed the path variable and sheet name from the loop I created to call a stored procedure with the excel file insert query inside.
Thanks for the reply, I really appreciate it.
May 5, 2008 at 12:39 am
Hi there A1_Technology_geb,
show me what you have so far and I will try to give you some pointers.
regards
GermanDBA
Regards,
WilliamD
May 7, 2008 at 1:45 am
I have create a SSIS package .Now i m trying to schedule this SSIS in sql agent jobs.
but i m geeting error. unable to run it.
pls. help me how can i run this?
steps of creating jobs in sql server 2005 ??????????????????
is it any credential required for this?
May 8, 2008 at 6:47 am
You say that you don't want to use DTS or SSIS.
Have you consider to use Excel and VBA instead?
If you have Excel installed at the "production machine" then think about it.
The nice thing is that you have much more control of Excel formulas and formats
and can combain the figures with filtering and charts. Your result is more "reportlike"
including analysing/filtering/pivoting options if you like.
I do a lot of jobs creating and distributing Excel files and started with DTS and
stored procedures but now I do it all in Excel and VBA.
If you already are familiar with Excel and VBA the step to add ADO with Database functions is not scaring.
May 29, 2008 at 3:10 am
Fantastic article ! Helped me a lot !
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply