Need help in splitting hash table into single system.object of multiple lines

Agent status
as online
bs online
bss bass online offline
I have an excel data like this above. While using import-excel or even if it is an csv import , the last row is considered as an array values. I get the output like below in powershell console
Agent status ----- ------ as online bs online bass... online...
How do I split it into seperate system.object into powershell console. so that I get the output as
Agent status as online bs online bss online bass offline

 

Please do not ask me what I did so far. I really do not know where\how to approach this. I googled, however, couldnt find a solution.

Sound’s strange/spooky. :wink: Could you provide some sample data in CSV format? (formatted as code please) Actually what you described shouldn’t be possible with CSV.

$data = @' Agent,status as,online bs ,online "bss bass","online offline" '@

$data | ConvertFrom-Csv


Try it. . May be I cofused too much on giving it a looong title with hashtable bla bla. it isnt an hashtable…
just multiple lines of index 2. System.object[]…

Hmmm … that’s not formatted as code but anyway :wink: … that’s actually not a valid CSV format. Every line has to have the same amount of “cells”. What means that every line has to have the same amount of delimiters - what’s not the case in your sample data.

The root cause might be in the source for this data. I assume this is just sample data and the real data is much more complex, isn’t it? I’d recommand to fix the source to output valid CSV data first before trying to fix the data later.

If that’s not possible your solution would have to be pretty complex. You would have to try to detect missformed CSV data and correct it first before importing it.

I didn’t understand what’s meant by formatted as code.

Yeah, I get this csv from udeploy rest api’s which I had to put in and split system.object[] by using - join `n…

But now I need to segregate results of only offline servers. So I m importing it back to memory to apply where $_. Status is offline.

However since it is inside one cell, it results in both values appearing… challenging.

Your CSV should look like bellow.

$data = @'
Agent,status
as,online
bs ,online
"bss bass","online offline"
'@

That doesnt help bro. then the output will be this…

Agent status


as online
bs online
bss bass online offline

I would be able to achieve $data | ConvertFrom-Csv | where {$_.Status -match “offline”}
Then the output is

Agent status


bss bass online offline

 

bss and bass are 2 different agents inside a cluster and thatsy the data is inside single cell.
While getting offline agent list, I shouldnt be seeing bss and online…

So it seems like either the data from your udeploy source is broken or you break it while initially importing it. Anyway, you have to find a way to create valid CSV data to reliably work with it with Powershell.
You mentioned that you already had to do something with it. You might show this code - there might be something inappropriate.

You can format code as code by using the code tag buttons “pre” right above the edit window when you write a post.

lol definitely its not broken… sometimes you will have PSCUSTOMOBJECT like the below

$var =@{
Cluster = $API.ClusterName
Agent =  @($API.AgentNames -join ' `n `r' )
Status = @($API.Statuses -join  ' `n `r' )
}

when you export-csv $var without -join it will create system.object[] in the csv output . Hence that is required to avoid it. I cannot avoid using array since some of the cluster contains 1 agent, some may contain more than 1. Thanks to the blog below.
https://learn-powershell.net/2014/01/24/avoiding-system-object-or-similar-output-when-using-export-csv/

So like you said, if I have to export it the way you want, then I need to modify if Count gt 1 , iterate the array and add member property etc etc…
But I am thinking if i am able to use -join to put it into csv, there should be a way to reverse it as well.
However, Yes, the data coming from API results, is itself mix of single values and arrays which cannot be similar to a table considering Database concepts.

Thanks for your responses. I will still keep exploring my options. :slight_smile: There shouldn’t be anything that PowerShell cannot do. haha… jk.

I cannot post the full code since I cannot access this blog from office network , neither I can email outside the network.

lol definitely its not broken… sometimes you will have PSCUSTOMOBJECT like the below

$var =@{
Cluster = $API.ClusterName
Agent = @($API.AgentNames -join ’ `n `r’ )
Status = @($API.Statuses -join ’ `n `r’ )
}

when you export-csv $var without -join it will create system.object[] in the csv output . Hence that is required to avoid it. I cannot avoid using array since some of the cluster contains 1 agent, some may contain more than 1. Thanks to the blog below.
https://learn-powershell.net/2014/01/24/avoiding-system-object-or-similar-output-when-using-export-csv/

So like you said, if I have to export it the way you want, then I need to modify if Count gt 1 , iterate the array and add member property etc etc…
But I am thinking if i am able to use -join to put it into csv, there should be a way to reverse it as well.
However, Yes, the data coming from API results, is itself mix of single values and arrays which cannot be similar to a table considering Database concepts.

Thanks for your responses. I will still keep exploring my options. :slight_smile: There shouldn’t be anything that PowerShell cannot do. haha… jk.

I cannot post the full code since I cannot access this blog from office network , neither I can email outside the network.

ok. No worries. Will see what I can do about it.