Redirecting PowerShell Output To a .csv file

Question

Hello,

I have following script. Having issues with directing to a .csv or a text file. Help will be greatly appreciated.

$Output=foreach ($svr in get-content "D:\DBA\PSScripts\SQLServerList.txt" ) {
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=master;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "SELECT
     @@SERVERNAME as ServerName,
    SERVERPROPERTY('ProductVersion') AS BuildNumber,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('ProductUpdateLevel') AS UpdateLevel,
    SERVERPROPERTY('ProductUpdateReference') AS UpdateReference,
    SERVERPROPERTY('ProductMajorVersion') AS Major,
    SERVERPROPERTY('ProductMinorVersion') AS Minor,
    SERVERPROPERTY('ProductBuild') AS Build"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize
}
$Output | Out-File "D:\DBA\PSScripts\output.txt" -Force

Thanks.
Victor

Answer 1

Try this:

$dt = new-object "System.Data.DataTable"        # create the datatable once
get-content "D:\DBA\PSScripts\SQLServerList.txt"  |
    ForEach-Object{
        $cn = new-object System.Data.SqlClient.SqlConnection "server=$_;database=master;Integrated Security=sspi"
        $cn.Open()
        $sql = $cn.CreateCommand()
        $sql.CommandText = "SELECT
        @@SERVERNAME as ServerName,
        SERVERPROPERTY('ProductVersion') AS BuildNumber,
        SERVERPROPERTY('Edition') AS Edition,
        SERVERPROPERTY('ProductLevel') AS ProductLevel,
        SERVERPROPERTY('ProductUpdateLevel') AS UpdateLevel,
        SERVERPROPERTY('ProductUpdateReference') AS UpdateReference,
        SERVERPROPERTY('ProductMajorVersion') AS Major,
        SERVERPROPERTY('ProductMinorVersion') AS Minor,
        SERVERPROPERTY('ProductBuild') AS Build"
        $rdr = $sql.ExecuteReader()
        $dt.Load($rdr)              # this should merge the data with existing data
        $cn.Close()
}
$dt | Export-CSV D:\DBA\PSScripts\output.txt -NoTypeInfo -Force

Keep in mind that a CSV cannot preserve the data type of the exported data. Everything in a CSV is text. If you want to preserve the data types you'll have to write the data to XML.

Answer 2

Rich,

Thanks for the response. Only issue is that it is writing to the csv file only one row instead of 89 rows info that is part of get-content input file.

Victor