Powershell – An example of how to save a queries results to a CSV file

The below is an example PowerShell script to connect to a Microsoft SQL Database and dump the results of a query into a CSV file.

You will need to change the variables at the top along with the column names against the line starting “$DataSet.Tables[0] | select”

#Connect to SQL and run QUERY 
$SQLServer = "SQLServerName.mydomain.local"  
$SQLDBName = "Database_Name" 
$SQLUsername = "SQLReaderUser"
$SQLPassword = "MySQLPassword"

$OuputFile = "c:\scripts\SQL_Export.csv"

$SqlQuery = "SELECT  
	rtrim(EmployeeNumber) as EmployeeNumber,
	rtrim(JobTitle) as JobTitle,
	rtrim(Department) as Department,
	rtrim(Company) as Company,
	rtrim(Location) as Location,
	rtrim(CostCentre) as CostCentre,
	rtrim(ManagerEmployeeNumber) as ManagerEmployeeNumber
  FROM [$SQLDBName].[dbo].[Employee_Basic]"
  
##Delete the output file if it already exists
If (Test-Path $OuputFile ){
	Remove-Item $OuputFile 
}
 
Write-Host "INFO: Exporting data from $SQLDBName to $OuputFile" -foregroundcolor white -backgroundcolor blue
 
## - Connect to SQL Server using non-SMO class 'System.Data': 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $SQLUsername; Password = $SQLPassword" 
 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
$SqlCmd.CommandText = $SqlQuery 
$SqlCmd.Connection = $SqlConnection 
 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
$SqlAdapter.SelectCommand = $SqlCmd 
 
$DataSet = New-Object System.Data.DataSet 
$SqlAdapter.Fill($DataSet) 
$SqlConnection.Close() 

#Output RESULTS to CSV
$DataSet.Tables[0] | select "EmployeeNumber","JobTitle","Department","Company","Location","CostCentre","ManagerEmployeeNumber" | Export-Csv $OuputFile