PowerShell ConvertTo-CSV end of line issue when sending to other items

user_6651115 0
Viewed 335

I make a pscustom object which looks like so

PS C:\...\acc> $csv

Count NonExistentUser
----- ---------------
1sht  1sht           
2sht  21sht

I convert it

$csv = $csv | ConvertTo-Csv -NoTypeInformation

I upload it

$upload= Invoke-RestMethod -Headers $SessionHeader -Method Post -Uri $attachUri  -Body $csv -ContentType 'multipart/form-data'

The upload works but the csv on the other end .... the whole csv is all on the same line when you download the csv from any other portal that stores attachments.

the output csv that is always uploaded is always on one line

"Count","NonExistentUser" "1sht","1sht" "2sht","21sht"

How do I add the link breaks to the end of each line before sending or converting?

powershell
2 Answers

tl;dr:

# $csv is an array of *custom objects*.
# If you send those to ConvertTo-Csv, you'll get an *array* of lines,
# which you must explicitly join to form a *single, multiline string*
# in order to pass it to -Body below.
$csvData = ($csv | ConvertTo-Csv -NoTypeInformation) -join "`n"

Invoke-RestMethod -Headers $SessionHeader -Method Post -Body $csvData -ContentType 'multipart/form-data' -Uri $attachUri

Note:

  • By default, Invoke-RestMethod uses character encoding ISO-8859-1 (almost identical to Windows-1252, with a few characters missing) to encode strings in requests.

  • To use UTF-8 instead, append ;charset=utf-8 to the -ContentType argument (e.g.,
    -ContentType 'application/json;charset=utf-8'.

It looks like your only problem was that your (final) $csv value, which is an array of lines - because that's what ConvertTo-Csv emits - was passed to -Body as-is, which caused Invoke-RestMethod to implicitly stringify the array.

When PowerShell automatically stringifies an array, its elements are space-concatenated, resulting in a single-line representation.

A simple example, using an expandable string ("..."), which applies the same stringification:

PS> $lines = 'one', 'two'; "$($lines)"
one two

Therefore, in order to pass your CSV data as a multi-line string, as intended, you must explicitly join the array elements with newlines first, as shown above, via -join, the string joining operator

user_453750

ok this applies to almost everything PowerShell in-memory ... you use the pnpsharepoint module ... you use invoke-restmethod ... your going to love this.

You have a csv and you dont want to write to disk .. because .. why would anyone ever want to write to disk.... and you tested but ...

Now I know you already tested writting it to disk and are really mad that MS didnt provide a -encoding utf8 to converto-csv

we never ever want to write to disk in code because who has a disk available
Export-Csv -Encoding utf8 -path <i in no way want to save to disk>

invoke-restmethod -infile $csv 
or
    $stream = [IO.MemoryStream]::new([Text.Encoding]::ASCII.GetBytes($CSV))
    $attachmentInfo = New-Object -TypeName Microsoft.SharePoint.Client.AttachmentCreationInformation 
    $attachmentInfo.FileName = $fileName
    $attachmentInfo.ContentStream = $stream
    $attchedFile = $listItem.AttachmentFiles.Add($attachmentInfo)

yeah it keeps removing your end of line character .. here you go

$csv = @()
            $csv += [pscustomobject]@{
                Count = '1sht'
                NonExistentUser = '1sht'
            }
            $csv += [pscustomobject]@{
                Count = '2sht'
                NonExistentUser = '21sht'
            }

now convert that before you use it

$csv = $csv | convertto-csv -NoTypeInformation | out-string
$csv = $csv -replace "`r`n","`n"

Now use that new $csv variable ... cheers

user_66511150
Related