3

I have a very large JSON response for employees that I am trying to get into table format, export to CSV and eventually insert into SQL Server. I was able to determine how to get all of my variables from the json file, however now I am getting all of my values inserted on one row for each column instead of a new row for each employee. Also, when I export to CSV the value turns into System.Object[].

$json1 = Invoke-webRequest -Uri $Workeruri -Certificate $cert -Headers $WorkerHeader | convertfrom-json

$table = [PSCustomObject] @{
 associateOID = $json1.workers.associateOID
 workerID = $json1.workers.workerID.idValue 
 GivenName = $json1.workers.person.legalName.givenName
 MiddleName = $json1.workers.person.legalName.middleName
 FamilyName1 = $json.workers.person.legalName.familyName1 

 } |format-table -autosize
 $table | export-csv $filepath -NoTypeInformation

The columns are a small sample, there are actually probably 100 columns. However, my response returns like this:

associateOID     workerID        givenName                                          
------------     --------        ---------                                                                                                                                                                                                                                           
{1,2,3,4,5...}  {a,b,c,d,e...}   {Lebron James, Micheal Jordan, Steph Curry...}

I would like it to return:

associateOID     workerID        givenName                                          
------------     --------        --------- 
1                 A              Lebron James
2                 B              Micheal Jordan
3                 C              Steph Curry

Also, when exporting to CSV the response has the correct columns, but all columns return with: System.Object[]. Also, my fields that have ints and dates are not returning data. How can I fix that as well?

I have tried using sort-object, group-object, for-each loops. Nothing has worked.

Jumpman
  • 67
  • 1
  • 4

2 Answers2

0

you can try like this:

$json1 = Invoke-webRequest -Uri $Workeruri -Certificate $cert -Headers $WorkerHeader | ConvertFrom-Json

$table = $json1 | ForEach-Object {
    [PSCustomObject] @{
        associateOID = $_.workers.associateOID
        workerID = $_.workers.workerID.idValue 
        GivenName = $_.workers.person.legalName.givenName
        MiddleName = $_.workers.person.legalName.middleName
        FamilyName1 = $_.workers.person.legalName.familyName1
    }
}

$table | Export-Csv $filepath -NoTypeInformation

$table | Format-Table -AutoSize

Your snippet takes all the values for each column and stores them in a single object instead of iterating on the object collection converted from JSON.

Also, once you use Format-Table, data is formatted for display but not usable in the pipeline anymore. That's why I've separated display on screen and CSV export.

sodawillow
  • 12,497
  • 4
  • 34
  • 44
  • I tried your exact code and still have each employee in the same cell for the CSV. From another thread I read that I could do a for loop, and use [i] at the end of each column name. That worked for columns which were always filled out, but columns that weren't such as MiddleName, ended up in the wrong order with that method. – Jumpman Jul 07 '16 at 21:55
0

@sodawillow almost had it, assuming that json1.workers is the list of objects that contains your workers.

$json1 = Invoke-webRequest -Uri $Workeruri -Certificate $cert -Headers $WorkerHeader | ConvertFrom-Json

$table = $json1.workers | ForEach-Object {
    [PSCustomObject] @{
        associateOID = $_.associateOID
        workerID = $_.workerID.idValue 
        GivenName = $_.person.legalName.givenName
        MiddleName = $_.person.legalName.middleName
        FamilyName1 = $_.person.legalName.familyName1
    }
}

$table | Export-Csv $filepath -NoTypeInformation

$table | Format-Table -AutoSize
jadenguy
  • 21
  • 4