PowerShell Script output

Question

Post reply

PowerShell Script output

Bron Tamulis

Ten Centuries

Points: 1090
More actions
December 6, 2021 at 10:35 pm

#3961016

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16153 More actions · Answer 1

reading the manual is normally advisable as it does contain the information one is looking most of the of the times.

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-7.2

Bron Tamulis Ten Centuries Points: 1090 More actions · Answer 2

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