Formatting Powershell output from stored procedure

Question

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?

stej · Accepted Answer · 2011-03-30 19:48:56Z

3

select-object with expanded property might help:

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

answered Mar 30, 2011 at 19:48

stej

29.6k11 gold badges76 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NekoIme · Accepted Answer · 2011-03-30 20:03:34Z

0

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

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

answered Mar 30, 2011 at 20:03

NekoIme

361 silver badge1 bronze badge

1 Comment

Christopher Klein Over a year ago

still leaves the 'column1' header to the file. Nice to know the export-csv tag though

Collectives™ on Stack Overflow

Formatting Powershell output from stored procedure

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related