Powershell Convert Array of PSCustomObjects to CSV

Question

I am working with a Blobtrigger Azure Function and I want to process an input.csv from a Blobstorage Container and then write an output.csv to another Blobstorage Container.

I am reading my input and filling a dummy array which I want to convert to a csv-file as follows:

# Input bindings are passed in via param block.
param([byte[]] $InputBlob, $TriggerMetadata)

# Write out the blob name and size to the information log.
Write-Host "PowerShell Blob trigger function Processed blob! Name: $($TriggerMetadata.Name) Size: $($InputBlob.Length) bytes"

$TempFile = New-TemporaryFile
[io.file]::WriteAllBytes($TempFile.FullName, $InputBlob)

$dataSet = Import-Csv $TempFile.FullName
$new_users = @()

foreach($data in $dataSet){
    $new_users += $data
}

Now $new_users.GetType() says it is a System.Array. $new_users[1].GetType() says it is a PSCustomObject (System.Object). The custom object looks like this when I output it:

2023-02-22T09:33:42Z   [Information]   OUTPUT: givenName      : Marly
2023-02-22T09:33:42Z   [Information]   OUTPUT: surname        : Giorgietto
2023-02-22T09:33:42Z   [Information]   OUTPUT: email          : tgiorgietto0@prweb.com.fake
2023-02-22T09:33:42Z   [Information]   OUTPUT: companyName    : 
2023-02-22T09:33:42Z   [Information]   OUTPUT: country        : 
2023-02-22T09:33:42Z   [Information]   OUTPUT: mobilePhone    : 
2023-02-22T09:33:42Z   [Information]   OUTPUT: department     : 
2023-02-22T09:33:42Z   [Information]   OUTPUT: officeLocation : 
2023-02-22T09:33:42Z   [Information]   OUTPUT: city           : 
2023-02-22T09:33:42Z   [Information]   OUTPUT: postalCode     : 
2023-02-22T09:33:42Z   [Information]   OUTPUT: streetAddress  :

When I try to create a csv from my $new_users array of custom objects by doing this:

$testCsv = ConvertTo-Csv -InputObject $new_users -NoTypeInformation

I only get meta-data gibberish when I view the file content:

["\"Length\",\"LongLength\",\"Rank\",\"SyncRoot\",\"IsReadOnly\",\"IsFixedSize\",\"IsSynchronized\",\"Count\"","\"4\",\"4\",\"1\",\"System.Object[]\",\"False\",\"True\",\"False\",\"4\""]

Now when I only export one of the elements of $new_users by indexing the array with $new_users[1], it actually contains data in the form: 1 string for all the keys of the PSCustomObject and 1 string for all the values of the PSCustomObject.

["\"givenName\",\"surname\",\"email\",\"companyName\",\"country\",\"mobilePhone\",\"department\",\"officeLocation\",\"city\",\"postalCode\",\"streetAddress\"","\"Marrrrly\",\"Giorgietto\",\"tgiorrrrgietto0@prweb.com.fake\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\""]

Obviously, I want my output CSV to look just like my input CSV:

givenName,surname,email,companyName,country,mobilePhone,department,officeLocation,city,postalCode,streetAddress
datapoint1.givenName,datapoint1.surname,...
datapoint2.givenName,...

Does anyone have an idea how to fix this? Thank you very much for your time in advance!

What is in `$InputBlob`? Please understand that you can't just write a blob (byte array) to a file and expect it to be a valid [`csv` (Comma-separated values)](https://en.wikipedia.org/wiki/Comma-separated_values) file. — iRon, Feb 22 '23 at 09:53
$InputBlob is a csv-file as well. Is there a better way to read data from the file and to write it back to a $OutputBlob (output csv-file)? — ABF, Feb 22 '23 at 10:01
```$csv = ConvertTo-Csv -InputObject $new_users``` treats the parameter as a single data row so it's serializing your **array** and putting **its** properties (Length, Type, SyncRoot, etc) in the csv. If you want to convert the **items** in the array into csv rows try this instead.... ```$csv = $new_users | ConvertTo-Csv``` — mclayton, Feb 22 '23 at 10:04
This actually filled the output $csv with all the data, however it is stored like this: ["row1.givenName, row1.surname,...","row2.givenName,...", "row3.givenName, ..."] which is not the format I expected: row1.givenName,row1.surname,... \n row2.givenName,row2.surname,... \n ... — ABF, Feb 22 '23 at 10:19
In the end, a combination of mclaytons info: ```$csv = $new_users | ConvertTo-Csv``` as well as the output binding statement ```Push-OutputBinding -name outputBlob -value ($csvstring -join "`n") ``` helped me to output in the right format. Before the csv was mingled in [ ] and all the content was written to a single field. thanks to: https://social.technet.microsoft.com/Forums/en-US/1041c527-8a45-4e63-8bc4-937bae40d852/azure-function-writing-to-blob-storage-using-output-binding-is-mangling-the-output?forum=windowsazuredata — ABF, Feb 22 '23 at 15:55

iRon · Answer 1 · 2023-05-31T06:05:42.290

"`$InputBlob` is a csv-file as well"

Apparently it is not (as it works fine with one that is a csv)...
Please add a simply example of you blob file (e.g. using $Blob |ConvertTo-Hex) to your question or create a Minimal, Reproducible Example

function test {
    # Input bindings are passed in via param block.
    param([byte[]] $InputBlob, $TriggerMetadata)
    
    # Write out the blob name and size to the information log.
    Write-Host "PowerShell Blob trigger function Processed blob! Name: $($TriggerMetadata.Name) Size: $($InputBlob.Length) bytes"
    
    $TempFile = New-TemporaryFile
    [io.file]::WriteAllBytes($TempFile.FullName, $InputBlob)
    
    $dataSet = Import-Csv $TempFile.FullName
    $new_users = @()
    
    foreach($data in $dataSet){
        $new_users += $data
    }
    $new_users
}

$Csv = @'
a,b
1,2
3,4
'@
$Blob = [system.Text.Encoding]::UTF8.GetBytes($Csv)
Write-Host "`$Blob = [byte[]]($($Blob -Join ', ')) # use for MCVE"
Test $Blob

yields:

$Blob = [byte[]](97, 44, 98, 10, 49, 44, 50, 10, 51, 44, 52) # use for MCVE

PowerShell Blob trigger function Processed blob! Name:  Size: 11 bytes

a b
- -
1 2
3 4

As an aside: try to avoid using the increase assignment operator (+=) to create a collection

score 1 · Answer 2 · answered Feb 22 '23 at 14:12

It appears as though your input blob is the Contents of a csv file, expresed as a single string, or an array of strings. If that's true, all you need to do to create a copy in a file is:

$InputBlob | Out-File myfile.csv

No conversion necessary. If you want to convert it to an array of PSCustomObject, then you can do this:

$myarray = $InputBlob | ConvertFrom-Csv

If your input blob is something else, then please revise your question.

ABF · Answer 3 · 2023-02-22T17:20:38.740

In the end, a combination of mclaytons info: $csv = $new_users | ConvertTo-Csv as well as the output binding statement Push-OutputBinding -name outputBlob -value ($csv -join "`n") helped me to output in the right format. Before the csv was mingled in [ ] and all the content was written to a single field. thanks to: https://social.technet.microsoft.com/Forums/en-US/1041c527-8a45-4e63-8bc4-937bae40d852/azure-function-writing-to-blob-storage-using-output-binding-is-mangling-the-output?forum=windowsazuredata

Powershell Convert Array of PSCustomObjects to CSV

3 Answers3

"$InputBlob is a csv-file as well"

"`$InputBlob` is a csv-file as well"