PowerShell – How to save an SQL query results to an CSV file

The following 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 connections variables at the top of the script along with the $OuputFile and your SQL query at $SqlQuery

# SQL Connection Variables - Change
$SQLServer = "SQLServerName.mydomain.local"  
$SQLDBName = "Database_Name" 
$SQLUsername = "SQLReaderUser"
$SQLPassword = "MySQLPassword"

# Where to save the output CSV file - Change
$OuputFile = "c:\scripts\SQL_Export.csv"

# Your SQL Query - Change
$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

8 thoughts on “PowerShell – How to save an SQL query results to an CSV file”

  1. Hi, my result.

    “3”;”ALMACEN CENTRAL”
    “4”;”PLAZA LIMA SUR”
    “5”;”PLAZA SAN MIGUEL”
    “9”;”MEGA PLAZA”

    Can you skip the quotes? “”

    Reply
  2. (Get-Content $OuputFile) | Foreach-Object {$_ -replace ‘”‘, “”} | Set-Content $OuputFile # optionally -Encoding ‘UTF8’

    Reply
  3. Exception calling “Fill” with “1” argument(s): “Login failed for user ‘********’.”
    At C:\PawerBI\CmdFile\Job.ps1:37 char:1
    + $SqlAdapter.Fill($DataSet)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

    May i know what to check if the user cannot run the same script?

    Reply
  4. long string gets capped away with …..
    for example instead of returning the whole string the dataset trims it like

    the quick brown fox……

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.