Export nested hash table to csv in powershell

Question

I have an array, with a nested hashtable containing data:

tid              : 1
token            : 12345
participant_info : @{firstname=bob; lastname=smith; [email protected]}
completed        : y

tid              : 2
token            : 67890
participant_info : @{firstname=Alice; lastname=Jones; [email protected]}
completed        : n

I would like to output this as a CSV with the nested items of firstname, surname and email pulled out of the inner hashtable - eg

tid,token,firstname,surname,email,completed
1,12345,bob,smith,[email protected],y
2,67890,alice,jones,[email protected],n

I'm going to guess that the answer is looping through with foreach and creating a custom ps object, but because my nested items aren't named, I can't work out how to do this using the other examples on here.

Any assistance is appreciated! Thanks!

Show us what you have tried so far and explain how it has not worked for you. — EBGreen
– EBGreen, Commented Apr 6, 2018 at 14:22
That isn't a valid hashtable.. You don't have any keys, just values. — Maximilian Burszley
– Maximilian Burszley, Commented Apr 6, 2018 at 14:26
Your outer hash does not appear to have key-value pairs? Also, how is this data generated? If you control that I would say your best bet is to put it into a better data structure to begin with. — EBGreen
– EBGreen, Commented Apr 6, 2018 at 14:26
Your hashtable also has an opening @( but no corresponding closing ). — Mark Wragg
– Mark Wragg, Commented Apr 6, 2018 at 14:27
The data is coming from Limesurvey's remote control API, using the list_participants method. I convert the received JSON using ConvertFrom-Json. I have just noticed that the returned type is actually an array, which does include the inner hash table - I'm thinking I need to delete this one and ask again! :-) — David Phillips
– David Phillips, Commented Apr 6, 2018 at 14:35

Maximilian Burszley · Accepted Answer · 2018-04-06 16:34:29Z

5

Given your sample:

@(
    [pscustomobject]@{
        tid = 1
        token = 12345
        participant_info = @{
            firstname = 'bob'
            lastname = 'smith'
            email = '[email protected]'
        }
        completed = 'N'
    }

    ...
)

And desired output:

id,token,firstname,surname,email,completed
1,12345,bob,smith,[email protected],y
2,67890,alice,jones,[email protected],n

You can do something like this:

$JsonList = @( ... )
$Path = "$Env:UserProfile\file.csv"

ForEach ($Sample in $JsonList)
{
    $Sample | Select-Object -Property @(
        @{N = 'id';        E = { $Sample.tid }}
        @{N = 'token';     E = { $Sample.token }}
        @{N = 'firstname'; E = { $Sample.participant_info.firstname }}
        @{N = 'surname';   E = { $Sample.participant_info.lastname }}
        @{N = 'email';     E = { $Sample.participant_info.email }}
        @{N = 'completed'; E = { $Sample.completed }}
    ) | Export-Csv -Path $Path -Append
}

Edit: you're dealing with PSCustomObject, not Hashtable, so my previous syntax won't work for that. Here's what I'm presuming your code to look like now (I've updated my above example):

@"
[
    {
        "tid": 1,
        "token": 12345,
        "participant_info": {
            "firstname": "bob",
            "lastname": "smith",
            "email": "[email protected]"
        },
        "completed": "N"
    }

    ...
]
"@ | ConvertFrom-Json

edited Apr 6, 2018 at 16:34

answered Apr 6, 2018 at 14:56

Maximilian Burszley

20k7 gold badges39 silver badges68 bronze badges

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

7 Comments

Maximilian Burszley Over a year ago

@DavidPhillips Update your question with $Json | Format-List -Property * output

David Phillips Over a year ago

Thanks for the time you've spent on this. It does appear that something is a bit odd as the code you've given didn't output the answer I was expecting. If I just type the variable into the shell, I get a table of 1 123456 @{firstname=bob;surname=smith;[email protected]} Y If I do a foreach $item in $sample, then write-host ($item[0]), I get @{id=1;token=12345;participant_info=;completed=N}. No matter what I try, I can't return the inner values from here - eg $sample[0]['id']

David Phillips Over a year ago

Thanks! But this returns no data for each attribute - the CSV shows multiple rows, but with no data. $jsonlist.tid lists all of the tid's, and $jsonlist.tid.getvalue(0) returns the correct value, but I've not been able to use the square bracket notation to return anything.

Maximilian Burszley Over a year ago

@DavidPhillips Check my edit. I've updated to your use-case.

David Phillips Over a year ago

Boom! Perfect - thank you so much for helping and staying with my badly worded question. I've kept the other version for hashtables incase I should encounter those in the future. I thought anything with {} brackets rather than () was a hashtable.

|

Collectives™ on Stack Overflow

Export nested hash table to csv in powershell

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related