CODEWITHSUNDEEP
sql-serverpowershell
Christopher Jack
Christopher Jack

Reputation: 97

Export return of SQL script to Excel document using powershell

At the moment I have the following code which grabs the return table and outputs it into a CSV file.

Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location
$SQLServer = "localhost"
$today = (get-date).ToString("dd-MM-yyyy")
$DBName = "ZoomBI"
$ExportFile = "\\Shared_Documents\FC Folder\Despatch\Brexit Files\DHL\DHL "+$today+".csv"
$Counter = 0
$Storedprocedure = "EXEC [dbo].[DHLDeliveries]"

while ( $true )
{
    # Remove the export file
    if (Test-Path -Path $ExportFile -PathType Leaf) {
        Remove-Item $ExportFile -Force
    }
    # Clear the buffer cache to make sure each test is done the same
    $ClearCacheSQL = "DBCC DROPCLEANBUFFERS"
    Invoke-Sqlcmd -ServerInstance $SQLServer -Query $ClearCacheSQL
    # Export the table through the pipeline and capture the run time. Only the export is included in the run time.
    $sw = [Diagnostics.Stopwatch]::StartNew()
    Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DBName -Query $Storedprocedure | Export-CSV -Path $ExportFile -NoTypeInformation
    $sw.Stop()
    $sw.Elapsed
    $Milliseconds = $sw.ElapsedMilliseconds    
    $Counter++
    Exit
}

However, instead of that I need to be able to output the results to an Excel document with two sheets and put the results into each sheet.

# Create a Excel Workspace 
    $excel = New-Object -ComObject Excel.Application 
 
# make excel visible 
    $excel.visible = $true 
 
# add a new blank worksheet 
    $workbook = $excel.Workbooks.add() 
 
# Adding Sheets 
    foreach($input in (gc c:\temp\input.txt)){ 
        $s4 = $workbook.Sheets.add() 
        $s4.name = $input 
    } 
 
# The default workbook has three sheets, remove them 
    ($s1 = $workbook.sheets | where {$_.name -eq "Sheet1"}).delete()  
 
#Saving File 
    "`n" 
    write-Host -for Yellow "Saving file in $env:userprofile\desktop" 
    $workbook.SaveAs("$env:userprofile\desktop\ExcelSheet_$Today.xlsx")

Can anyone help ?

Upvotes: 0

Views: 449

Answers (1)

Randy in Marin
Randy in Marin

Reputation: 1143

I would take a look at the ImportExcel module. It took me 2 lines of code to create an excel document with two sheets.

https://www.powershellgallery.com/packages/ImportExcel/5.4.2

https://www.youtube.com/watch?v=fvKKdIzJCws&list=PL5uoqS92stXioZw-u-ze_NtvSo0k0K0kq

Upvotes: 1

Related Questions