1

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

textfield Sitecore Forms field with line breaks

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

saved textfield value in database

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 ';'
}

CSV table display

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

  • Are you using SPE to export the data to CSV? What tells you that the characters are missing? Can you try to export this directly to a text file and see what characters exist? – Michael West Mar 19 at 20:55
  • @MichaelWest We are using SPE, yes. If I take the content from IExportDataProvider.Export.Content and do a string replace with "_" on space characters I get the following output: Line_1__Line_2___Line_3. This indicates to me that the Sitecore implementation of that method appears to be the limiting factor here? – Marcel Mar 19 at 22:13
  • 1
    You appear to be describing an API that is not provided by SPE but perhaps Sitecore Forms. This would necessitate you "looking under the hood" at that API to see if there is some type of formatting taking place. – Michael West Mar 19 at 23:37
4

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
}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.