Thursday, July 11, 2019

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.

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.



1 comment:

  1. Towards the end of my original post I have "Get-Module ImportExcel | get-member" to get all the commands available in the module. I have now fixed that error in my blog but right now there is no way for me to republish this to the https://www.sqlservercentral.com where all my blogs are currently syndicated.

    Please accept my sincere apologies.


    (Get-Module ImportExcel).ExportedCommands

    ReplyDelete