InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 06:39:21 GMT20http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHello, Do you want to export data in one table or different tables to multiple sheets? If the later one, you can create a template file, which the first sheet is used to save data in one table, while the second saves data in other. And then export data. There is a simple example. Please have a look. [url=http://www.c-sharpcorner.com/UploadFile/d2dcfc/C-Sharp-create-excel-report-chart-contained-with-mark-designe/]http://www.c-sharpcorner.com/UploadFile/d2dcfc/C-Sharp-create-excel-report-chart-contained-with-mark-designe/[/url]Hope helpful!Thu, 08 Mar 2012 19:42:11 GMTSummiRS2http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxInteresting idea, let me try that. I have a very similar requirement. I want to generate a spreadsheet with many worksheets, but I don't want to manually create a template Excel. I want to create it from the queries. If needed, I can create views for every query.Mon, 16 Jan 2012 11:33:20 GMTNaomi Nhttp://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHi,This is sekharplease send step by step process for how split the data into multiple excel sheets in single excelfile using ssisregardssekharWed, 14 Dec 2011 04:39:41 GMTsripphttp://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxyes,you can mail using OSQL command and Vmailer exe fileCheersRBThu, 17 Jun 2010 11:45:32 GMTPaVeRa22http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHi Guys, Its pretty simple...I was decided to use 5 DFT tasks in side to create multiple sheets in single excel file. I started working on it, mean while I tired to use source and target of the excel properties differently than by default.Here I sent dynamic sheet name, data, when I ran , all of sudden i got data populated in 10 sheets, :-)this logic can apply not only for larger record count, but also for small count for e.g 10 records should goes into 10 sheets in single excel file, pages depends on the total record count / no of rows in each page.this can be achieved using temp tables and for loop container and one sql connection, and one excel connection.I have this solution, but not in my lappy, i will try to create a sample and post here, I gave you almost the outline of the solution.CheersRBThu, 17 Jun 2010 11:33:37 GMTPaVeRa22http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspx[quote][b]sapna2310 (10/21/2008)[/b][hr]Is there a way we can mail query results in excel format in sql 2005 without using DTS or SSIS?[/quote]If you have DB_Mail setup, then yes.[code]EXEC msdb.dbo.sp_send_dbmail @profile_name = 'EmailProfile', @recipients = 'you@domain.com', @copy_recipients = 'me@domain.com', @body = 'Hi Here are the results', @subject = 'Excel Results', @file_attachments = 'c:\file.xls';[/code]Fri, 29 May 2009 06:42:08 GMTM Murphyhttp://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxhi; I just tried tu use your code to a similar problem I have, but I recieved the next error:Msg 102, Level 15, State 1, Line 19Incorrect syntax near 'Microsoft'.Msg 102, Level 15, State 1, Line 20Incorrect syntax near ''the code is this:DECLARE @rc intDECLARE @dt varchar(8)DECLARE @cmd nvarchar(1000)declare @newfile nvarchar(100)SELECT @dt = Convert(varchar(8),getdate(),112)SET @cmd = 'copy i:\toluca\toluca.xls i:\toluca\toluca_'+@dt+'.xls'EXEC @rc = master.dbo.xp_cmdshell @cmd Exec master..xp_cmdshell @cmd set @newfile='i:\toluca\toluca_'+@dt+'.xls'SET QUOTED_IDENTIFIER ONSET ANSI_NULLS ON --set @newfile=''Excel 8.0;Database='+@newfile+';;HDR=YES'' insert into OPENROWSET( ''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='+@newfile+';;HDR=YES'', ''SELECT * FROM [ayunt$]'')select * from accioral.dbo.mun125hopr u can help me !! thnaksThu, 28 May 2009 01:48:08 GMTluisalfonso70http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHi GermanDBA,Thanks for you good solutions.can you please provide the step by step details, how to do the export data to one excel file of multiple sheets using DTS packages?It is will be a great help!!!!Thanks in advance.Thanks PJBTue, 04 Nov 2008 07:28:33 GMTjilanibaji.phttp://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxIs there a way we can mail query results in excel format in sql 2005 without using DTS or SSIS?Tue, 21 Oct 2008 04:47:03 GMTsapna2310http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxIn that case, the path to the spreadsheet should probably be a UNC that the SQL server can see... the reference to C: is for the server and won't work because the path you used doesn't actually exist on the server.Wed, 20 Aug 2008 18:48:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHi Jeff, This is my Code.insert into OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Contact.xls;;HDR=YES','SELECT * FROM [Sheet1$]')select * from testingMy SQL Server is on different server from Excel file.ThanksWed, 20 Aug 2008 05:18:03 GMTSajid-811795http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxLeft my mindreader hat home... please post your code. ;)Mon, 18 Aug 2008 16:47:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHi All,I tried "Openrowset" but it is not working. The error is "Could not find sheet1$". I am using MS Excel 2003 and SQL Server 2005. Please any one have some ideas.ThanksMon, 18 Aug 2008 03:07:43 GMTSajid-811795http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspx[quote][b]balasach82 (6/12/2008)[/b][hr]I am Balaji new to this discussion. My Code is::exec xp_cmdshell 'copy C:\templates\BALAJI1.xls C:\export\BALAJI1.xls'insert into OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\export\BALAJI1.xls;;HDR=YES','SELECT * FROM [Sheet1$]')select * from [b][size="3"]balaji1[/size][/b]ERROR:: Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'BALAJI1'.[/quote]Hilighted code above must be the table name that contains the data that you want to insert into the spreadsheet.Tue, 24 Jun 2008 19:43:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxBALAJI1 IS A table which would contain the records present in the excel sheet.Fri, 13 Jun 2008 05:13:04 GMTbalasach82http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxI attach the word document that contains the definition of the stored procedures USP_DMOExportToExcel that allows you to export the result set of a query on Excel.Call for example:With SQL Server authentication:[font="Courier New"]USP_DMOExportToExcel @SourceServer='ServerName', @SourceUID= 'UserName', @SourcePWD = 'Password', @QueryText = 'use DBTEST Select Field1, Field2 From Table1', @filename = 'C:\Table1.xls', @WorksheetName='MyTable1', @WorksheetIndex=1, @RangeName ='MyRangeTable1'[/font]With integrated security:[font="Courier New"]USP_DMOExportToExcel @SourceServer='ServerName', @QueryText = 'use DBTEST Select Field1, Field2 From Table1', @filename = 'C:\Table1.xls', @WorksheetName='MyTable1', @WorksheetIndex=1, @RangeName ='MyRangeTable1'[/font]I hope it is useful, I usually use it for export data.SergioThu, 12 Jun 2008 11:02:29 GMTsgovonihttp://www.sqlservercentral.com/Forums/Topic487837-19-1.aspx(Guesswork follows...)Does "select * from balaji1" work without the insert statement? (Is balaji1 a table/view in the SQL Server database?) Might you need to further qualify the select statement source with db name, and schema?Thu, 12 Jun 2008 10:19:46 GMTJim Russell-390299http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxI am Balaji new to this discussion. My Code is::exec xp_cmdshell 'copy C:\templates\BALAJI1.xls C:\export\BALAJI1.xls'insert into OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\export\BALAJI1.xls;;HDR=YES','SELECT * FROM [Sheet1$]')select * from balaji1ERROR:: Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'BALAJI1'.Thu, 12 Jun 2008 08:50:51 GMTbalasach82http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxI much prefer treating this requirement as a pull rather than a push. Make each query into a view, and give your user(s) select access to the view.Then create a workbook, and for each tab, install a "Data/Import External Data/New Database Query" to present the results of each view. Set the query data range properties to "refresh data on file open" (and other settings as you like.)The advantages:1. It is easier to implement2. The user always sees the most current query results3. To the user, there is no visible difference between a pre-populated workbook and one that gets populated when it is opened.(Just warn them that changes they try to make to the data by hand will be lost.)Fri, 30 May 2008 05:22:24 GMTJim Russell-390299http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxFantastic article ! Helped me a lot !Thu, 29 May 2008 03:10:07 GMTpeter.roethlisbergerhttp://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxYou 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 andstored 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.Thu, 08 May 2008 06:47:26 GMTGosta Munktellhttp://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxI 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?Wed, 07 May 2008 01:45:51 GMTdeepti811http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHi there A1_Technology_geb,show me what you have so far and I will try to give you some pointers.regardsGermanDBAMon, 05 May 2008 00:39:00 GMTWilliamD-http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxThis 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.Thu, 01 May 2008 21:13:27 GMTa1_technology_gebhttp://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHi deepti811,I will write an example again to show you what you need to do:[code]--Step 1exec xp_cmdshell 'copy d:\templates\sales.xls d:\export\sales.xls'--Step 2insert 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 3insert into OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\export\sales.xls;;HDR=YES', 'SELECT * FROM [Sheet2$]')select * from sales_part2[/code]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 Sheet1Step 3 inserts the first set of data into Sheet2You 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 :DWed, 30 Apr 2008 00:45:21 GMTWilliamD-http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHi ,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.Wed, 30 Apr 2008 00:24:41 GMTdeepti811http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHi 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.[code]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+@languageset @cmdshell = @command+@location+' 'set @command = 'md 'set @cmdshell = @cmdshell+@command+@locationexec 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+@dayset @location = @location+'\'+@year+@month+@dayexec 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+@extensionset @cmdshell = @command+@vorlage+@dummy+@extension+' '+@location+'\'+@filenameexec master..xp_cmdshell @cmdshell,no_output;set @attachment1 = @location+'\'+@filenameset @cmdsql='SET ANSI_NULLS ONSET QUOTED_IDENTIFIER ONinsert into OPENROWSET( ''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database='+@location+'\'+@filename+';;HDR=YES'', ''SELECT * FROM [Sheet1$]'')selectstufffrom languageexporttablewhere language = '''''+@language+''''''exec (@cmdsql)--Step 3 - ? Do the other exports--Step ?+1 send email with results using email address supplied at proc call.[/code]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! :PMon, 28 Apr 2008 01:30:35 GMTWilliamD-http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxSure, 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? :DSun, 27 Apr 2008 21:20:48 GMTa1_technology_gebhttp://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHi 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[code]copy d:\templates\sales.xls d:\export\sales.xls[/code]3. Using your select statement to get the desired information for sheet1; insert the data into the excel file:[code] 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[/code]4. Using your select statement to get the desired information for sheet2; insert the data into the excel file:[code] 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[/code]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 :DI hope that helps you out.RegardsGermanDBAWed, 23 Apr 2008 01:04:49 GMTWilliamD-http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxhi,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.Tue, 22 Apr 2008 11:51:08 GMTdeepti811http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHi 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_1Please be aware that these methods of "speaking" to txt, csv and excel files will only work on SQL 2005 [b]32 Bit[/b] 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 helpsGermanDBATue, 22 Apr 2008 00:53:31 GMTWilliamD-http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHi,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.Mon, 21 Apr 2008 05:47:35 GMTdeepti811http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspxHi,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).?Mon, 21 Apr 2008 05:28:16 GMTAdrian Nichols-360275http://www.sqlservercentral.com/Forums/Topic487837-19-1.aspx[b]How to export sql server data into multiple excel sheets ? [/b]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. :)Mon, 21 Apr 2008 05:22:25 GMTdeepti811