Powershell one liner to export data directly from SQL Server to Excel

Most of the times I use the CSV files whenever I need to import or export SQL Server data. And then if I need to do further analysis on the data or simply beautify the results to share with the users or colleagues, I simply open the CSV file in Excel and do the rest of the work manually.

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?

No surprise that there is indeed a powershell module for Excel at the Powershell Gallary site.

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

Ugh... Local Administrator rights are required to install a module at the computer level.  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-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.