Dynamically Create NEW Excel Files from query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamically Create NEW Excel Files from query

Author

Topic

LonnieM
Starting Member

15 Posts

Posted - 2012-05-25 : 12:47:43

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 15:28:45

you can add a Seq_No field to data you get from file and them add a logic to split up the totals rows based on Seq_No into small chunks and then use the subsets for populating your required excel files.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources