Quick Export SQL Data to a Excel CSV file…

Here’s a quick way to get data off on of your SQL Server table and dump it to a CSV file.  Only 4 steps:

1. Make sure you are in SQLPS console, or have the community SQLPS (or SQLPSv2) module loaded ( ie. Import-Module SQLPS ) in either your PowerShell Console or ISE.

2. Have you query ready, then save it into a PowerShell variable:

$sql = “SELECT * FROM [AdventureWorks].[Production].[Location]”

3. Next one-liner will build the PowerShell object and exported to a *CSV file: (Execute in localhost only. Use -ServerInstance with the -database parameter if is needed t0 execute query)

Invoke-Sqlcmd -query $sql | Export-Csv -Path c:\temp\excelfile.csv -NoTypeInformation

4. Last line will open the file in Excel:

ii c:\temp\excelfile.csv

This surely beats going to SSIS for quick results!

For more information about the “Invoke-SQLcmd” use the help in PowerShell:

Help Invoke-SQLcmd -detailed