Power shell script to create excel file and load data by query

Question

Hi,

Please help me to get a powershell script to generate a excel file and send by DB mail.

regards,

Answer 1

Greetings,

Here is a powershell script to create an excel file and load data by query:

# Import the required modules
Import-Module -Name ImportExcel
Import-Module -Name SQLServer
# Set the server, database, and query details
$serverName = "YourServerName"
$databaseName = "YourDatabaseName"
$query = "SELECT * FROM YourTable"
# Set the output Excel file path
$excelFilePath = "C:\Path\To\Output\File.xlsx"
# Set the DB mail details
$smtpServer = "YourSMTPServer"
$senderAddress = "sender@example.com"
$recipientAddress = "recipient@example.com"
$mailSubject = "Excel file generated"
$mailBody = "Please find the attached Excel file."
# Connect to the SQL Server
$sqlConnection = New-SqlConnection -ServerInstance $serverName
# Execute the query and retrieve the data
$queryResult = Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $query
# Create a new Excel workbook and add the data
$workbook = New-ExcelWorkbook
$worksheet = $workbook | Add-ExcelWorksheet -WorksheetName "Data"
$worksheet | Write-ExcelRange -StartCell "A1" -Value $queryResult -AutoFit
# Save the Excel workbook to the specified file path
$workbook | Save-ExcelWorkbook -Path $excelFilePath
# Send the email with the Excel file attachment
Send-MailMessage -SmtpServer $smtpServer -From $senderAddress -To $recipientAddress -Subject $mailSubject -Body $mailBody -Attachments $excelFilePath
# Cleanup - remove the SQL connection and close the Excel application
$sqlConnection.Close()
$workbook.Application.Quit()

Make sure you have the required modules installed before running the script.

1. ImportExcel (to create Excel files) and SQLServer (to connect to and query SQL Server). These modules can be installed using the PowerShell Gallery (e.g. Install-Module -Name ImportExcel, Install-Module -Name SQLServer).
2. Change the variables at the top of the script to suit your specific environment and needs.
3. Set the appropriate SQL Server details for the $serverName and $databaseName variables.
4. Update the $query variable with the desired query.
5. Modify the $excelFilePath variable to specify the desired output file path and name. Set the $smtpServer, $senderAddress, $recipientAddress, $mailSubject and $mailBody variables in the DB mail details.

After making the necessary changes, you can run the script. Generates an Excel file containing query data and sends it as an email attachment using the specified DB Mail settings. 

Hope it helped!