March 1, 2019 at 11:22 pm
I already have a working stored procedure that exports sql query data set into a .csv file and emails it. Works great.
Now I need to modify it to send dataset as a formatted .xlsx report! xlsx template for how formatting is to look is attached. (column headers start on 11th row, are bold, Tahoma 8 Font, and column header cells highlighted in grey).
I don't know how to do it and if someone else does, can you provide an example to emulate?
--below generates query and emails dataset as .csv attachment
CREATE PROCEDURE [dbo].[UPS_Disputed_Submission_Form]
AS
/*******************************************************************************
Description:
Date Author Comments
---------- ----------- ----------------------------------------------------
********************************************************************************
sql to email demo taken from https://www.red-gate.com/simple-talk/blogs/sending-query-results-to-excel-through-e-mail/
********************************************************************************/
declare @qry varchar(8000)
declare @CT_Reason_Description varchar(50)
-- Create the column name with the instrucation in a variable
SET @CT_Reason_Description = '[sep=,' + CHAR(13) + CHAR(10) + 'CT_Reason_Description]'
-- Create the query, concatenating the column name as an alias
select @qry='set nocount on;select CT_Reason_Description ' + @CT_Reason_Description +
' , Status from Transportation.dbo.claimsStatuses'-- Send the e-mail with the query results in attach
exec msdb.dbo.sp_send_dbmail
@recipients='polkadot@myemail.com',
@query=@qry,
@subject='new statuses list',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'claim_statuses_not_in_conversion_tbl.csv',
@query_result_separator=',',@query_result_width =32767,
@query_result_no_padding=1
--Quote me
March 2, 2019 at 4:40 am
You won't be able to do this is SQL itself. It the formatting (and font?) are really that important you'll need to use a different tool like SSRS. You'll then be able to control the design of the spreadsheet.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 2, 2019 at 5:51 am
I've done something similar with SSIS (a few years ago).
I needed to get a temple spreadsheet with a table in it, to do this I created a new bank spreadsheet then using SSIS with an insert statement created an Excel table in template with the same columns are the query. You can then format the table with colours, bold etc.
Then I manually deleted all the rows from the "table" still leaving the table there and use that spreadsheet as the template. From SSIS you can then delete, update, insert this Excel table
Then in SSIS you copy the template to a file of the name you require. Insert the rows from your query into the "able" in the spreadsheet. Then you have a formatted table in the spreadsheet.
You can even have a template with macros in and set them to autorun, or run from a button on the spreadsheet to do almost anything you want to the data.
March 2, 2019 at 10:26 am
Thom A - Saturday, March 2, 2019 4:40 AMYou won't be able to do this is SQL itself. It the formatting (and font?) are really that important you'll need to use a different tool like SSRS. You'll then be able to control the design of the spreadsheet.
If I remove formatting requirement, is there a way to sent the dataset as .xlsx (all within the SQL sproc)?
--Quote me
March 2, 2019 at 10:43 am
yes - but why would you do it if there are better and easier ways to do it?
you can study the openxml model, and create the same file format in SQL - won't be easy though.
if you don't wish to use SSIS/SSRS you could look at creating a CLR proc/function that uses OpenXML and creates the file for you on the desired format.
Open XML linkOpen XML link https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk%5B/url]
March 2, 2019 at 12:11 pm
If you create a template Excel file with the desired formatting, and create a named range where you want to write the date, you can use the range name as a table name. This example uses [Sheet1$] as a table name, you can use a range name instead to write to a specific area. Then you can either use a DELETE to clear the entire range in the spreadsheet first, or figure out how to copy the template file to a working copy before writing to it.
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM [Sheet1$]')
SELECT * FROM dbo.DimScenario
Example taken from:
https://solutioncenter.apexsql.com/how-to-import-and-export-sql-server-data-to-an-excel-file/
March 6, 2019 at 7:54 pm
Thank you Scott Coleman, I'm following the instructions within link.
I haven't succeeded yet, because I need a linked server and I need someone with Admin permissions to create it for me, but after that I should be able to make progress.
Key here is that it can be done without external application like SSIS or Import/Export wizard, neither of which are options for me.
--Quote me
March 10, 2019 at 8:37 pm
I switched to using INSERT OPENDATASOURCE instead of OPENROWSET since the former doesn't require having a linked server set up.
Process involves creating a template excel that is permanently stored in a Template folder, and then during execution a copy of the template xlsx is moved to a folder where it can be written to by the OPENDATASOURCE query.
I am able to format the template with the needed header spaces, the bold column headers with right font. Everything is going well, but one thing I can't overcome is that a blank row is being inserted below the column headers and the data set inserts below that.
When saving the template (attached) I make sure that the cursor is at the top left corner of the file...
How to save the template so that no blank row is inserted below the column headers?
query
USE DB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Export_TO_XLSX]
AS
DECLARE @SqlCmd nvarchar(4000),
@RowsAffected int,
@EventText varchar(4000),
@Id int = 351,
@LogId int,
@DataFileExtension varchar(8000),
@FileShare varchar(8000),
@FileShare2 varchar(200),
@FileName varchar(8000),
@ServerShare varchar(8000),
@ExportFileName varchar(8000),
@ExecCmd varchar(8000),
@Recipients varchar(max) = 'polkadot@work.com',
@cc varchar(max) = null,
@Subject varchar(max) = 'claims',
@Body varchar(max) = null,
@RunDate date = GETDATE(),
@file_attachments varchar(max),
@emailbody varchar(max) = 'The attached excel contains claims';
SET NOCOUNT ON;
SELECT @FileName = pf.Name,
@DataFileExtension = pf.DataFileExtension,
@FileShare = pf.FileShare,
@ServerShare = pf.LocalServerPath
FROM ProcessEngine.dbo.P_FileProperties pf
WHERE Id = @Id;
BEGIN TRY
-- Copy the template file to a date specific file that will be populated at the end.
SET @ExportFileName = @FileName + '_' + CONVERT(varchar, GETDATE(), 112) + '_' + REPLACE(CONVERT(varchar, GETDATE(), 108),':','') + '.' + @DataFileExtension;
SET @ExecCmd = 'COPY "' + @FileShare + '\Template\' + @FileName + '.' + @DataFileExtension + '" "' + @ServerShare + '"' + ' /Z /Y /V';
--Copy the file to the local drive
EXECUTE master.dbo.xp_cmdshell @ExecCmd;
-- Now rename it with a date/time stamp.
SET @ExecCmd = 'RENAME "' + @ServerShare + '\' + @FileName + '.' + @DataFileExtension + '" "' + @ExportFileName + '"';
print @ExecCmd
--Copy the file to the local drive
EXECUTE master.dbo.xp_cmdshell @ExecCmd;
SET @SqlCmd =
'INSERT OPENDATASOURCE
( ''Microsoft.ACE.OLEDB.12.0'',''Data Source=' + @ServerShare + '\' + @ExportFileName + ';Extended Properties=Excel 12.0'')...[Sheet1$]
SELECT
UPS_Account_Number,
UPS_Tracking_Number,
UPS_Pick_Up_Date,
Original_Invoice_Number,
Purchase_Order_Number,
Merchandise_Quantity,
Package_Weight,
LBS_or_KGS,
Replacement_Cost,
Shipping_Charges,
Total_Claim,
Consignee_Contact_Name,
Consignee_Contact_Number,
Customer_contacted,
Replacement_been_shipped,
Replacement_UPS_Tracking_Number,
Claim_Type
FROM
BusOps_Transportation.stg.tracer_ups_extract_ct';
print @SqlCmd
EXECUTE sp_executesql @SqlCmd;
SET @RowsAffected = @@ROWCOUNT;
-- Move the populated spreadsheet to the production file share.
SET @ExecCmd = 'COPY "' + @ServerShare + '\' + @ExportFileName + '" "' + @FileShare + '"' + ' /Z /Y /V';
EXECUTE master.dbo.xp_cmdshell @ExecCmd;
set @FileShare2 = @FileShare + '\' + @ExportFileName
END TRY
BEGIN CATCH
SET @EventText = dbo.BuildErrorMessage(ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE());
RAISERROR(@EventText, 11, 1);
END CATCH
GO
--Quote me
March 11, 2019 at 2:44 pm
Instead of using [Sheet1$] as the table name, can you create a named range in the template spreadsheet (starting below the header row) and use that for the table name? Just writing to the sheet and hoping it starts in the right place may not be reliable.
If nothing else works and it insists on leaving a blank row, maybe you could hide that row in the template.
March 12, 2019 at 3:23 pm
hiding the row does work.
--Quote me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply