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
Hi, my result.
“3”;”ALMACEN CENTRAL”
“4”;”PLAZA LIMA SUR”
“5”;”PLAZA SAN MIGUEL”
“9”;”MEGA PLAZA”
Can you skip the quotes? “”
(Get-Content $OuputFile) | Foreach-Object {$_ -replace ‘”‘, “”} | Set-Content $OuputFile # optionally -Encoding ‘UTF8’
Hi,
Any way to use this via windows auth instead of sql user auth?
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?
how do I keep the date format in yyyy-MM-dd HH: mm: ss.fff
That is an excel problem. I hate this bug in EXCEL when working with CSV.
long string gets capped away with …..
for example instead of returning the whole string the dataset trims it like
the quick brown fox……
Many thanks. It helped me a lot.