But what if I could just skip the CSV and export data directly to Excel format? Wouldn't that save me time and efforts and also help me to automate if I wanted to?
No surprise that there is indeed a powershell module for Excel at the Powershell Gallary site.
https://www.powershellgallery.com/packages/ImportExcel/5.2.0
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 get the following error message.
Now I could relaunch the powershell as Administrator but since I am only testing/playing around, I decided to take up the second suggestion in the error message and add the -Scope Currentuser to the import-module command.
Install-Module -Name ImportExcel -RequiredVersion 5.2.0 -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.