DTS output from stored procedure to Excel
-
I am not sure if I am in the right forum...
I have a stored procedure which I run once a week to get the sales for the past 6 weeks, can't create a view as the variable is week number.
I need to get this report into Excel for the Marketing department, so thought about outputting a table from the stored procedure. Is it possible to retrieve this and then output it to Excel?
I would also like the column names to be week + weeknumber in the Excel file. I think the only way to do this is with dynamic sql - is there any other way?
-
You're correct you will need to use dynamic sql to provide the column name.
One other thing to consider, instead of outputting to an XLS file, output the data to a comma seperated file with a CSV extension. CSV files open up in Excel by default.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
I following sp is a modification of one I found here some weeks back. Sorry - can't remeber the origional author. It is used like so..
DECLARE @strReportSQL VARCHAR(8000)
SET @strReportSQL = 'INSERT INTO ~XLWS~ ([Column Date], [Column String], [Column Integer]) ' +
'SELECT ' +
'GETDATE(), ' +
'''Hello'', ' +
'1234 '
EXEC spSaveToExcel @pPath= 'C:\ExportToExcel.xls',
@pWorksheetName = '[Sample_Sheet_1]',
@pExcelTableDefinition = '[Column Date] DATETIME, [Column String] TEXT, [Column Integer] INTEGER',
@pSQL = @strReportSQLmain body follows..
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE spSaveToExcel (
@pPath VARCHAR(255) -- Full Drive or UNC path for XLS file. Requires Read/Write access
-- and must be accessable from server via SQL Server service account
-- & SQL Server Agent service account, if scheduled
,@pWorksheetName VARCHAR(128)-- Name of the XLS Worksheet (table)
,@pExcelTableDefinition VARCHAR(255) -- Comma delimited list in the following format, using Jet data types
--Column1Name Column1DataType, Column2Name Column2DataType, etc.
-- If @pWorksheetName does not exist then it is created using this
-- definition
,@pSQL AS VARCHAR(8000)-- The SQL to execute. Use ~XLWS~ aS a placeholder for the spreadsheet
)AS
SET NOCOUNT ON
DECLARE @intADOConnection int -- ADO Connection object to create XLS
DECLARE@intHR int -- OLE return value
DECLARE@strErrorSrc varchar(255) -- OLE Error Source
DECLARE @strErrorDesc varchar(255) -- OLE Error Description
DECLARE @strConnect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM
DECLARE @blnCreatedWorksheet bit -- Whether the XLS Worksheet exists
DECLARE @strCreateTableDDL varchar(8000)-- Jet4 DDL for the XLS WKS table creation
DECLARE @intInsertedCount int -- Number of records added to XLS
-- Init variables
SET@intInsertedCount = 0
-- Assign the ADO connection string for the XLS creation
SET @strConnect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@pPath+';Extended Properties=Excel 8.0'
-- Table creation DDL, uses Jet4 syntax,
-- Text data type = varchar(255) when accessed from T-SQL
SET @strCreateTableDDL = 'CREATE TABLE ' + @pWorksheetName + ' (' + @pExcelTableDefinition + ')'
/****************************************************************************************************************/
/* Test for the presence of the Excel spreadsheet. If it is there then either connect to the specified */
/* worksheet (@pWorksheetName), or create it using the supplied definition (@pExcelTableDefinition)*/
/****************************************************************************************************************/
-- Create the Conn object
EXEC @intHR = sp_OACreate 'ADODB.Connection', @intADOConnection OUT
-- Have to use <> as OLE / ADO can return negative error numbers
IF @intHR <> 0 BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @intADOConnection, @strErrorSrc OUT, @strErrorDesc OUT
SELECT Error=convert(varbinary(4),@intHR), Source=@strErrorSrc, Description=@strErrorDesc
RETURN
END
-- Set a the Conn object's ConnectionString property
-- Work-around for error using a variable parameter on the Open method
EXEC @intHR = sp_OASetProperty @intADOConnection, 'ConnectionString', @strConnect
IF @intHR <> 0 BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @intADOConnection, @strErrorSrc OUT, @strErrorDesc OUT
SELECT Error=convert(varbinary(4),@intHR), Source=@strErrorSrc, Description=@strErrorDesc
RETURN
END
-- Call the Open method to create the XLS if it does not exist, can't use parameters
EXEC @intHR = sp_OAMethod @intADOConnection, 'open'
IF @intHR <> 0 BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @intADOConnection, @strErrorSrc OUT, @strErrorDesc OUT
SELECT Error=convert(varbinary(4),@intHR), Source=@strErrorSrc, Description=@strErrorDesc
RETURN
END
-- Create the Excel table (worksheet)
-- Call the Execute method to Create the work sheet with the @pWorksheetName caption,
-- which is also used as a Table reference in T-SQL
-- Neat way to define column data types in Excel worksheet
-- Sometimes converting to text is the only work-around for Excel's General
-- Cell formatting, even though the Cell contains Text, Excel tries to format
-- it in a "Smart" way, I have even had to use the single quote appended as the
-- 1st character in T-SQL to force Excel to leave it alone
EXEC @intHR = sp_OAMethod @intADOConnection, 'Execute', NULL, @strCreateTableDDL, NULL, 129 -- adCmdText + adExecuteNoRecords
-- 0x80040E14 for table exists in ADO kludge, skip 0x80042732 for ADO Optional parameters (NULL) in SQL7
IF @intHR = 0x80040E14 OR @intHR = 0x80042732 BEGIN
-- Trap these OLE Errors
IF @intHR = 0x80040E14BEGIN
SET @blnCreatedWorksheet = 0
END
SET @intHR = 0 -- ignore these errors
END
IF @intHR <> 0 BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @intADOConnection, @strErrorSrc OUT, @strErrorDesc OUT
SELECT Error=convert(varbinary(4),@intHR), Source=@strErrorSrc, Description=@strErrorDesc
RETURN
END
-- Destroy the Conn object, +++ important to not leak memory +++
EXEC @intHR = sp_OADestroy @intADOConnection
IF @intHR <> 0 BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @intADOConnection, @strErrorSrc OUT, @strErrorDesc OUT
SELECT Error=convert(varbinary(4),@intHR), Source=@strErrorSrc, Description=@strErrorDesc
RETURN
END
/***************************************************************************************************************/
-- Execute the SQL
SET @pSQL = REPLACE(@pSQL, '~XLWS~', 'OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'', ''Data Source="' + @pPath + '";Extended properties="Excel 8.0;HDR=Yes;"'')...' + @pWorksheetName)
EXEC (@pSQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
My personal preference for outputting data to Excel is via a DTS package. If I need to pass a variable to it, I create a global variable and pass it on the command line. It also helps if you create batch files to call the DTS packages with that take your variable(s) as a parameter. I have also used VBScript (.vbs) files to execute packages when I needed something a bit more powerful.
To handle getting the column names into your output, there is another way you might do it. If it's not a problem to convert each field in your output to a varchar, you can:
select 'field1' , 'field2' , 'field3'
UNION ALL
select convert(varchar(255),field1) , convert(varchar(255),field2) , convert(varchar(255),field3)
from the_table
It only works if you can convert them all to varchars though.
You know, there may be another way to do it... when I'm generating reports in Excel, sometimes I use the MS Query tool to execute either a SQL statement or a stored procedure in Excel. You could create an Excel file that contains the query definition and just refresh the result, perform a Save As on the file, and send it to them. The only caveat is you would have to store your date parameter somewhere that your procedure could look it up, unless you wanted to modify the query in Excel each time, which isn't really a big deal. You could even use two separate queries in the Excel file, one for the column headings and another for the data to get around the dynamic columns. You might even include an autoexec macro in the Excel file that could ask you for the date that could update a parameter table for your procedures with your input, thereby configuing the report... Heh, I'm expanding as I go, so I haven't tried the above, but I'm pretty sure it would work. If you do something like this, let me know how it works out. 🙂
Matthew Galbraith
-
hi
approach the problem from the other direction
get the data from Excel, use [menubar] Data/Get External data... attach to the SQL Server DSN and access your data (or sp) using MS query. You can even automate the process to pass parameters (assigned in your spreadsheet) to the sp using VBA.
i've been doing this for years in vary complexity
regards
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply