Use Powershell To Save SQL Query To CSV File

The SSMS application is very versatile and can be easily used to export queries you run into a simple comma separated file (CSV). This is great when your working with only one instance but what if you need to consolidate data from multiple instances in the environment?

With this example we use the Invoke-Sqlcmd command and you may need to import the module depending on your Powershell version. Do this by running these commands…

Install-Module sqlserver
Get-Command -ModuleName sqlserver

Using Powershell To Export To CSV

This is a simple example where we run a query grabbing all of the logins on an instance that are not system or sysadmin and exporting the data to a basic .CSV file. The Powershell command is “Export-Csv” and it has many more features but in this example we assume the defaults and just create a new CSV file.

Note: re-running will overwrite the file

$path = "C:\temp\logins.csv"
$sqlinstance = "CC-CLPFSSQL01\SQLT1302"

$query = "SELECT 
	[loginname],
	CAST([createdate] AS DATE),
	CAST([updatedate] AS DATE)
FROM master.sys.syslogins
WHERE sysadmin = 0
AND [loginname] NOT LIKE '#%'
ORDER BY createdate DESC"

$results = Invoke-Sqlcmd -Query $query -ServerInstance $sqlinstance
$results | Export-Csv -Path $path -NoTypeInformation

Export To CSV With Headers

In the above example you will notice that 2 of the columns are not named in the exported file. This is because we CAST the datetime fields to a DATE field but did not give them an Alias. If you changed the Date columns to the following you will have column headers.

	CAST([createdate] AS DATE) AS Create_Date,
	CAST([updatedate] AS DATE) AS Update_Date

Append To CSV File

After creating the file we might need to append to it instead of replacing it. This is useful for log type scenarios (db tales com) or any other situation where we do not want to lose the older data. Luckily the Export-Csv command also has an -Append option. (Available Powershell 3+).

Change the code above and add -Append

$results | Export-Csv -Path $path -Append -NoTypeInformation

Note: Create the file with the correct headers as the -Append will fail if they are changed.

---