Iron Maiden: the remaining six albums (2000-2021) ►
◄ Playing DVDs directly on an Android tablet in 2025
A simple example script to take some parameters, ask where to save the output then run multiple SQL queries (in this case a result set then two extracts) and export the results into separate sheets of an Excel file. Assumes that the user's account has permissions for the database(s) and tables needed.
You can run the script as normal or via a shortcut such as:
"C:\Program Files\PowerShell\7\pwsh.exe" -NoExit -ExecutionPolicy Bypass -Command "./ScriptName.ps1"
#Requires -Version 7.4
Clear-Host; Write-Host; Write-Host 'If needed the script will start by installing modules to interact with Excel and databases.'
If(-not(Get-InstalledModule ImportExcel))
{
Set-PSRepository PSGallery -InstallationPolicy Trusted
Install-Module ImportExcel -Confirm:$False -Force
}
If(-not(Get-InstalledModule DBATools))
{
Set-PSRepository PSGallery -InstallationPolicy Trusted
Install-Module DBATools -Confirm:$False -Force
}
Write-Host 'Modules okay.'; Clear-Host
Write-Host 'SCRIPT TITLE HERE'
Write-Host '-----------------'; Write-Host
$parameter1 = Read-Host 'Parameter 1'
$parameter2 = Read-Host 'Parameter 2'
Write-Host; Write-Host 'Please use the dialogue box to choose where to save your file.'; Write-Host
$output = ''
Add-Type -AssemblyName System.Windows.Forms
$saveFileDialog = New-Object -TypeName System.Windows.Forms.SaveFileDialog
$saveFileDialog.OverwritePrompt = $true
$saveFileDialog.InitialDirectory = [Environment]::GetFolderPath('MyDocuments')
$saveFileDialog.FileName = 'Export.xlsx'
$saveFileDialog.Title = 'Save As'
$saveFileDialog.Filter = "Excel documents (.xlsx)|*.xlsx"
If ($saveFileDialog.ShowDialog() -eq 'OK') { $output = $saveFileDialog.FileName }
If ($output -eq '') { Write-Host 'No filename specified.'; Exit }
Write-Host "Filename: $output"; Write-Host
$database = 'DBName'; $instance = 'FQDN.domain.co.uk'
$sheets = @('Total Population','Extract 1','Extract 2');
$query = "SET NOCOUNT ON; SELECT TOP 1000 * INTO #temp_table FROM table_name;"
$query = $query + "select * from #temp_table;"
$query = $query + "select * from #temp_table where field_name='$parameter1';"
$query = $query + "select * from #temp_table where field_name='$parameter2';"
if (Test-Path $output) { Remove-Item $output }
Write-Host "Fetching data..."; Write-Host
$sheet = 0; $datasets = Invoke-DbaQuery -SqlInstance $instance -Database $database -Query $query -As DataSet
foreach ($table in $datasets.Tables) {
$table | Select * -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors | Export-Excel -Path $output -AutoSize -FreezeTopRow -AutoFilter -NoNumberConversion InvoiceRef,Tax_Code,InvoiceRefNums -Append -WorksheetName ($sheets[$sheet]); Write-Host ' +'($sheets[$sheet]); $sheet = $sheet + 1
}
Write-Host; Start-Process $output
💬 Comments are off, but you can use the mail form to contact or see the about page for social media links.
