May 25, 2012 at 10:43 am
:unsure:Hello All,
I have made several attempts at this over the years and have not succeeded yet. I have also scoured the forums and articles for this and can come close but have found no clean solution using SSMS & TSQL.
I need to iterate through a large file (2M+ rows) and "chunk" the data into 223 Excel files based on a district manager name. This sounds simple and I have semi-manually done this for quite some time. I would like to automate this cursor to actually create the Excel file automatically based on the district manger name.
The code below comes very close but does not create a new Excel file. The Insert into "openrowset" command apparently needs an existing spreadsheet?
I am now using SQL Server 12 RC0.
I am open to using SSIS but would need considerable hand-holding in that area.
CURRENT CODE (Will run out 223 ind. queries):
DECLARE @name VARCHAR(50) -- database name
DECLARE @FolderName varchar(200) -- folder path
DECLARE @FileName varchar(200) -- Excel File Name
DECLARE @sql varchar(1000) -- Dynamic SQL String
Set @FolderName = 'D:\Data\'
DECLARE db_cursor CURSOR FOR
SELECT District FROM Bi_Scamp.dbo.Temp_Vinnie_DM
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
Set @FileName = @FolderName + @name + '.xlsx'
Select A.*
From BI_SCamp.dbo.Temp_Vinnie AS A
Where A.District = @name
--Set @sql = 'INSERT INTO OPENROWSET (''Microsoft.JET.OLEDB.4.0'', ''Excel 8.0;Database=' + @FileName + ''', ''Select A.*
--From BI_SCamp.dbo.Temp_Vinnie_DM_2 AS A Where A.DM = ' + '''' + @name + '''''' + ')'
--Print @sql
--exec(@SQL)
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Any help would be greatly appreciated.
Sincerely,
Lonnie M
May 25, 2012 at 11:44 am
I assume SQL Server 2012. No such thing as SQL 12 yet.
This is much easier to do in SSIS. Is there a reason you don't do that?
As far as what you're doing, you could use sp_oa procedures to create a new Excel document.
May 25, 2012 at 11:59 am
:-D, Yes, SQL Server 2012 🙂
I haven't done it in SSIS simply because I'm not that familiar with it.
May 25, 2012 at 12:01 pm
Oops...hit the "Post" button too soon. So you have a sample package that might do this?
I am open to SSIS....just relatvely new to it.
Thanks,
Lonnie M
May 25, 2012 at 1:01 pm
These should help
http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/
http://www.bidn.com/blogs/KeithHyer/bidn-blog/2512/creating-excel-worksheets-dynamically-in-ssis
http://www.sqlservercentral.com/articles/74225/
http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/68025/
If you want to stick with your method: http://www.sqlservercentral.com/articles/Excel/64838/
May 28, 2012 at 10:20 am
Thank you for the information Steve. Unfortunately, I had seen most of these and none do exactly what I'm looking for. The article on SQL ServerCentral that uses the sp_OA methods was interesting...it even runs but produces no errors and no results.
I am surprised in this business world where Excel is so prevalent that someone has not done EXACTLY what I need to do.
I'm now looking to SSIS and will attempt to build something in there. Starting by exporting a file to Excel '07 then modifiying the package I'm looking for a way to insert the cursor I created and then kick out a dynamically named Excel file with each iteration. I am not a programmer so any additional help would be welcomed.
Thank you.
Lonnie M
May 29, 2012 at 9:05 am
A brief overview.
In SSIS, create an ADO recordset with the managers and then use a FOR EACH ADO enumerator container to produce the Excel file for each manager. The process is probably too long for a forum article, but you can find examples of this on the web.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 29, 2012 at 11:42 am
lmeinke (5/25/2012)
The Insert into "openrowset" command apparently needs an existing spreadsheet?
Correct.
Is the format of the spreadsheet the same for each district manager? If so, the simplest way to handle the case is to create an Excel file that mimics the format you need, but with no data in it, and use that as a template. In your code copy the template Excel file into place before issuing your OPENROWSET command to populate it for the current manager. Repeat as necessary until all spreadsheets have been generated.
As a side note I prefer to do this kind of work in SSIS. Using xp_cmdshell to create copies of the Excel template and OPENROWSET to populate the spreadsheets will certainly get the job done, albeit with some server options turned in that I would rather leave disabled.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply