Powershell script to append the resultset from multiple source to a csv

Question

I need help with this script. Basically I am connecting to multiple databases to get a dataset I need. And after capturing the dataset from all different databases, I need to write output it to a CSV file. Below is the code I basically came up with. The script is creating an output CSV file but it doesn't append all the data I captured. It only writes the last dataset captured. How do I go around this?

Need help to fix it.

$DB = Get-Content c:\ps\DBLIST.txt
foreach($Data in $DB)

{

Write-Host $Data
$strDB = $Data+".local"

$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = "Server=$strDB;Database=db;User ID=user;Password=password"
$con.open()

$qry = "select a, b, c, d from table1"

$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = $qry
$cmd.Connection = $con

$da = New-Object System.Data.SqlClient.SqlDataAdapter
$da.SelectCommand = $cmd
$ds = New-Object System.Data.Dataset
$da.Fill($ds)
$dr = $cmd.ExecuteReader()

Write-Host

$outFile = "C:\ps\OUTPUT.csv" 


    If ($dr.HasRows)
        {


          write-Host a     b     c     d

          While ($dr.Read())
            {

            Write-Host $dr["a"]         $dr["b"]        $dr["c"]        $dr["d"]

            }

        }
    Else
        {
          Write-Host There are no records found.  Try again.
        } 

$ds.Tables[0] | export-csv $outFile -NoTypeInfo -Force -Append
#$ds.Tables[0] | Export-Csv -Delimiter ','$outFile -Encoding "unicode"

Write-Host

$dr.Close()
$con.Close()

}

user4317867 · Accepted Answer · 2015-02-26 00:13:53Z

Instead of a ForEach loop, you can make use of the Powershell Pipeline via Foreach-Object. If you send the list of database servers down the pipeline, and then simply output the results inside the loop, Powershell will send all the results into the pipeline, effectively combining them. You can then take the final result and write it to the CSV file.

Note that I had to move the $outfile variable above the loop (where it belongs anyway). I also had to explicitly label the -Path parameter when I tested it in Powershell v2.0:

$outFile = "C:\TEMP\OUTPUT.csv" 

$DB = Get-Content c:\ps\DBLIST.txt
$DB | Foreach-Object {
    $Data = $_
    $strDB = $Data+".local"

    $con = New-Object System.Data.SqlClient.SqlConnection
    $con.ConnectionString = "Server=$strDB;Database=db;User ID=user;Password=password"
    $con.open()

    $qry = "select a, b, c, d from table1"

    $cmd = New-Object System.Data.SqlClient.SqlCommand
    $cmd.CommandText = $qry
    $cmd.Connection = $con

    $da = New-Object System.Data.SqlClient.SqlDataAdapter
    $da.SelectCommand = $cmd
    $ds = New-Object System.Data.Dataset
    $da.Fill($ds) | Out-Null   # prevent the number of records from going down the pipeline
    $dr = $cmd.ExecuteReader()

    # This is the magic right here -- it simply outputs the
    # contents of $ds.Tables[0] to the pipeline
    $ds.Tables[0] 

    $dr.Close()
    $con.Close()

} | Select-Object a,b,c,d  | Export-Csv -Path $outFile -Delimiter ',' -Encoding "unicode"

score 1 · Accepted Answer · 2014-07-29 08:24:43Z

1

This should work

$SomeObject | export-csv $outFile -NoTypeInfo -Append

Edit for PowerShell v2 where -Append doesn't exist: Collect all info in one object. Write this object to a file once done.

# before you start the loop:
$a = @()

# in your loop:
$a += $ds.Tables[0]

# after the loop:
$a | Export-Csv $outFile -NoTypeInformation

edited Jul 29, 2014 at 8:24

answered Jul 28, 2014 at 21:38

user2226112

2 Comments

user2885241 Over a year ago

They always have the same properties. Unfortunately the PS version we are using is 2.0 :( the Append is not working. Is there any clever way to get this done on 2.0?

user4317867 Over a year ago

Late to the party but here's a script that I pieced together. Takes multiple SQL queries and outputs the results into an Excel Workbook. One worksheet per query and you can specify the worksheet name. As an added bonus, the columns are autosized to fit, making things easier to read. stackoverflow.com/questions/27917115/…

Collectives™ on Stack Overflow

Powershell script to append the resultset from multiple source to a csv

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related