Recorded PowerShell Object data to Azure blob as CSV is missing line breaks

Question

I am trying to record my data to the Azure blob storage using the next command:

    #Get PowerBI metrics
        $Result = Get-PowerBIActivityEvent -StartDateTime 2023-03-13T23:58:59 -EndDateTime 2023-03-13T23:59:59
        $Result = ($Result | ConvertFrom-Json) | Select-Object * 
        $Result = $Result |  ConvertTo-Csv -NoTypeInformation
    
        Connect-AzAccount -ServicePrincipal -TenantId $TenantId -Credential $Credential
        
# Create a context object using Azure AD credentials
        $ctx = New-AzStorageContext -StorageAccountName $accountName -UseConnectedAccount
        $container = Get-AzStorageContainer -Name $containerName -Context $ctx
        $date_loaded = (Get-Date -Format "MMddyyyy").ToString()
        $content = [system.Text.Encoding]::UTF8.GetBytes($Result) 
        $container.CloudBlobContainer.GetBlockBlobReference($date_loaded+".csv").UploadFromByteArray($content,0,$content.Length)

The file is successfully created but all the records are in one line. I believe it's something with Encoding but I can't figure out how to fix it.

I know there is an option to record existing file to blob, but it doesn't work for me as I want to run it on cloud and I need direct conversion of the variable to the blob.

As an aside: `| Select-Object *` is an expensive no-op in your case. — mklement0, Mar 16 '23 at 22:32

mklement0 · Accepted Answer · 2023-03-17T13:42:15.290

$Result contains an array of strings, because ConvertTo-Csv emits the CSV rows it creates one by one (which, when captured in a variable, results in a regular PowerShell array, which is of type [object[]]).
This array is implicitly stringified (converted to a single string) when you call $content = [System.Text.Encoding]::UTF8.GetBytes($Result), which by default concatenates the (stringified) elements with spaces (see this answer for details).
- That is, there will be no newlines in the result (except if the array elements themselves happen to contain any).

Instead, you must pass a single, multiline string, which you must create explicitly:

# To use LF-only newlines, use "`n"
# To use the platform-native newlines, use [Environment]::NewLine 
# Alternatively (simple, but slower, with platform-native newlines): 
#   ($Result | Out-String)
$content = [System.Text.Encoding]::UTF8.GetBytes($Result -join "`r`n")

Note:

PowerShell is unusual in that it performs many automatic type conversions, unlike other languages.
While these implicit conversions are usually helpful, when it comes to calling .NET APIs with [string] parameters they are not, as your question demonstrates.
- Unfortunately, given PowerShell's commitment to backward compatibility, this behavior is unlikely to change.

I am super grateful for your help! I spent so much time looking for a solution that I'm embarrassed to mention it Thank you! — Oleg Kazanskyi, Mar 17 '23 at 13:44
My pleasure, @OlegKazanskyi; glad to hear that it helped. It's a subtle problem, for sure, and I wish PowerShell worked differently in this case, but it's too late to change that. — mklement0, Mar 17 '23 at 14:17

Recorded PowerShell Object data to Azure blob as CSV is missing line breaks

1 Answers1