1

I am currently working on an automation which requires to export sql results to excel data. I want to do this via SQL query. Few options i know are as below, but before i start exploring these things. I would like to know the best possible approach .

PS - It would be really great if there is a way to dynamically create excel during query execution and export data in multiple excel sheets.

  1. OPENROWSET
  2. bcp_cmd
Mohana Krishnan A R
  • 11
  • 1
  • 3
  • 1
    3. SQL Server Integration Services (SSIS) – KeithL Feb 21 '20 at 18:23
  • Because you reference BCP, presumably your SQL query is, or could be, saved in a text file. If that's the case, an alternative is execsql.py (https://pypi.org/project/execsql/), which will export data to an ODF workbook, which Excel will open. Disclaimer: I wrote execsql. – rd_nielsen Feb 21 '20 at 19:59
  • Does this answer your question? [T-SQL: Export to new Excel file](https://stackoverflow.com/questions/9086880/t-sql-export-to-new-excel-file) – TylerH Jan 03 '22 at 19:06

3 Answers3

0

You can CREATE VIEW and utilize that view in Excel 2016 under its data connections through PowerQuery. Views are preferred since they are managed independently in the server, and provide realtime data results without requiring a full query to be embedded in the Excel file. The resulting set exists in the workbook as a refresh-able table. Results that need to be recorded should be done via new workbooks or UPDATE's back to the server in a separate script.

enter image description here

In the PowerQuery Editor, Home tab, click Advanced Editor. The database connection string and call to the server is below. You can also dynamically pass parameters from an Excel table to the query utilizing a table in the Name Manager.

Excel tab, table name: tbl_Parameters

    A         B
1 StartDate  01/01/2020
2 EndDate    02/01/2020

let
   Source = Sql.Database("ServerName" , "Database", [Query="

DECLARE @Start_Date AS datetime
DECLARE @End_Date AS datetime

SET @Start_Date = '"&StartDate&"'
SET @End_Date = '"&EndDate&"'


SELECT * FROM uvw_product
WHERE item_sold_date BETWEEN
@Start_Date AND @End_Date

"])
in
   Source
hSin
  • 364
  • 2
  • 12
0

A while back I cobbled this Powershell script together.
It querys sql server data, saves as csv, formats it and saves as xls, then mails via smtp.
You can set up a windows scheduled task to automate it.

There's also an import xls module for powershell.

Import-Module Sqlps -DisableNameChecking;

#execute mysql query as excel
$Server = "DB SERVER";
$Database = "DBNAME";

$Query = @"
*SELECT QUERY HERE*
"@
$a = Get-Date
#note: if you run get-location from ide, it will use the ide path instead of the script path 
$currentLocation = Split-Path -Parent $PSCommandPath
$FilePath = $currentLocation + "\CSVName.csv"
$SavePath = $currentLocation + "\XLSFileName" +$a.Day+ $a.Month + $a.Year + ".xls"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $Server; Database = $Database; User ID = DBUSERNAME; Password = PASSWORD";
$SqlConnection.Open()
$sqlcmd = $SqlConnection.CreateCommand()
$sqlcmd.Connection = $SqlConnection
$sqlcmd.CommandText = $Query


$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $sqlcmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0] | Export-Csv -notypeinformation $FilePath


$SqlConnection.Close()
#Invoke-Sqlcmd -Query $Query -ConnectionString $SqlConnection.ConnectionString | Export-Csv -notypeinformation $FilePath

#release memory function
function Release-Ref($ref){
    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}

#format excel to display correct date
$objExcel = new-object -comobject excel.application  
$objWorkbook = $objExcel.Workbooks.open($FilePath)
$objWorksheet = $objWorkbook.Worksheets.Item(1) 
$objRange = $objWorksheet.UsedRange
[void] $objRange.EntireColumn.Autofit()
$objWorkbook.Saved = $True
$objExcel.DisplayAlerts = $False
$objWorkbook.SaveAs($SavePath,1)
$objExcel.Quit()

#release memory
Release-Ref($objWorksheet) 
Release-Ref($objWorkbook) 
Release-Ref($objExcel)

#create mail
$smtpServer = "SMTPSERVER"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)

$att = new-object Net.Mail.Attachment($SavePath)
$msg = new-object Net.Mail.MailMessage
$msg.Subject = "EMAIL SUBJECT"
$msg.From = "FROM EMAIL"
#$msg.To.Add("TO EMAIL 1")
$msg.To.Add("TO EMAIL 2")
$msg.Body = @"
Hi,

MSG BODY HERE

Best Regards
"@
$msg.Attachments.Add($att)

$smtp.Send($msg)
$att.Dispose()
Jeff
  • 810
  • 8
  • 18
0

Download and install the 64-bit or 32-bit version of the driver, based on what you have installed.

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Then, you should be able to run this.

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

Notice: this may not work if you have one 32-bit technology and one 64-bit technology. In this case, you may need to employ some kind of workaround.

See this link for additional ideas of how to integrate SQL Server and Excel.

https://solutioncenter.apexsql.com/how-to-import-and-export-sql-server-data-to-an-excel-file/

ASH
  • 20,759
  • 19
  • 87
  • 200