Add column and assign value to an existing array

Question

I want to add a column and a value to an existing array generated from a CSV.

This is my code:

$mycsv = "C:\Users\test\Documents\serveurs.csv"
$servers = Import-Csv $mycsv

This is the result:

ServerName                              Ip
----------                              --
Castor                                  172.22.0.64
Pollux                                  172.22.0.67

I want to add new column with value for each ServerName, like this:

ServerName                              Ip                  Available
----------                              --                  ---------
Castor                                  172.22.0.64         Yes
Pollux                                  172.22.0.67         No

I tried the code below:

$item = New-Object PSObject
$item | Add-Member -Type NoteProperty -Name 'Available' -Value 'Yes'

$servers += $item

But it's actually not working, can anyone help me?

The final idea is to have value assigned if the server is reachable or not over the network, using Test-Connection cmdlet.

Ansgar Wiechers · Accepted Answer · 2019-05-22 13:55:32Z

10

You could use a calculated property for determining the availability of each server:

$csv1 = 'C:\Users\test\Documents\serveurs.csv'
$csv2 = 'C:\Users\test\Documents\serveurs2.csv'

Import-Csv $csv1 |
    Select-Object ServerName, Ip,
        @{n='Available';e={[bool](Test-Connection -Count 1 $_.Ip 2>$null)}} |
    Export-Csv $csv2 -NoType

Note, however, that even when limiting the test to a single probe per server this may take some time, since all items are processed sequentially. If you have a lot of servers to test, you may want to run the checks as parallel jobs:

$csv = 'C:\Users\test\Documents\serveurs.csv'

$servers = Import-Csv $csv

# clear job queue
Remove-Job *
# load job queue with test jobs
$servers | ForEach-Object {
    Start-Job -ScriptBlock {
        $args[0]
        [bool](Test-Connection -Count 1 $args[0] 2>$null)
    } -ArgumentList $_.Ip
}
# wait for jobs to finish
do {
    Start-Sleep -Milliseconds 100
} while (Get-Job -State 'Running')

# gather job results into hashtable
$availability = @{}
Get-Job | ForEach-Object {
    $result = Receive-Job -Id $_.Id
    $availability[$result[0]] = $result[1]
    Remove-Job -Id $_.Id
}

# add availability column to server data and export back to CSV
$servers |
    Select-Object ServerName, Ip, @{n='Available';e={$availability[$_.Ip]}} |
    Export-Csv $csv -NoType

edited May 22, 2019 at 13:55

answered Jun 17, 2014 at 10:01

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

2 Comments

AlexPawlak Over a year ago

Much better answer than mine, I need to take a closer look on this script to learn more about hashtables and structure in general. Can you explain me what exactly is "e=" clause? (in e={$availability[$_.Ip]) ? It just takes value from script-expression, right?

Ansgar Wiechers Over a year ago

@Koliat Check the TechNet article on calculated properties. e is short for Expression.

mjolinor · Accepted Answer · 2014-06-17 10:42:38Z

Populating the field value aside, another option for getting the property added is to use Select-Object.

$csv = 'C:\Users\test\Documents\serveurs.csv'
$servers = Import-Csv $csv | select *,Availability

Then populate availability however you choose.

If you're going to use background jobs to multithread I think you'd be better off breaking the server names up into groups and setting a background job to work on each group. It takes about 5 seconds to set up and tear down a background job, and using them for trivial tasks like pinging a single computer can be counter-productive.

It's more work to code, but if you want to multi-thread that I think a runspace pool would produce results much faster than background jobs in this application.

AlexPawlak · Accepted Answer · 2014-06-17 09:37:24Z

3

I am not sure whether there exists a build in module to manipulate CSV files.

You can either manipulate CSV file to give you the output or add NoteProperty to the existing variable.

Example 1:

(cat "C:\Users\test\Documents\serveurs.csv") -replace "Servername;IP", "Servername;IP;Available" -replace "64","64;" -replace "67","67;" | out-file "C:\Users\test\Documents\serveurs.csv"

Example 2:

$CSV | Add-Member -MemberType NoteProperty "Available" -Value "Yes"

This should sort out your needs, I guess.

EDIT:

Since the question was clarified, here is the proposed answer.

I am pretty sure there exists a simpler method to do this, but it works anyway

$CSV | Add-Member -MemberType NoteProperty "Available" -Value "" 
for ($i=0; $i -le $CSV.Length-1; $i++) {$CSV[$i].Available = Test-Connection $CSV[$i].Servername -quiet}

edited Jun 17, 2014 at 9:37

answered Jun 17, 2014 at 8:45

AlexPawlak

8091 gold badge11 silver badges22 bronze badges

4 Comments

Adeel ASIF Over a year ago

Your 2nd example is working, but it's not really what i want. Because it's actually assigning the value "yes" to all of my ServerName

AlexPawlak Over a year ago

I'm sorry but you didn't specify what you exactly want. Do you want to have value assigned based on if the server is reachable over the network?

Adeel ASIF Over a year ago

Yes, i will use Test-connection cmdlet. I know how to use it, but i want to put the final result into my array (generated from my csv). My problem is "how to assign new column and new value to an array generated from a csv?" Thank you for your help.

AlexPawlak Over a year ago

Updated with an answer

kovkov · Accepted Answer · 2019-09-20 12:01:01Z

If you want to create your custom object and adding values this should be possible by doing something similar to this:

     [System.Collections.ArrayList]$myList = @()
     $mycsv = "C:\Users\test\Documents\serveurs.csv"
     $servers = Import-Csv $mycsv
     ForEach ( $server in $servers ) {
     $myobj = New-Object PSObject
     $test="No"
     if (Test-Connection $server.ip -Count 1 | Out-Null){
     $test="Yes"
     } 
     Add-Member -InputObject $myobj -NotePropertyName Name -NotePropertyValue $server.ServerName
     Add-Member -InputObject $myobj -NotePropertyName IP -NotePropertyValue $server.ip
     Add-Member -InputObject $myobj -NotePropertyName Available -NotePropertyValue $test
     $mylist.add($myobj)
     }$mylist

You will receive a list of your objects which you can then export into your csv or sort or anything

Collectives™ on Stack Overflow

Add column and assign value to an existing array

4 Answers 4

2 Comments

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related