To change Column value of an array to Column header

Question

I am extracting some data from an API which I have finally stored in the form of two strings which looks something like this:

$String1:

Client  1
Client  2
Client  3

$String2:

Product  a
Product  b
Product  c

The above is a long list in reality.

I also have fetched a different set of data from a different API which is stored in an array.

$Array:

Server     State
abcd.com   Normal
defg.com   Dead
fguy.com   Normal

Note, the way I have fetched $String1 and $String2 are values of foreach of Server from a different API output extracted as findstr Client and findstr Product.

Now, what I want to achieve from these 3 $s is a final table which will look something like this:

$Final:

Server     State   Client   Product
abcd.com   Normal    1         a
defg.com   Dead      2         b
fguy.com   Normal    3         c

So what I first tried to do is create and intermediate table which might look as

Client   Product
1          a
2          b
3          c

and then merge with the $Array into the Final table.

And currently I am going nowhere with this, I have tried a lot of different ways which look stupid and getting me nowhere.

[1] exactly what is at `$array1[0]`? [2] what do you get with `$Array1[0].GetType()`? — Lee_Dailey, Aug 23 '19 at 14:01
@Lee_Dailey Sure [1] $array1[0] gives me the first letter of Client i.e C and, [2] $Array1[0].GetType() prints me IsPublic IsSerial Name BaseType True True Char System.ValueType — darc, Aug 23 '19 at 14:09
Very interested in what the `Servers` array is, since you are building array 1 and 2 from that. Is that a CSV file you read with Import-Csv? It looks like there could be a simple solution by just iterating that Servers array with a foreach() and output all properties belonging together from that into a PsCustomObject. — Theo, Aug 23 '19 at 14:11
@Theo No i am not building $array1 and $array2 from Servers but from a different api output. Actually $array1 and $array2 are findstr Client and findstr Product outputs from the API output. — darc, Aug 23 '19 at 14:19
Ah, your description _"Note, the way i have fetched $Array1 and $Array2 are values of foreach of Server"_ led me to believe otherwise. Another question then. How can you be sure the 3 arrays are items of the server in the correct order? I.e. do $array1[0], $array2[0] and $array3[0] **really** belong to the same server? — Theo, Aug 23 '19 at 14:22
@Theo I have edited the description, my bad for the confusion. Ok, so ````$array1[0] = C ```` ````$array2[0] = P ```` ````$array3[0] = Server State abcd.com Normal```` And i am confirmed of the order because thats the way i have extracted the data from the api to be in order, thats how both the apis are sorted as per server names — darc, Aug 23 '19 at 14:32
@darc - that means that `$Array1` IS NOT an array. [*grin*] it's a string - probably a multline string, but a string and NOT an array. — Lee_Dailey, Aug 23 '19 at 15:44
@darc - that means your description of hte problem is WRONG and no solution based on your incorrect description will work on the actual _strings_ since those solutions will be written for _arrays of strings_ or for `arrays of objects with string properties_. ///// you likely otta fix your description of the problem and rename the non-arrays to something accurate instead of totally misleading. [*grin*] — Lee_Dailey, Aug 23 '19 at 15:59
@Lee_Dailey : Thanks a bunch for pointing it out, i have edited the desc properly. Hope it is clear now. — darc, Aug 23 '19 at 16:28
hey @Theo Sorry, i didnt mark it as answer cuz i didnt get the answer relevant to my problem. But it was almost close to it. — darc, Sep 03 '19 at 16:16

f6a4 · Answer 1 · 2019-08-23T15:26:36.983

0

This should work:

# Example-Arrays
$Array1 = @( 'Client  1', 'Client  2', 'Client  3' )
$Array2 = @( 'Product  a', 'Product  b', 'Product  c' )
$Array3 = @( [PsCustomObject]@{'Server' = 'abcd.com'; 'State' = 'Normal'},
             [PsCustomObject]@{'Server' = 'defg.com'; 'State' = 'Dead'},
             [PsCustomObject]@{'Server' = 'fguy.com'; 'State' = 'Normal'} )

# Create datatable
$dt = New-Object system.Data.DataTable
[void]$dt.Columns.Add('Server',[string]::empty.GetType() )
[void]$dt.Columns.Add('State',[string]::empty.GetType() )
[void]$dt.Columns.Add('Client',[string]::empty.GetType() )
[void]$dt.Columns.Add('Product',[string]::empty.GetType() )

for( $counter = 0; $counter -lt $Array1.Count; $counter++ ) {

    # Add new rows:
    $newRow = $dt.NewRow()
    $newRow.Server  = $Array3.Server[$counter]
    $newRow.State   = $Array3.State[$counter]
    $newRow.Client  = $Array1[$counter] -replace '^.+(\d+)$', '$1'
    $newRow.Product = $Array2[$counter] -replace '^.+[ \n\t\r]+(.*)$', '$1'
    [void]$dt.Rows.Add( $newRow )
}

# Output-Datatable
$dt

# To File
$dt | Out-File 'test.txt'

edited Aug 23 '19 at 15:26

answered Aug 23 '19 at 14:33

f6a4

1,684
1
10
13

I will try this script out, but the problem is here in this script i need to manually put the values here inside the array description which for example purpose i have given 3 values, but in reality it has around 50 entries and since the final script will run on a schedule, the inventory list will change from time to time, so cannot hard code any values in here – darc Aug 23 '19 at 14:54
The Arrays are for example only, you can take your own Arrays. Just to be sure: Could you check your $array3 with "$Array3[0].GetType()" and write here what is the Output of this command – f6a4 Aug 23 '19 at 15:02
Got it, here is the gettype you asked for IsPublic IsSerial Name BaseType True True Object[] System.Array – darc Aug 23 '19 at 15:16
Thanks. Theo is right, $Array3 has objects so I updated my script. With a datatable you are able to make queries and different sorts. e.g. $dt.Select("State = 'Dead') – f6a4 Aug 23 '19 at 15:28

Theo · Accepted Answer · 2019-08-24T09:57:07.863

0

Since it looks like $Array3 is an array of objects with two properties: Server and State, I think this could help you out:

$Array1 = 'Client  1','Client  2','Client  3'
$Array2 = 'Product a','Product b','Product c'
$Array3 = @(
    [PsCustomObject]@{'Server' = 'abcd.com'; 'State' = 'Normal'},
    [PsCustomObject]@{'Server' = 'defg.com'; 'State' = 'Dead'},
    [PsCustomObject]@{'Server' = 'fguy.com'; 'State' = 'Normal'}
)

for ($i = 0; $i -lt $Array3.Count; $i++) {
    $Array3[$i] | Select-Object *, 
                                @{Name = 'Client'; Expression = { $Array1[$i] -replace '^Client\s*'}},
                                @{Name = 'Product'; Expression = { $Array2[$i] -replace '^Product\s*'}}
}

Output:

Server   State  Client Product
------   -----  ------ -------
abcd.com Normal 1      a      
defg.com Dead   2      b      
fguy.com Normal 3      c

If you want to, you can capture the result of the for(..) loop and save that as CSV file somewhere. In that case just do

$result = for ($i = 0; $i -lt $Array3.Count; $i++) {
    $Array3[$i] | Select-Object *, 
                                @{Name = 'Client'; Expression = { $Array1[$i] -replace '^Client\s*'}},
                                @{Name = 'Product'; Expression = { $Array2[$i] -replace '^Product\s*'}}
}
$result | Export-Csv -Path 'D:\serverresult.csv' -NoTypeInformation

Update

Apparently the arrays 1 and 2 you mention are not arrays at all, but (multiline) strings.

In that case, split the lines inside these strings so you will end up with true arrays:

$string1 = @"
Client  1
Client  2
Client  3
"@

$string2 = @"
Product  a
Product  b
Product  c
"@

# split the multiline strings (examples) on the Newline character into arrays
$Array1 = $string1 -split '\r?\n'
$Array2 = $string2 -split '\r?\n'

# now, both arrays will have 3 elements:
# $Array1 = 'Client  1','Client  2','Client  3'
# $Array2 = 'Product a','Product b','Product c'

# array no. 3 is an array of objects as we saw earlier
$Array3 = @(
    [PsCustomObject]@{'Server' = 'abcd.com'; 'State' = 'Normal'},
    [PsCustomObject]@{'Server' = 'defg.com'; 'State' = 'Dead'},
    [PsCustomObject]@{'Server' = 'fguy.com'; 'State' = 'Normal'}
)


# Finally, you can use the `for(..)` loop unaltered

$result = for ($i = 0; $i -lt $Array3.Count; $i++) {
    $Array3[$i] | 
        Select-Object *, 
                      @{Name = 'Client'; Expression = { $Array1[$i] -replace '^Client\s*'}},
                      @{Name = 'Product'; Expression = { $Array2[$i] -replace '^Product\s*'}}
}

# output on console
$result

# output to CSV file
$result | Export-Csv -Path 'D:\serverresult.csv' -NoTypeInformation

edited Aug 24 '19 at 09:57

answered Aug 23 '19 at 14:42

Theo

57,719
8
24
41

I will try this script out, but the problem is here in this script i need to manually put the values here inside the array description which for example purpose i have given 3 values, but in reality it has around 50 entries and since the final script will run on a schedule, the inventory list will change from time to time, so cannot hard code any values in here – darc Aug 23 '19 at 14:54
@darc The hardcoded arrays in the script are simply examples taken from your question to work with. They are dummies. You should try with the _Real_ arrays you have obtained by using the API calls of course. – Theo Aug 23 '19 at 15:00
Got it. Thanks, let me try it out – darc Aug 23 '19 at 15:16
i tried the below part `$result = for ($i = 0; $i -lt $Array3.Count; $i++) { $Array3[$i] | Select-Object *, @{Name = 'Client'; Expression = { $Array1[$i] -replace '^Client\s*'}}, @{Name = 'Product'; Expression = { $Array2[$i] -replace '^Product\s*'}} } $result | Export-Csv -Path 'D:\serverresult.csv' -NoTypeInformation` But i am getting this as output, the values of Client and product are not getting printed `Server : abcd.com State : normal Client : Product : ` – darc Aug 23 '19 at 15:22
@darc Then arrays 1 and 2 are not what they appeared. Check their types as in `Array1[0].GetType()`. Same for `$Array2` – Theo Aug 23 '19 at 15:43
This is what i got for both of them `IsPublic IsSerial Name BaseType True True Char System.ValueType` – darc Aug 23 '19 at 15:49
So, it looks like it is a string – darc Aug 23 '19 at 15:55
Thank you so much for looking into my question diligently, i have edited the description to be correct and precise. Hope you are able to point me to the correct solution. – darc Aug 23 '19 at 16:32
@darc if they are strings, try and make them arrays by splitting at the newlines. `$Array1 = $Array1 -split '\r?\n'`. Same for the Products array. – Theo Aug 23 '19 at 16:49
Ok, i did that and the output of the strings looks the same on console, but its gettype has changed to : IsPublic IsSerial Name BaseType True True String System.Object – darc Aug 23 '19 at 17:11
@darc That is the whole idea. You said they were arrays, but in fact they were strings. As the code is meant to iterate arrays, you split them into string arrays and use that in the for() loop – Theo Aug 23 '19 at 19:15
@darc I have updated my answer to show you how to split the multiline strings into arrays first. – Theo Aug 24 '19 at 09:59

iRon · Answer 3 · 2019-08-26T07:27:27.450

Two cmdlets I wrote might be help for this:

`ConvertFrom-SourceTable`

This cmdlet is able to restore objects from a fixed width table.
For tables that do not contain a header line (as in your case for $String1 and $String2), you can separately define it with the -Header parameter where the header has basically to functions: it defines the property names and the column alignment (along with a possible ruler and the data contained by the table).

$Client = ConvertFrom-SourceTable $String1 -Header 'Name    Client'

$Product = ConvertFrom-SourceTable $String2 -Header 'Name     Product'

It is not clear whether the $Array is also a string or an object list. Presuming it is a string, you simply might restore the object list as follows:

$Server = ConvertFrom-SourceTable $Array

`Join-Object`

The other cmdlet is initially written to join objects on an common property relation. Nevertheless, if you omit the -On parameter (which defines the relation), it will simply join the objects based on the line index. The -Property parameter will just select the properties you need (and skip the Name property, defined in the header of the $String1 and $String2):

$Server | Join $Client | Join $Product -Property Server, State, Client, Product

_{(See also: In Powershell, what's the best way to join two tables into one?)}

To change Column value of an array to Column header

3 Answers3

ConvertFrom-SourceTable

Join-Object

`ConvertFrom-SourceTable`

`Join-Object`