Earlier, we discussed using PowerShell to import data from a spreadsheet into SQL Server using a method described here. Now, we want to do the exact reverse – export the data from SQL Server to a spreadsheet using PowerShell.
As you’ll notice in the linked post, it uses the ImportExcel module. Well, we’re going to use the same module here – partly to keep it simple but, mostly, because it is such a useful module. (Thanks Douglas Finke for creating and maintaining such a great module!)
To export the data, you will need one powerful line of code:
Send-SQLDataToExcel -Connection "Server=.;Trusted_Connection=True;" -MsSQLserver -SQL "select * FROM dbo.MockPersonData" -Path MockPersonData.xlsxSo let’s break down that one-liner.
Send-SQLDataToExcel – the name of the command.
Parameters:
- Connection – The connection string to the SQL Server database that the data is kept in. If you want to see all kinds of goodness with connection strings, I would suggest going here as that gives you all (and I do mean all) of the ways you can connect to SQL Server.
- MsSQLServer – Specifies the connection string is for the actual SQL server, not an ODBC connection.
- SQL – The actual SQL query to run against the connection specified
- Path – The spreadsheet that will be created
You’re probably thinking that’s all that the module will do. Nope, not even close. You can make header lines, make Excel see the data as a data table, conditional formatting within the tables, etc…
So. Much. Goodness!
If you want to see videos on just a portion of the goodness that can be done by the Import-Excel module, you can check out Douglas’ YouTube site found here.
Of course, there is always the help you can find within the module found by typing into PowerShell those magic words:
get-help Send-SQLDataToExcelAnd with that, we have yet again reached the end of this post. There are lots of great ways to get data out of SQL Server, but one of my favorite ways is with PowerShell and the Import-Excel module. So until next time!