CODEWITHSUNDEEP
powershell
user 9191
user 9191

Reputation: 747

Powershell - Separate output based on Server + export to Excel

Running a sql script for a list of databases. The output on powershell is not separated. How could I separate them based on server name. 

$SERVERS = gc "C:\Users\listOfServers.txt"

foreach ($SERVER in $SERVERS) { 
 $InvokeParams = @{
 Server = $SERVER
 Database = 'test'
 Username = 'admin'
 Password = 'testpassword'
 InputFile = 'C:\business.sql'
}
Invoke-SqlCmd @InvokeParams 
}

Right now my output looks like this :

ValueDate: 1/30/2019 12:00:00 AM
PrevValueDate: 1/29/2019 12:00:00 AM
Count:100

ValueDate: 3/30/2019 12:00:00 AM
PrevValueDate: 3/29/2019 12:00:00 AM
Count:200

ValueDate: 4/30/2019 12:00:00 AM
PrevValueDate: 4/29/2019 12:00:00 AM
Count:2100

ValueDate: 11/30/2019 12:00:00 AM
PrevValueDate: 11/29/2019 12:00:00 AM
Count:12200

Goal is : Server 1 (output server 1) server 2 (output server 2)

I would like to add a parameter that gives the server name for each output- or sort like an Id to separate them.

Goal is to export the output into an Excel sheet - not working at the moment

 $out = Invoke-SqlCmd @InvokeParams | Format-Table
 $path = 'C:\Users\test1.csv'
 $out | Export-Csv -Path $path 
 Invoke-Item -Path $path

Upvotes: 0

Views: 71

Answers (1)

Maximilian Burszley
Maximilian Burszley

Reputation: 19684

You can use calculated properties to achieve this:

$SERVERS = gc "C:\Users\listOfServers.txt"

$out = foreach ($SERVER in $SERVERS) { 
    $InvokeParams = @{
        Server = $SERVER
        Database = 'test'
        Username = 'admin'
        Password = 'testpassword'
        InputFile = 'C:\business.sql'
    }
    Invoke-SqlCmd @InvokeParams | Select-Object -Property *, @{L='Server'; E={$SERVER}}
}

$path = 'C:\Users\test1.csv'
$out | Export-Csv -Path $path 
Invoke-Item -Path $path

Upvotes: 2

Related Questions