Powershell - Separate output based on Server + export to Excel

Question

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

Maximilian Burszley · Accepted Answer · 2019-06-24 19:45:56Z

2

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

edited Jun 24, 2019 at 19:45

answered Jun 24, 2019 at 19:27

Maximilian Burszley

19.9k7 gold badges39 silver badges68 bronze badges

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

8 Comments

user 9191 Over a year ago

I was close to it when i added the select-object statement, but thanks again @theIncorrigible1

user 9191 Over a year ago

invalid argument: @InvokeParams | Select-Object -Property *, @{Server=$SERVER}

Maximilian Burszley Over a year ago

@user9191 I had forgotten the syntax on props, check my update now.

Maximilian Burszley Over a year ago

Curse you, Semicolons!

user 9191 Over a year ago

It does not export the data - also not able to see the output on shell as well. During the process , for one of the databases: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

|

Collectives™ on Stack Overflow

Powershell - Separate output based on Server + export to Excel

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related