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

Question

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?

mklement0 · Accepted Answer · 2020-12-18T20:45:59.510

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

William Lee · Answer 2 · 2020-12-18T18:06:07.030

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

If you use PowerShell Core, almost everything defaults to UTF8 no BOM — AdminOfThings, Dec 18 '20 at 18:58
`ConvertTo-Csv` outputs _strings_, and PowerShells strings are always .NET strings (internally composed of UTF-16 Unicode code units). Using a different character encoding only applies when communicating with the outside world. Therefore an `-Encoding` parameter isn't appropriate for `ConvertTo-Csv`, but it is for cmdlets that write to a _file_, such as `Export-Csv` for instance. — mklement0, Dec 18 '20 at 19:41
To control the encoding of strings for inclusion in requests sent by `Invoke-RestMethod`, append something like `;charset=utf-8` to the `-ContentType` parameter; e.g. `-ContentType 'application/json;charset=utf-8'` — mklement0, Dec 18 '20 at 20:26

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

2 Answers2