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          : [email protected]
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\",\"[email protected]\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\""]

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) file. — iRon
– iRon, Commented Feb 22, 2023 at 9: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
– ABF, Commented Feb 22, 2023 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
– mclayton, Commented Feb 22, 2023 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
– ABF, Commented Feb 22, 2023 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: social.technet.microsoft.com/Forums/en-US/… — ABF
– ABF, Commented Feb 22, 2023 at 15:55

Walter Mitty · Accepted Answer · 2023-02-22 14:12:32Z

1

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.

answered Feb 22, 2023 at 14:12

Walter Mitty

19k2 gold badges32 silver badges60 bronze badges

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

Comments

iRon · Accepted Answer · 2023-05-31 06:05:42Z

"`$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

ABF · Accepted Answer · 2023-02-22 17:20:38Z

0

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

edited Feb 22, 2023 at 17:20

answered Feb 22, 2023 at 15:59

ABF

1191 gold badge1 silver badge15 bronze badges

Collectives™ on Stack Overflow

Powershell Convert Array of PSCustomObjects to CSV

3 Answers 3

Comments

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

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

"$InputBlob is a csv-file as well"

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

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