Now imagine skipping the CSV step and exporting data directly to Excel, complete with visually appealing formatting. Wouldn't that save time and effort and open the door to automation?
https://www.powershellgallery.com/packages/ImportExcel
You can import the module directly from there or do the manual download. I decided to use the import method. For that I would need to have the PSGallary as one of the registered repositories in the PowerShell
If you don't already have registered the Powershell Gallary as one of the repository, there are couple methods depending on the PowerShell version you have. I have the 5.x version and used the following command to register it.
Register-PSRepository -Default
If that does not work then try the full command:
Register-PSRepository -Name "myNuGetSource" -SourceLocation "https://www.myget.org/F/powershellgetdemo/api/v2" -PublishLocation "https://www.myget.org/F/powershellgetdemo/api/v2/Packages" -InstallationPolicy Trusted
Next I checked and it was now registered as an Un-Trusted source. Either I would need to add the -Force parameter to the Import-Module command or it will prompt me to confirm if I trust the source.
I decided to update the InstallationPolicy for it by issuing the command Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted to update that setting.
So far so good, great.
I copied the powershell command provided at the Powershell Gallary site to import the module.
Right off the bat I an ugly error message!
PS C:\WINDOWS\system32> Install-Module -Name ImportExcel
Install-Module : Administrator rights are required to install modules in 'C:\Program Files\WindowsPowerShell\Modules'. Log on to the computer with an account that has
Administrator rights, and then try again, or install 'C:\Users\Contaso\Documents\WindowsPowerShell\Modules' by adding "-Scope CurrentUser" to your command. You can also
try running the Windows PowerShell session with elevated rights (Run as Administrator).
At line:1 char:1
+ Install-Module -Name ImportExcel
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Install-Module], ArgumentException
+ FullyQualifiedErrorId : InstallModuleNeedsCurrentUserScopeParameterForNonAdminUser,Install-Module
Install-Module -Name ImportExcel-Scope CurrentUser
Success!
And finally, the one liner to export the query output to Excel.
Send-SQLDataToExcel -Connection "Server=SQLServer1\SQL2016AG01;Trusted_Connection=True;" -MsSQLserver -SQL "select @@servername SQLServer, name DBName from sys.databases" -Path sc.xlsx
At the minimum, you need to specify the Connection string, connection type (MsSQLServer), SQL query and the Excel file name.
Here is another example of same with some additional parameters.
Send-SQLDataToExcel -Connection "Server=SQLServer1\SQL2016AG01;Trusted_Connection=True;" -MsSQLserver -SQL "select @@servername SQLServer, name DBName from sys.databases" -AutoSize -BoldTopRow -FreezeTopRow -TableName Test -TableStyle "Light13" -WorkSheetname Test -Path sc.xlsx
If you would like to explore all the parameters, options etc. of Send-SQLDataToExcel look up the complete help file in the powershell:
Get-Help Send-SQLDataToExcel
Partial Screen Shot:
And if you would like to explore all the things you can do with the ImportExcel module:
Get-Command -Module ImportExcel
OR
(Get-Module ImportExcel).ExportedCommands
I must admit that this is a two or three pages blog for something I claim to be a one-liner. But we need Excel module in order for the one liner to work. I did not have it in my PowerShell so I also included the steps I followed to get it.