CODEWITHSUNDEEP
powershell
Christopher Klein
Christopher Klein

Reputation: 2793

Formatting Powershell output from stored procedure

I'm running into a small issue trying to get the output from a stored procedure into a text file via. Powershell.

#Connection Object
$cn = New-Object System.Data.SqlClient.SqlConnection(
    "Data Source=localhost; Database=test;User ID=test;Password=xyzzy;"
    )

$q = "exec usp_Users"

#Data Adapter which will gather the data using our query
    $da = New-Object System.Data.SqlClient.SqlDataAdapter($q, $cn)
#DataSet which will hold the data we have gathered
    $ds = New-Object System.Data.DataSet
#Out-Null is used so the number of affected rows isn't printed
    $da.Fill($ds) >$null| Out-Null
#Close the database connection
$cn.Close()

if($ds.Tables[0].Rows.Count -eq 0){   
write-host '0:No Data found'   
exit 2 
}  

$file = "C:\temp\" + "users" + $(Get-Date -Format 'MM_dd_yyyy') + ".txt"
$ds.Tables[0] |  out-File $file -encoding ASCII -width 255

Here is the output:

Column1
-----
USER_NAME,USER_TYPE
[email protected],MasterAdministrator
[email protected],UserAdministrator
[email protected],Users

How can I get rid of the 'Column1' and the underline?

Upvotes: 3

Views: 3454

Answers (2)

NekoIme
NekoIme

Reputation: 36

You can export a datatable directly into a csv file by using export-csv:

$ds.Tables[0] | export-csv tofile.csv -notypeinformation

Upvotes: 0

stej
stej

Reputation: 29449

select-object with expanded property might help:

ds.Tables[0] | Select-Object -expand Column1 | out-file..

Upvotes: 3

Related Questions