Powershell export-csv shows System.Object[],"System.Object[]"

Question

I want to export my powershell script to csv, only when the file is exported, I get the headers in my csv file, but the data contains systemobject.

#------- Opvragen token tiptrack -------
#Dit is de URL waar de token voor tiptrack wordt opgevraagd.
$Url_token="https://tiptracknext-staging-login.indicia.nl/oauth2/aus342go9hNphcHXM0i7/v1/token"

#Dit is de body die mee wordt gestuurd in de request, deze informatie staat gelijk aan de data in de post request vanuit de handleiding.
$Data_token = @{
grant_type="client_credentials"
client_id="123457"
client_secret="123456"
scope="eapi"
}

$token_tiptrack=Invoke-RestMethod -Method Post -Uri $Url_token -ContentType "application/x-www-form-urlencoded" -Body $Data_token

#------- Opvragen Employerbudgetsid -------
#Dti is de URL waarna de GET request wordt gestuurd om het employerid te kunnen.
$Url_budgetid='https://staging.tiptrack.nl/Tiptrack.Employer.Api/odata/EmployeeBudgets?$expand=Employee($expand=SecureEmployee)&$top=5'

#Dit is header die mee wordt gestuurd in de request. Deze data in deze header staat gelijk aan de data in de API handleiding.
$header_process = @{
Authorization='Bearer '+$token_tiptrack.access_token
"accept"="application/json"
}

#Vanuit het uploaden van het bestand krijgen we een reactie van de server, in deze reactie staat het upload id, deze id hebben we nodig om het bestand te kunnen verwerken. 
$data = Invoke-RestMethod -Uri $Url_budgetid -Method Get -Headers $header_process 


$exportdata = $data | 
    ForEach-Object { return [PSCustomObject]@{ 
        EmployeeNumber = $_.Value.Employee.SecureEmployee.EmployeeNumber;
        ComputedCurrentBalanceAmount =($_.Value.ComputedCurrentBalanceAmount) ;
        }  } 

$exportdata  | Select EmployeeNumber,ComputedCurrentBalanceAmount | Export-Csv C:\afas\test3.csv -NoTypeInformation

This is the ouput that i want:

# EmployeeNumber,ComputedCurrentBalanceAmount
# EMP1,100,00
# EMP2,250,49
# EMP3,450,00

Try to omitt the return in the ForEach-Object or return the whole exportdata. Next try this and inspect the $ExportCsv-Object: $ExportCsv = $exportdata | Select EmployeeNumber,ComputedCurrentBalanceAmount What does $ExportCsv.getType() say? — T-Me
– T-Me, Commented Aug 13, 2020 at 12:26
This is the output: EmployeeNumber ComputedCurrentBalanceAmount -------------- ---------------------------- {A20200626-00, A20200626-01, 100003, 100015...} {$null, $null, 1695,4000, 323,0400...} — Martijnsp1991
– Martijnsp1991, Commented Aug 13, 2020 at 12:35
@T-Me, this is the reaction when i do $ExportCsv.getType() IsPublic IsSerial Name BaseType -------- -------- ---- -------- True False PSCustomObject System.Object — Martijnsp1991
– Martijnsp1991, Commented Aug 13, 2020 at 12:38
You have multiple arrays at different depths on your data. Is there another deeper level where data sets share the same array? Maybe you need $data | foreach-object { $_.Value | Foreach-Object { $_.Employee.SecureEmployee.EmployeeNumber } }. Without seeing the returned data from the GET, it'll be difficult to guess. — AdminOfThings
– AdminOfThings, Commented Aug 13, 2020 at 12:54

AdminOfThings · Accepted Answer · 2020-08-13 14:34:21Z

1

The provided output data does not list any data for the SecureEmployee property. So I'll assume your property hierarchy in your code is correct. The Value property contains an array of Employee objects that you will need to iterate.

$exportdata = $data.Value | ForEach-Object { 
    [PSCustomObject]@{ 
        EmployeeNumber = $_.Employee.SecureEmployee.EmployeeNumber
        ComputedCurrentBalanceAmount = $_.ComputedCurrentBalanceAmount
    }
} 

$exportdata | Export-Csv C:\afas\test3.csv -NoTypeInformation

answered Aug 13, 2020 at 14:34

AdminOfThings

25.2k4 gold badges22 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Powershell export-csv shows System.Object[],"System.Object[]"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related