PowerShell Script output
-
I have a power shell sql script that works as I desire except the top line of the excel (csv file it creates) is not desired.
The csv file created is used as a source file for another system - I need to eliminate the Power Shell Dialogue in first row
Here's the script:
# SQL Connection Variables - Change
$SQLServer = "XXXX\GP2018"
$SQLDBName = "FARGO"
$SQLUsername = "sa"
$SQLPassword = "XXXXX"
# Where to save the output CSV file - Change
$OuputFile = "c:\SQL\SQL_Export.csv"
# Your SQL Query - Change
$SqlQuery = "SELECT rtrim([GPCustId]) as GPCustID
,rtrim([CustBalance]) as CustBalance
,rtrim([USERDEF2]) as USERDEF2
,rtrim([YGLBalance]) as YGLBalance
FROM [$SQLDBName].[dbo].[X_CustBalances]"
# 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 "GPCustId","CustBalance","USERDEF2","YGLBalance" | Export-Csv $OuputFile
-
reading the manual is normally advisable as it does contain the information one is looking most of the of the times.
-
Thank you very much - I was able to determine that adding "-NoTypeInformation" to the output file command line at end resolved the issue.
I did try to research independently before posting - just wasn't able to figure out how to correct.
This is my first time dealing with PowerShell and exporting SQL data to a network share.
Regards,
Bron
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply