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
– mklement0, Commented Mar 16, 2023 at 22:32

mklement0 · Accepted Answer · 2023-03-17 13:42:15Z

1

$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.

edited Mar 17, 2023 at 13:42

answered Mar 16, 2023 at 22:30

mklement0

453k68 gold badges729 silver badges990 bronze badges

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

2 Comments

Oleg Kazanskyi Over a year ago

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!

mklement0 Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related