Sitecore Forms CSV export not preserving new line characters from textarea input field

Question

I've got a <textarea> set up in a Sitecore Form in which users can enter multi line inputs:

When the value is saved to the database I notice that the breaks ARE preserved:

If I copy the value from the database and paste it in Notepad++, it appears as follows:

Line 1

Line 2


Line 3

So far so good. However, when the form submissions are exported to CSV, the data appears to lose any new line information.

When pulling this data I need to be able to find and replace instances of new lines because I want to display the CSV data in a table using the following borrowed PowerShell code:

function Get-FormData {
    param (
        [parameter(mandatory=$true)]
        [Sitecore.Data.ID]$FormID,
        [parameter(mandatory=$false)]
        [Nullable[DateTime]]$StartDate,
        [parameter(mandatory=$false)]
        [Nullable[DateTime]]$EndDate
    )
    $provider = [Sitecore.DependencyInjection.ServiceLocator]::ServiceProvider.GetService([Sitecore.ExperienceForms.Data.IExportDataProvider])
    $content = $provider.Export($FormID.Guid, $StartDate, $EndDate).Content
    if (-not $content) {
        return $null
    }

    # Not working: replace new line chars with HTML breaks for table display
    $content = $content.replace("'n","<br/>").replace("'r","<br/>").replace("\\r\\n","<br/>").replace("\r\n","<br/>")

    $content | ConvertFrom-Csv -Delimiter ';'
}

Have you any ideas how we can preserve this new line data when pulling it from the database?

Answer 1

ExportDataProvider replaces newlines in the data from FormDataProvider to spaces. So you must get the data with FormDataProvider and format it manually.

Here is an example of a fixed version:

function Get-FormData($formId, $startDate, $endDate) {
    $provider = [Sitecore.DependencyInjection.ServiceLocator]::ServiceProvider.GetService([Sitecore.ExperienceForms.Data.IFormDataProvider])
    $entries = $provider.GetEntries($formId.Guid, $startDate, $endDate)
    if (-not $entries) {
        return $null
    }

    # Get all column titles
    $titles = new-object System.Collections.Generic.HashSet[string]
    $entries | % { $_.Fields | % { $titles.Add($_.FieldName) > $null } }

    # Make rows from the entries
    $entries | % { ConvertTo-Row $_ $titles }
}

function ConvertTo-Row($entry, $titles) {
    $row = [PSCustomObject]@{}
    $titles | % {
        $field = $entry.Fields | ? FieldName -eq $_ | select -First 1
        $value = $field.Value -replace [Environment]::NewLine,"<br>" # replace newlines to br tag
        $row | Add-Member -MemberType NoteProperty -Name $_ -Value $value
    }
    $row | Add-Member -MemberType NoteProperty -Name Created -Value $entry.Created
    $row
}