Powershell: formatted String(2Dim Array) to CSV

Question

I have a function to return a json string in this format:

{"TownA":{"female":512,"male":468},"TownB":{"female":748,"male":845}}

I'd like to append the data to a CSV:

town, value, gender   
TownA,  512,   female 
TownA,  468,   male  
TownB,  748,   female  
TownB,  845,   male

I think I need to first convert it to a custom object, like this:

$Line= [pscustomobject] @{
        'Town' = [STRING]""
        'Value' = [FLOAT]""
        'sex' = [STRING]""
        }

But I'm not sure. How can append this data to my csv?

Santiago Squarzon · Accepted Answer · 2025-02-07 13:56:05Z

2

What you got from your function is a Json string, you can use ConvertFrom-Json to parse it into object, from there you will have to enumerate the properties and nested properties to get the desired output:

$json = ConvertFrom-Json '{"TownA":{"female":512,"male":468},"TownB":{"female":748,"male":845}}'
$json.PSObject.Properties | ForEach-Object {
    foreach ($property in $_.Value.PSObject.Properties) {
        [pscustomobject]@{
            Town   = $_.Name
            Value  = $property.Value
            Gender = $property.Name
        }
    }
} # Can pipe to Export-Csv from here

answered Feb 7 at 13:56

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

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

Comments

sirtao · Accepted Answer · 2025-02-07 14:40:24Z

1

Your string is a JSON.
There are multiple way to manipulate it.

If you are using Powershell Core/7.x you can use this

# Replace with however you are getting the JSON.   
$JsonString = '{"TownA":{"female":512,"male":468},"TownB":{"female":748,"male":845}}'

$JsonHashtable = $JsonString | ConvertFrom-Json -AsHashtable

$ConvertedArray = foreach ($Key in $JsonHashtable.Keys) {
    foreach ($Gender in $JsonHashtable.$Key.Keys) {
        # casting to [Ordered] is actually superfluous unless you REALLY need
        # the columns being in a specific order.  
        [ordered]@{
            'town'   = $Key
            'value'  = $JsonHashtable.$key.$Gender
            'gender' = $Gender
        }
    }
}

$ConvertedArray | Export-Csv -Path $CsvPath

answered Feb 7 at 14:40

sirtao

3,0311 gold badge4 silver badges15 bronze badges

1 Comment

Joel Coehoorn Feb 7 at 14:45

If he's appending to an existing csv, the order is definitely gonna matter ;)

Collectives™ on Stack Overflow

Powershell: formatted String(2Dim Array) to CSV

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related