Exporting to Excel from PowerShell without having Excel installed
TLDR
In case you don’t have time reading my writing about PowerShell and the coding process and just would like to have a script up and running feel free to jump right to the end of the blog post where the final scripts are provided.
What’s this all about?
As a BI/Analytics Practitioner my most common tool for communicating data to business users is Excel. I have been working to build a modern analytics infrastructure leveraging OLAP cubes for a while now…however it often boils down to business partners (my customers) asking for raw data in an excel sheet *sigh*.
One notice in advance: I don’t call myself a PowerShell expert. I think I could do some decent programming but get to practice too rarely. So for sure there are ways to code this more elegantly. Additionally I come from a background programming with Java and love to stretch code out across multiple lines and do commands step by step. I have noticed that in the PowerShell Community there is some affinity for one-liners as well performing many operations piped together. That’s generally not my style to code, so sorry if you might think of it as lengthy. Anyway if you are reading this and do know better ways of coding please let me know and learn from you.
Prologue: My long story with Excel-Exports and tooling
I started off 6 years ago trying to do Excel exports with SSIS (from SQL Server 2008 back then). This was quite a nightmare…you had to work with an ODBC driver for excel which behaved strange in some situations and I couldn’t find any proper documentation back then not even thinking about decent error messages pointing to the source of the error. My colleagues from software development avoid the issue by exporting just csv however that’s not that appealing to the end user in certain scenarios (for me typically the auto-convert to excel did not work as intended). However SSRS did not support exporting to multiple sheets in Excel in Version 2008 so that did not work out either. Eventually my company bought Cubeware Cockpit which has a great report distribution engine and made the task of exporting excel files way easier. It is still my tool of choice today.
However, I now have an environment without this tool and therefore looked for an alternative. I remembered some PowerShell Scripts doing magic in exporting to Excel but they rely on the COM-API which calls Excel directly installed on the local machine. In contrast to that my regular excel export should run on the server which has no Excel installed (and probably shouldn’t have).
First Try: ExcelPSLib
The first solution I came up with after some research on the web was the module ExcelPSLib. So I started testing with this simple script:
| Import-Module dbatools; | |
| Import-Module ExcelPSLib; | |
| $credential = Get-Credential; | |
| # get results from table | |
| $result= Invoke-DbaQuery -SQLInstance localhost -Database master -Query "SELECT * FROM sys.tables" -SqlCredential $credential ; | |
| # debug the output | |
| $result | Out-GridView; | |
| $begin = Get-Date; | |
| # export to excel | |
| $result | Export-OOXML -FileFullPath test.xlsx -WorksheetName test -AutoFit -HeaderStyle WhiteSmoke; | |
| $end = Get-Date; | |
| echo "duration $($end-$begin)" |
Looks nice but hang on a sec….only one row brought back in excel…that’s strange as there should be four according to my query:
You can see from the following screenshot that just exporting the four rows did already take more than 5 seconds.
Actually dear reader I lied to you: I made a dumb mistake testing with just one row…then making the script more fancy (more about that later) and finally testing on the server with 4K rows. Then my jaw dropped as I realized that this export took over an hour! So please be smarter than me and test your code with a decent amount of data first J.
Well for sure I am using ExcelPSLib in the wrong way continuously overwriting just one row. I looked for an example of writing multiple rows but couldn’t find one. As far as I could see the more fancy examples are all about setting the content of an excel sheet row by row (with then different content).
So I finally gave ExcelPSLib up, headed over to sqlcommunity.slack.com and asked for help on exporting to Excel. I got an immediate response by Andy Levy (alevyinroc) recommending the ImportExcel module. So I gave that a try.
Final Solution: ImportExcel
Funny using ImportExcel to actually export to excel but I guess the name comes from the first features implemented. I happily discovered that compared to ExcelPSLib this module is more actively maintained (the latest release being from this month). Interestingly enough the same DLL (EPPlus) is used under the covers. So I changed the line related to the export and I was back in the game. I think that’s truly amazing how easy PowerShell Cmdlets can be used. I had some trouble installing the module by copying it to the server and unlocking it there…but the usage was pretty straightforward and simple.
So here’s the adapted test script for ImportExcel:
| Import-Module dbatools; | |
| Import-Module ImportExcel; | |
| $credential = Get-Credential; | |
| # get results from table | |
| $result= Invoke-DbaQuery -SQLInstance localhost -Database master -Query "SELECT * FROM sys.tables" -SqlCredential $credential ; | |
| # debug the output | |
| $result | Out-GridView; | |
| $begin = Get-Date; | |
| # export to excel | |
| #$result | Export-OOXML -FileFullPath test.xlsx -WorksheetName test -AutoFit -HeaderStyle WhiteSmoke; | |
| $result | Export-Excel $outputpath -WorksheetName test -AutoFilter -AutoSize -BoldTopRow -FreezeTopRowFirstColumn; | |
| $end = Get-Date; | |
| echo "duration $($end-$begin)"; |
This code now finished in less than 1 second and exported all four rows. In my test the first data row was formatted a little bit odd…I have no idea why…it didn’t happen to me with my business query so I’ll just leave it.
Remove technical columns
Taking a closer look on the result I realized that there are some columns which come out of the PowerShell structures.
Additional columns from internals
I found a great blog post by Shane O’Neill on how to remove that columns and adapted his code like this:
$result | Select-Object -Property * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | Export-Excel test.xlsx -AutoFilter -AutoSize -BoldTopRow -FreezeTopRowFirstColumn -WorksheetName test
Getting fancy
After the basics worked well I invested some time crafting a reusable PowerShell-Script. Typical export processes follow all the same basic pattern:
- There’s some SQL code with the query performed.
- This code is typically executed with some additional parameters (e.g. you would like to get a report monthly with the relevant data of the completed previous month)
- The file name of the excel file should resemble the execution so it should be something like <ReportTitle>_<supplement>.xlsx
- Finally the result should be sent to the business folks via e-mail
I still keep it quite basic here…I am not talking about generating an excel file with multiple reports on different sheets or sending multiple excel files in one e-mail. This is something reporting tools such as Cubeware Cockpit are great in and it would be doable in PowerShell as well….but that’s not the requirement I am currently dealing with…so why over-engineer? Long story short: One Export, One SQL File and one resulting e-mail with one Excel file.
Parameters
| param( | |
| [Parameter(Mandatory=$TRUE)] [ValidateNotNullOrEmpty()] [string] $databaseServer, | |
| [Parameter(Mandatory=$TRUE)] [ValidateNotNullOrEmpty()] [string] $databaseName, | |
| [Parameter(Mandatory=$TRUE)] [ValidateNotNullOrEmpty()] [string] $sqlFile, | |
| [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $parameters = '', | |
| [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $fileName = 'Export', | |
| [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $worksheetName = 'Export', | |
| [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $mailRecipient = 'martin.guth@3c-d.de', | |
| [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $fileNameSupplement = '_' + $(Get-Date -Format yyyyMMdd_HHmmss) | |
| ) |
I wrote a generic PowerShell-Script which exports reports based on the parameters it’s called with.
The straight-forward-ones IMHO are:
- $databaseServer: the server the query gets executed at
- $databaseName: the database the query gets executed at
- $sqlFile: the SQL file containing the reporting query to execute
- $worksheetName: the name of the worksheet inside the Excel file
- $mailRecipients: the e-mail-addresses of the export recipients
Now for the more complicated ones:
- $parameters -> Some TSQL which is appended to the top of the SQL file
- Imagine your query works with variables like @dateTimeFrom and @dateTimeTo for time slicing. Then you would pass a declare into that parameter such as
DECLARE
@dateTimeFrom DATETIME = '20190701 00:00',
@dateTimeTo DATETIME = '20190801 00:00'
;
One small gotcha is that you have to make sure that those variables are not declared within the TSQL again…you could just comment the respective declare block and uncomment it if you would like to do some testing in SSMS.
- In case you have no parameters the default is just ‘’ which appends an empty string to the file
- $fileName: that’s the basic file name your Excel file should be named with
- $fileNameSupplement: the supplement is the individual add-on to the file name. An example could be “201910” for an export of October 2019. Per default the current timestamp is added to the filename if this supplement is empty.
- Example1: $fileName = “test”, $fileNameSupplement = “201910”
resulting complete file name is “test_201910.xlsx” - Example 2: $fileName = “dummy”
resulting complete file name is “dummy_20191121_184801.xlsx” if the file is generated at November 21th 2019 at the time 18:48:01
- Example1: $fileName = “test”, $fileNameSupplement = “201910”
Script code
I hope you are able to read the code fluently and get what it does. I tried to add descriptive comments to most parts.
Syntactic sugar is the usage of a log file for each execution. That way I keep track of the report execution and can quickly identify errors.
Regarding errors: The whole script runs in a Try-Block and if an Exception is caught this is written to the log file as well. To make such an erroneous execution more visible this log file gets renamed from “*.log” to “*.log.err”. Additionally I write the excel file to a temp folder first and delete it there after a successful execution. In the case of an error it stays there for further investigation.
Regarding the assembly of the fileName, the fileNameSupplement and the extension “xlsx” there are a few lines which should preserve that this works whether the user gives “test.xlsx” or just “test” as fileName and this wouldn’t result in a file called “test.xlsx.xlsx”.
At the end the e-mail is sent in a few lines. Pretty straightforward in my view as well and I love PowerShell for making e-mail-delivery that easy.
So here’s the complete script at last:
| #exportReport.ps1 | |
| <# | |
| .SYNOPSIS | |
| Takes a query from a SQL file, adds parameters to it, executes it on a SQL Server and writes the result into an Excel file. Return value 0 indicates successful execution, Return value -1 indicates errors. | |
| .DESCRIPTION | |
| This script enables exporting query results to excel from a machine, where Excel is not installed. | |
| This is done leveraging the module ExcelPSLib. | |
| System requirements: | |
| - Powershell version 3 or greater with modules dbatools and ImportExcel installed | |
| - SQL Server 2008 or greater | |
| .PARAMETER databaseServer | |
| Name of the database server the query should be executed on | |
| .PARAMETER databaseName | |
| Name of the database the query should be executed within | |
| .PARAMETER sqlFile | |
| Path to a file with a SQL query. | |
| .PARAMETER parameters | |
| List of parameters with their assigned values in a DECLARE-statement. | |
| This have to be the same parameters like those contained in the query (if any). | |
| Typically queries running in a specified interval are parameterized regarding the time slice the information is needed for. | |
| Example: | |
| DECLARE | |
| @dateTimeFrom DATETIME = '20190701 00:00', | |
| @dateTimeTo DATETIME = '20190801 00:00' | |
| .PARAMETER fileName | |
| (Base) Name for the generated file (default Export) | |
| .PARAMETER fileNameSupplement | |
| additional file name with separator (default timestamp): the defaults fileName Export and fileNameSupplement timestamp will resolve to something like "Export_20191126_144401.xlsx" | |
| .PARAMETER worksheetName | |
| name of the excel worksheet with the exported data (default Export) | |
| .PARAMETER mailRecipient | |
| Array of E-Mailadresses the report should be sent to. Example: 'test@info.com', 'mock@info.de' | |
| .EXAMPLE | |
| & X:\Scripts\exportReport.ps1 -fileNameSupplement '_201910' -parameters 'DECLARE @dateTimeFrom DATETIME = ''20191001'', @dateTimeTo DATETIME = ''20191101''' -databaseServer 'mydbserver' -databaseName 'mydb' -sqlFile 'X:\scripts\myexport.sql' -fileName 'myexport' -mailRecipient 'mail@info.de', 'test@info.de' | |
| .NOTES | |
| #> | |
| param( | |
| [Parameter(Mandatory=$TRUE)] [ValidateNotNullOrEmpty()] [string] $databaseServer, | |
| [Parameter(Mandatory=$TRUE)] [ValidateNotNullOrEmpty()] [string] $databaseName, | |
| [Parameter(Mandatory=$TRUE)] [ValidateNotNullOrEmpty()] [string] $sqlFile, | |
| [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $parameters = '', | |
| [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $fileName = 'Export', | |
| [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $worksheetName = 'Export', | |
| [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string[]] $mailRecipient = 'test@vcab.de', | |
| [Parameter(Mandatory=$FALSE)] [ValidateNotNullOrEmpty()] [string] $fileNameSupplement = '_' + $(Get-Date -Format yyyyMMdd_HHmmss) | |
| ) | |
| # Function to print all parameter values | |
| # shamelessly copied from https://stackoverflow.com/questions/21559724/getting-all-named-parameters-from-powershell-including-empty-and-set-ones | |
| # turns out that the most rated approach with the nicely layed out function does not work with invocation using & | |
| # thus I used the solution by Jason Kreissler ....pardon me for copying that one-liner | |
| function printParameters { | |
| (Get-Command -Name $PSCommandPath).Parameters | Format-Table -AutoSize @{ Label = "Key"; Expression={$_.Key}; }, @{ Label = "Value"; Expression={(Get-Variable -Name $_.Key -EA SilentlyContinue).Value}; } | |
| } | |
| Try | |
| { | |
| # import necessary modules | |
| Import-Module dbatools; | |
| Import-Module ImportExcel; | |
| # set up variables | |
| $tempFolder = 'X:\Scripts\temp'; # temporary folder to store information needed only during script execution | |
| $logFolder = 'X:\Scripts\log'; # folder to store logging information from script exection | |
| $logFile = $logFolder + "\exportReport_$(Get-Date -Format yyyyMMdd_HHmmss).log"; # fileName of the log file | |
| $PSEmailServer = "myMailServer"; # address of the e-mail-server | |
| echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Starting script execution. Parameters are as following: " > $logFile; | |
| printParameters >> $logFile; | |
| # add supplement to filename | |
| # derive file extension: code assumes that there is just one dot in the fullName separating the fileName from the extension | |
| $originalFileName = $fileName; | |
| If ($filename.split('.').length -gt 2) | |
| { | |
| echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [WARNING] Filename "$fileName" contains multiple dots. Script expects at max one dot separating the extension from the name. The part after the first dot will not be processed into the final filename." >> $logFile; | |
| } | |
| $fileExtension = | |
| If ($filename.split('.')[1] -ne $null) | |
| { | |
| $filename.split('.')[1] | |
| } | |
| Else | |
| { | |
| 'xlsx' | |
| }; | |
| $fileName = $filename.split('.')[0] + $fileNameSupplement + '.' + $fileExtension; | |
| echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Supplement ""$fileNameSupplement"" has been added to provided fileName. Complete FileName is now ""$fileName""." >> $logFile; | |
| # read SQL query into variable $sql and add parameter declaration to sqlFile | |
| $sql = Get-Content $sqlFile; | |
| $sql = $parameters + " | |
| " + $(Get-Content $sqlFile); | |
| # echo for debugging | |
| #echo $sql; | |
| echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Parameters added to SQL. Executing query now." >> $logFile; | |
| # execute query on database server | |
| $result= Invoke-DbaQuery -SQLInstance $databaseServer -Database $databaseName -Query $sql ; # -SqlCredential | |
| echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Query execution finished. Writing results to Excel now." >> $logFile; | |
| # write results to excel | |
| $outputpath = $tempFolder + '\' + $fileName; | |
| #echo $outputpath; | |
| #remove internal columns... thanks @Shane O'Neill (https://nocolumnname.blog/2017/04/27/using-excludeproperty-in-select-object/) | |
| # export | |
| $result | Select-Object -Property * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | Export-Excel $outputpath -AutoFilter -AutoSize -BoldTopRow -FreezeTopRowFirstColumn -WorksheetName $worksheetName | |
| echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Export to Excel finished. Sending e-mail to $mailrecipient." >> $logFile; | |
| # prepare and send e-mail | |
| $encoding = New-Object System.Text.utf8encoding; | |
| $mailsubject = "Export $originalFileName"; | |
| $mailbody = "..."; | |
| Send-MailMessage -To $mailrecipient -From "powershell@abc.com" -Subject $mailsubject -Body $mailbody -Encoding $encoding -Attachments $outputpath; | |
| echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Mail sent." >> $logFile; | |
| echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [INFORMATION] Remove temporary export file after successful execution." >> $logFile; | |
| Remove-Item $outputpath; | |
| $returnValue = 0; | |
| } | |
| Catch | |
| { | |
| echo "$(Get-Date -Format "yyyyMMdd HH:mm:ss") [ERROR] Powershell exception occurred. For details see next lines." >> $logFile; | |
| # get error | |
| $_ >> $logFile; | |
| # Rename the log file to show erroneous executions easily | |
| Rename-Item $logFile "$logFile.err"; | |
| $logFile = "$logFile.err"; | |
| # set return value | |
| $returnValue = -1; | |
| } | |
| Finally | |
| { | |
| Exit $returnValue; | |
| } |
A typical call could look like this (here I am running some logic to determine the last complete month and to prepare the supplement):
| [String] $dateFrom = $(Get-Date).AddMonths(-1).ToString("yyyyMM01 00:00:00") | |
| [String] $dateTo = Get-Date -Format "yyyyMM01 00:00:00" | |
| [String] $month = $dateFrom.Substring(0,6) | |
| echo "$dateFrom $dateTo $month"; | |
| & "X:\Scripts\exportReport.ps1" -databaseServer "mydbserver" -databaseName "mydb" -sqlFile "mySqlFile.sql" -fileName "FancyExport" -fileNameSupplement "_$month" -parameters "DECLARE @dateTimeFrom DATETIME = '$dateFrom', @dateTimeTo DATETIME = '$dateTo';" |