Sitecore Forms - Export to Semicolon File


The new dashboard for Sitecore Forms has a button that allows a user to export their form data to a CSV file. When clicking on that button, you can choose to export all data or a specific date range. Interestingly enough, when that CSV gets generated, it shows up with semi-colons as the delimiter. Under normal circumstances, this wouldn't be a big deal, except that the export provider forgot to tell the file that it uses a semi-colon as the delimiter. This results in the file not gracefully opening in Excel. This popped up in a question on Slack today, and I remembered that we applied a "fix" for this in a project. A colleague of mine, Keith Deshane, solved this problem by overwriting the controller used to generate the export. In going through his change, I did a deeper dive into the implementation, and was able to achieve a similar resolution with a little less code.

Out of the box, Sitecore leverages the Sitecore.ExperienceForms.Client.Data.CsvExportProvider class to generate the file content when the user chooses to export the form data. DotPeek (a Sitecore dev's best friend) told me that the semi-colon as the delimiter is hard-coded into the implementation:

Hard Coded Delimiter

This means that we can either write our own export provider, or figure out a way to tell the exported file that it is full of semi-colons. Luckily, the implementation of the provider uses virtual methods!! :D

Virtual Method

Let's make a new class that extends the existing provider:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public class SemiColonExportProvider : CsvExportProvider
{
    public SemiColonExportProvider(IFormDataProvider formDataProvider) : base(formDataProvider) { }
 
    protected override string GenerateFileContent(IEnumerable formEntries)
    {
     // this will tell the file that it is using semi-colons
        const string separator = "\"sep=;\"\r\n"; /* see foot note */
 
        var content = base.GenerateFileContent(formEntries);
        var stringBuilder = new StringBuilder();
 
        stringBuilder.AppendLine(separator);
        stringBuilder.AppendLine(content);
 
        return stringBuilder.ToString();
    }
}

This class is pretty straight forward. We extend the existing provider, and are going to use most of the logic. The first line of the GenerateFileContent method will tell the file that the separator is a semi-colon. We can leverage the default logic for loading up the content, and then just make sure the separator is added to the beginning of the file.

All that is left is to swap out the original provider with the new provider:

1
2
3
4
5
6
7
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
    <sitecore>
        <services>
            <register serviceType="Sitecore.ExperienceForms.Data.IExportDataProvider, Sitecore.ExperienceForms" implementationType="Feature.Forms.Providers.SemiColonExportProvider, Feature.Forms" patch:instead="*[@implementationType='Sitecore.ExperienceForms.Client.Data.CsvExportProvider, Sitecore.ExperienceForms.Client']"/>
     services>
    sitecore>
configuration>

Easy as that, the new export logic will now generate a file that can be interpreted by Excel:

Final Output

FOOTNOTE: @mike_i_reynolds says not to hard code string literals. Check out this post to learn how you can configure and inject the separator string.

 

I hope this helps alleviate some headache when it comes to figuring out why your exported form data isn't opening gracefully. 

 

View more blogs and tutorials about Sitecore

 

Learn about our Sitecore work

 

Categories: Sitecore, Sitecore Forms
Tags: Sitecore;

SEARCH ARTICLES

CATEGORIES

Sitecore 144
Commerce 100
Web Development 100
Sitecore Commerce 83
Sitecore Experience Commerce 9 64
Sitecore Experience Commerce 59
Content Management 55
eCommerce 48
B2B eCommerce 42
Sitecore Experience Platform 39
Sitecore Platinum Partner 39
Architecture 34
Insite 29
User Experience 26
Strategy 22
B2C eCommerce 21
B2B Commerce Blogs 21
commerceconnect 21
CloudCraze 20
SaaS (Software as a Service) 20
Cloud 17
Commerce Server 17
Salesforce B2B Commerce Cloud 16
Mobile 13
Search 13
Plugins 12
Analytics 12
Application Development 12
Digital Transformation 11
Sitecore Symposium 11
SPEAK 10
Helix 10
DMS 8
Social 8
Business Process 7
Authentication 7
5 Reasons 7
BI and Big Data 6
Data Visualization 6
Sitecore Presentation 6
Coveo 6
NET Development 5
Microsoft Azure 5
Automation 5
Front-End Development 4
SaaS (Software as a Service) 4
Digital Strategy 4
Brightcove 4
Press Release 4
Avalara Tax 3
Sitecore Experience Accelerator (SXA) 3
Sitecore Layouts 3
Video 3
SPE 3
Multi-site 3
Multi-lingual 3
Accessibility 3
Habitat 3
Vault 3
Identity 2
Managed Services 2
CDN 2
SMB 2
Cryptocurrency 2
Sitecore Forms 2
Sitecore Experience Commerce 9 Promotions 2
Uncategorized 2
EXM 2
Conversational Commerce 2
Sitecore SaaS 2
Security 2
Unit Testing 2
Headless Architecture 2
Sitecore Experience Awards 2
Google 1
Content Delivery Network 1
Configure Price Quote 1
CPQ 1
Blockchain 1
Coupons 1
Sitecore Rss 1
Artificial Intelligence 1
Machine Learning 1
Okta 1
RFP Process 1
NoSQL 1
Flex Accelerator for Sitecore 1
Reviews 1
SEO 1
Page Labels 1