sent SQL query as formatted Excel .xlsx via email

  • 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

  • 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

  • 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.

  • Thom A - Saturday, March 2, 2019 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.

    If I remove formatting requirement, is there a way to sent the dataset as .xlsx (all within the SQL sproc)?

    --Quote me

  • 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]

  • 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/

  • 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

  • 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

  • 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.

  • 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